Query to retrive record for max date

[Guest]

Friends,

I have table " Labor record" where i keep records of all Labor name that
engauged mostly everyday. How can I retrive all labor name for the max date
(the last date in table) only for Reports? In Report I don't need to get
all previous Date Records . I need only Last Records.

I created Query of this table in which in the criteria line I wrote
Date() and it work ok as long as I have Records in table for toDays date
but suppose I don't have records today then but I would like to report for
the latest available records then I have nothing in report!!!!

so What is the sol. for this?
Andy

 

[BMore via AccessMonster.com]

Andy,

keeping the answer to basic MS Acces functionality:
1) Make a query (Q1) selecting only the date from (T1) the table containing
the date, set aggregate to Max
Now you have the max date.

2) Make a second query (Q2) where you join the first query (Q1) to the table
containing the date (T1)
Select the fields you need
Run Q2 (it will run the Q1 for you). Now you have your answer

in sql other solutions are available, but this is an MS Access forum and too
many people are already going for too complex options, ignoring the power of
basic MS Access.

Ben

 

[John Spencer]

Try using

Criteria: DMax("YourDateFieldName","LaborTable")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

John Spencer,

Thank you very much. You are "Guru"!!!! The query u suggested is working
perfectly. Thank you again.

Andy

 

[Guest]

BMore,
Thank you very much for helping me. John's Query works great. I 'll try
with your Query too and see which is convient to me . Thank you again.
Andy