hi i have a database with multiple date entry of the same record id. how do i write a query so that it pulls up just the last date value?

thanks

Check out the MAX function in Access help

SELECT IDField, Max(DateField) AS MaxDate
FROM TableName
GROUP BY IDField

If you want other info from that record, join the above query to the original table on those 2 fields.

Thank you that works.

I got a similar question.

I have a query that lists multiple records and dates. How do i get it to show just the last date for each record.

My query has:

TAG (unique ID for each cow)
DryingOffDate (a date)

An example of the output so far is:

UK162574100278 03/02/2009
UK162574100278 20/12/2008
UK162574100278 29/11/2008
UK162574100411 31/01/2009
UK162574100411 11/01/2009

I want to list just the latest date for each TAG.

Cheers

That's not similar, it is exactly the same with exactly the same solution.

Brian

i'll go try it again, sorry bout that, guess i misunderstood - i thought it would filter to just 1 record !

am i right in saying it would list all the TAG's but with just the latest date. and would it matter that the DueDate is a calculated field in the query i am taking it from ?

thanks for your assistance too. ;)

Yes it will list all TAGS with the max date for that TAG, I think its ok for it to be a calculated field.

Brian

I must be missing something here mate i'm sorry.

This is my query in SQL that works but duplicates entries with more than one date:-

SELECT qryAIRegister.TAG,qryAIRegister.DueDate
FROM AnimalRegister INNER JOIN qryAIRegister ON AnimalRegister.TAG = qryAIRegister.TAG
WHERE (((qryAIRegister.DueDate)>=Date()+60) AND ((AnimalRegister.[On Farm])=True))
ORDER BY qryAIRegister.TAG, qryAIRegister.DueDate;

I am not sure where the MAX function is to be inserted. I have tried quite a few things but most give me this error:-

"You tried to execute a query that does not include the specified expression 'TAG' as part of an aggregate function"

This is the error i got with this alteration to the SQL statement:-

SELECT qryAIRegister.TAG, MAX(qryAIRegister.DueDate) AS MaxDate
FROM AnimalRegister INNER JOIN qryAIRegister ON AnimalRegister.TAG = qryAIRegister.TAG
WHERE (((qryAIRegister.DueDate)>=Date()+60) AND ((AnimalRegister.[On Farm])=True))
ORDER BY qryAIRegister.TAG, qryAIRegister.DueDate;

:(Am i doing something wrong..?

I guess its probably just staring me in the face. Your assistance and my persistance will prevail though i think ;)

Thanks

You have to have a totals query.
In your first query change it to a Totals query, click the icon that looks like a greek letter about half way along, Tag will show Groupby, change the duedate Groupby to max

Brian

Brilliant, thats just what i was looking for mate - thanks very much for your help.

Another little step on the learning curve ;)