I am using the last function in my table to sort the most recent maintenance record for items in my database. Unfortunately my qry does not seem to be picking up new records that I add to the maintenance table.

Now, I am using lookups within my table to auto-fill predetermined types of maintenance. In my query I have my last function actually looking at the lookup table as that is the only thing that seems to be returning values oddly enough. When I point the lookup directly at the maintenance table itself it cannot recognize the field values.

Below is my SQL code.



Edited by: sup909 on Fri May 8 15:24:46 EDT 2009.

EDIT: SQL script to prevent horizontal scrolling. Doug

Edited by: DougY on Fri May 8 17:09:34 EDT 2009.

Though "Last" may have date implications based upon its name it refers to the order of selection of a record.
So you'll get the date of whatever was the last record that the engine selected for each group.

Since you're also selecting another row from tblMaintenance are you wanting them to be related, or just grouped as you have them (i.e. you're not wanting the one "last" row from that table related to tblHistoryLookup?)

If MYear is the only field you're wanting an aggregated result from then Max is enough for you.
If you want an entire record then Max will generally still play a part - but in conjunction with a subquery.

Cheers.

You are correct in that I want the entire record. I actually don't want anything from tblHistoryLookup per se, since that only has about 5 records in it. That table is basically just populating a lookup field for the tblMaintenance.Type

Ahh, I was able to fix the problem by creating a sub-qry. Essentially I did the sort by Last twice and it worked.

Thanks.

I would like to stress DO NOT USE LAST! It "APPEARS" to work in your current case, but it WILL NOT ALWAYS work the way you think it will. It is one of the most misused functions in Access and one of the least useful.

I believe you should rethink your use of it.

To confirm what Bob's saying (who was confirming what I said and so I'm re-confirming what I said originally :-s).

LAST is not the choice here. It is MAX you should be using.
Last does have it's uses - and it is somewhat predictable (as much as it's possible to be) but not what to use in these scenarios).
I'm not clear on what you're saying you've implemented on Last to see what you wanted, but if my earlier supposition was correct, then we'd need to know the final desired output is to be to offer the more standard, reliable solution).

Cheers.