find latest date

  • Thread starter Thread starter chris
  • Start date Start date
C

chris

I have an access xp database with about 600,000 records for various stock
codes.
I would like to know how to find the latest date for a nominated code. Each
record is like:
stock_code date rec1 rec2 rec3 etc
AAA 21/3/03
BBB 22/4/03
AAA 27/3/03
BBB 23/4/03
etc
I would like to be able to enter a code (say AAA) and find the latest date
for that code (27/3/03).
Thanks

Chris
 
I have an access xp database with about 600,000 records for various stock
codes.
I would like to know how to find the latest date for a nominated code. Each
record is like:
stock_code date rec1 rec2 rec3 etc
AAA 21/3/03
BBB 22/4/03
AAA 27/3/03
BBB 23/4/03
etc
I would like to be able to enter a code (say AAA) and find the latest date
for that code (27/3/03).
Thanks
Click to expand...

Use a Query based on another Query for this.

First create a Totals query grouping by Stock_Code and selecting Max
of [Date].

Then create a second query joining your table to this query by
Stock_Code and Date, to pick up the remaining fields.
 
chris said:
I have an access xp database with about 600,000 records for various
stock codes.
I would like to know how to find the latest date for a nominated
code. Each record is like:
stock_code date rec1 rec2 rec3 etc
AAA 21/3/03
BBB 22/4/03
AAA 27/3/03
BBB 23/4/03
etc
I would like to be able to enter a code (say AAA) and find the latest
date for that code (27/3/03).
Thanks
Click to expand...

The latest date for a code is the maximum date for that code. Assuming
your field [date] -- bad, bad, name! -- is of the date/time data type,
then a query to get the latest date for code AAA would look like this:

SELECT Max([date]) FROM stock_data
WHERE stock_code = "AAA";
 
Thanks for your help, Dirk's solution did it for me.
(I know [date] is a bad name but I inherited the database and haven't
changed it yet).
Chris


Dirk Goldgar said:
chris said:
I have an access xp database with about 600,000 records for various
stock codes.
I would like to know how to find the latest date for a nominated
code. Each record is like:
stock_code date rec1 rec2 rec3 etc
AAA 21/3/03
BBB 22/4/03
AAA 27/3/03
BBB 23/4/03
etc
I would like to be able to enter a code (say AAA) and find the latest
date for that code (27/3/03).
Thanks
Click to expand...

The latest date for a code is the maximum date for that code. Assuming
your field [date] -- bad, bad, name! -- is of the date/time data type,
then a query to get the latest date for code AAA would look like this:

SELECT Max([date]) FROM stock_data
WHERE stock_code = "AAA";

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top