getting the last record

[icemonster]

i want to know how to do this properly.

i have a table that has a visit date and a visit status field, what i want is, to make a query that will return the last visit that has a status of confirmed then use that field in another query where i'll alias it to say, "last confirmed" visit and it would show on every client record we have that is tied to it specifically.

i once saw a best sample db for this one, and it was tracking mileage for a car yet i can no longer find it. any help is greatly appreciated.

 

[JamesMcS]

Wouldn't it just be select max of visit date where status=confirmed?

 

[icemonster]

yes. but then i want that record to join another query (same table, schedule) then from the qryMAX i want the field that shows the last record to then be part of that query, so i can have a query that shows clientid, THELASTCONFIRMEDREC, visit date, status. but the thing for the lastconfirmedrec is, it's not exactly giving me the results that i need.

 

[JamesMcS]

You should be able to do this just in the one query. Group by all the fields you want to show in the final result and use max of visit date where status is confirmed?

 

[icemonster]

i already did that, it works but my real main issue is, that is sort of shows the last record, say the last visit confirmed on the next record line, so meaning, if the record does not exist yet that field will be null.

i really hope i find that mileage db that allenbrowne made.

 

[CharlesWilliams]

I think I might have that file. Give me a few to dig it up for you.