Selecting a date from a table

[Sher]

I have a table that has rows of data (rows are titled
depthID (my key field), datetaken, depth, and readingby)
The depth readings are taken on various dates. I want to
create a query from this table to use as a basis for a
report. I only want the date and depth reading for the
most recent date to show on the report.

I know I can specify in the query criteria box a specific
date or range of dates, but I want the report to
automatically only list the latest date and reading
associated with that date.

Thanks
Sher

 

[Allen Browne]

1. Create a new query.

2. Switch to SQL view. (View menu).

3. Paste:
SELECT TOP 1 DepthID, DateTaken, Depth FROM MyTable ORDER BY DateTaken DESC
DepthID DESC;

4. Replace "MyTable" with the name of your table.

5. Switch back to query design view, and see how it works.
Note that the "TOP 1" bit is set in the Properties of the query.

By ordering in decreasing order on the date, the most recent reading is
placed on top. By selecting TOP 1, you get only the top row of the query.
(Well, if there are two readings on one date, JET gives you two rows, but
you can get around that by specifying to sort on the primary key field
also.)