Query Field Value Based on Min Date

[Cmacvp16]

I need to run a simple query to pull one record and their value, for each member, based on Service Date.

Table
Client DOS Receipt
J. Smith 01/01/2003 $295
J. Smith 02/15/2003 $215
H. Jones 03/18/2003 $415
H. Jones 03/15/2003 $400

Query Results
Clint DOS Receipt
J. Smith 01/01/2003 $295
H. Jones 03/15/2003 $400

Thanks

 

[dcx693]

You can use a totals query to group on Client and use the Min (or First) function on the DOS.

If you're not sure about totals queries, look up the topic in the Access on-line help under "Calculate a sum, average, count, or other total on groups of records in a query".

 

[Cmacvp16]

I still am unable to fiqure this out. On the surface this seems rather simple.

Sorry

 

[Jon K]

The attached database contains your sample data and a series of two queries. You can open it and run Query2.

The database was saved from Access 2000.

 

[dcx693]

Thanks for the pickup Jon K!

Cmacvp16, in Jon's database sample, Query1 is the query that I suggested making. It find the minimum DOS. Query2 takes that information, joins it to the original table and picks out the Receipt amount associated with that user and DOS.