Have a question about a query

[shcuck]

When I run a query using the "Last" or "Max" criteria, it will give me the last entry for each of the different fields. For Example:

If I am running the query with the following fields:
Date, Name, action, termination. Using the MaxofDate, the query will return the max date of each action and each termination, instead of the Last record entered regardless of the termination or action.

I hope it makes sense.

I would really appreciate it if someone could help out with this?

Thanks
Shcuck

 

[mph1]

What is it you want to accompolish? Find the last record entered or date? They may not be one in the same. Or maybe I don't understand your question.

 

[shcuck]

I have a database that has many records everyday. I want to find out what the last action code was for the day.

example database:

Account# Date Time(hhmmss) action code
123456 12/20/02 124536 paid
265897 12/20/02 133252 unpaid
365898 12/20/02 142515 other
698547 12/20/02 163202 unpaid
985474 12/20/02 174536 paid

in the example above, I need the query to only show the record for account# 985474 because that was the last entry for the day. When I run the query using "Last" or "Max" it will show.

Account# Date Time(hhmmss) action code
365898 12/20/02 142515 other
698547 12/20/02 163202 unpaid
985474 12/20/02 174536 paid

It will show the last date and time that each action code was used instead of:

Account# Date Time(hhmmss) action code
85474 12/20/02 174536 paid


I hope this has helped clarify my question.

Thanks again.

 

[lhite]

Make sure that the ACTION field is not set to GROUP BY. Choose LAST or MAX for that as well. Make sure that the column for ACTION is after the column for DATE. You will probably have to create separate queries for the ACTION and TERMINATIONs, if you're looking to get both. (Since I don't know how your database works I can't say for sure, but isn't a termination an action?)

-Larry

 

[mph1]

It sounds like the “action code” and “account number” are equateable. Is the “account umber” and ID number the same? Whatever type of record number system you are using, you could use the Dmax function to find the last record entered (highest number)
HTH

Maurie

 

[sdk]

Hi,

An easier way is to use the top values of the query.

Build a normal select qry, then set the 'top values' property to one. Order by the two date/time fields descending.

I think this will give you the answer your looking for.

Cheers

Steve

 

[shcuck]

Thanks a lot for all your help. I was able to work it out, combining the suggestions of all.

Thanks again.

Shcuck