show only the newest date

[roley]

Warning not a PRO.

I have a query that i use to keep track of rental equipment. When the piece comes in off rent we record the date it left and when it came back, as well as it's "hour" meter. It's like a speedometer to tell us how long it has been running. At the end of each month i run a query that compares the hours it came in with against the hours it was last priced up with. My problem is when I run this query it shows all the times it was out and all I need the the last date. The results of this query sometimes is 100 records long and a unit will be mixed in 4 to 5 times. Since that last date will have the the most hours. I tried max in the total and also last. It did not work, obviously. There are two tables in my query, TableA is the inventory table and TableB is the rental record table. I have it joined by the stock number. I have parameters in the date in field asking for the my dates. I also calculate the hours and as a criteria keep only the pieces that aged 100 hours. How can I only see the eldest date? Your responses are always appreciated.

 

[Brianwarnock]

Hi

"It did not work, obviously. "

As my old Uni Prof said " nothing is obvious to those who do no know"

I guess that you want the record with the last return date, but the fact that Last is an aggregate function is causing you problems.

You need to write a simple select query selecting stock number and return date only, group on stock number select Last return date, then include this in your query joining it to the rental table on return date and stock number the join properties select all recordsfrom this query and only matching records from rental, this will ensure that only the latest record for a stock item is selected for your query.


I hope this provides what you want.

Brian