RC FlightLog Database Setup

[ZJGMoparman]

I am using a very similar query to the one from post 4. I'm using the dmax function to find the Max last updated date. For some reason when I run the query it is showing more than one record for the same battery. Shouldnt dmax only sbow the record corresponding to the maximum date?

[June7]

Similar is not the same and the two queries are really very different.

My suggested query uses DMax in WHERE clause. Your query uses Max and no filter criteria. DMax and Max work differently.

[ZJGMoparman]

I have now created a separate query to calculate the total flight time and average flight time for each battery. For some reason when I run the query I am getting separate results for some batteries. For example: Battery 17 has two flights. When I run the query it shows two lines for battery 17... as if it is treating them as two different batteries.The other batteries seem to be working fine... at least with the test data I am using.

[ZJGMoparman]

I should add that the sql of the query I am using is:

SELECT tblBattery.BatteryID, DMax("SessionDate","qryLinkSession-Flight","Battery=" & [BatteryID]) AS RecentUse
FROM tblBattery INNER JOIN tblFlight ON tblBattery.BatteryID = tblFlight.Battery
ORDER BY tblBattery.BatteryID;

[June7]

I am getting lost. What is it you are trying to do?

Have you tested the exact queries I provided in post 4?

[ZJGMoparman]

I have tested those queries. They seemed to work as long as the flight date and charge date are on separate days. The query I asked about most recently is for something else. The most recent query I asked about is only supposed to calculate total time and averageflight time for each battery.

[June7]

From what I understand of your data, the queries I suggest for the comboboxes work properly even if flights and charging are on same date.

Query for total and average flight times for each battery is an aggregate query, try:

SELECT tblBattery.BatteryID, Sum(Val(Left(Nz([FlightTime],"00"),2))+Val(Mid(Nz([FlightTime],"00:00"),3,2))/60) AS TotMinutes, Avg(Val(Left(Nz([FlightTime],"00"),2))+Val(Mid(Nz([FlightTime],"00:00"),3,2))/60) AS AvgMinutes
FROM tblBattery LEFT JOIN tblFlight ON tblBattery.BatteryID = tblFlight.Battery
GROUP BY tblBattery.BatteryID;

[ZJGMoparman]

The totals and average query works exactly like I wanted. I just added a round function to round to 2 decimal Places.

The control source queries from post 4 are not working on my end. Query1 displays all of the flights for each battery with the corresponding date which is right. The other two queries are listing every single battery now.

[ZJGMoparman]

Actually let me update what I just said. Only the flightentry query is showing every battery. The charge entry query shows nothing.. no matter what kind of test data i eneter, nothing changes.

[June7]

Try this for the ChargeEntry query:

SELECT tblBattery.BatteryID
FROM tblBattery
WHERE (((Nz(DMax("ChargeDate","tblCharge","Battery=" & [BatteryID]),#1/1/2000#))<Nz(DMax("SessionDate","qryFlightTime","Bat tery=" & [BatteryID]),Date())) AND (Not (DMax("SessionDate","qryFlightTime","Battery=" & [BatteryID])) Is Null));

[ZJGMoparman]

Alright, I now have both the ChargeSource and Flightsource queries working like I want. I modified a few things from what you suggested June because I added another criteria for the battery to show up. Now I have a subform nested in the session form that I can enter the flights under that session. The subform is displayed in datasheet view. I would like to have a requery that will update the flightsource combo box list once I use one of the batteries. Since I will be having multiple flights per session, I don't want to accidentally enter the same battery in more than one flight.

[June7]

Commit the record to table then requery the combobox.

Record is committed when form closes, move to another record, or with code.

So in the form Current event:
Me.comboboxname.Requery

[ZJGMoparman]

Thank you June. That worked perfect! I'm slowly learning Access. I still have a load I would like to add to my database, but it is coming together. I would like to add graphs and reports for the batteries, flights, and aircraft, but I am not to that point yet nor do I know how. I will need to start reading when I get to that stage.