Display the latest date in series

  • Thread starter Thread starter Hera
  • Start date Start date
H

Hera

Greetings, I have a report based on a query, which totals
a variety of amounts that are entered on a weekly basis. I
need to display the latest date for which amounts were
entered on my report. Some of these items might be updated
for 4 consecutive weeks, others might be 16 weeks, so the
number of weeks I track is inconsistent.

For instance, a series of amounts were entered for 6
consecutive weeks beginning 6/6/03 and ending 7/11/03.
Reports are pulled on a quarterly basis, so I prompt for
start/end dates. Then for each of my items, I total the
amounts and I need to display the date of 7/11/03 with
those amounts, as that was my final entry and the date
this item closed out.

Is there a function for this or an expression, and would
it better fit in the query or the report grouping?

Many thanks,
Hera
 
Hera,

Without knowing a little more about your table structure,
I'll give you an example that I use frequently.

I hav an application that analyzes data coming from a
wargame that tells me where a unit is every 5 minutes, if
it is moving. If it isn't moving, it doesn't add a record
to the table.

The table structure is something like:

UnitID, Sim_time, Latitude, Longitude

For me to determine where the unit currently is at a
particular point in time, I have to identify the last
record that was written for that unit, prior to my
requested time, so I have a query that looks like:

SELECT UnitID, Max(Sim_Time) as LatestReportTime
FROM myTable
WHERE Sim_Time <= [Simulation Time]
GROUP BY UnitID

I then use this query in a second query to get the rest of
the data for that unit at that time.

SELECT UnitID, Sim_Time, Latitude, Longitude
FROM myTable T
INNER JOIN qryLatestReport Q
ON T.UnitID = Q.UnitID and T.Sim_Time = Q.LatestReportTime

Hope you can use this technique with your data.

Dale
 
Back
Top