query for last date on multiple records

[phamyh]

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

 

[Rabbie]

Check out the MAX function in Access help

 

[pbaldy]

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.

 

[phamyh]

Thank you that works.

 

[shenty]

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

 

[Brianwarnock]

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

Brian

 

[shenty]

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.

 

[Brianwarnock]

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

 

[shenty]

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

 

[Brianwarnock]

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

 

[shenty]

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

Another little step on the learning curve

 

[widemonk]

In addition to showing the maximum date, I would like the query to show the name of the person that actioned the last date activity but ONLY for that last date, regardless of who did it.

My code below returns the last date that an audit took place for each individual auditor. eg, Aisle numbers are duplicated, one for each auditor. Im not bothered who did it, I just want to know when, and which member of staff it was.

SELECT tblAudit.AisleID AS Expr1, Max(tblSession.AuditDate) AS MaxDate, tblSession.Auditor
FROM tblSession INNER JOIN tblAudit ON tblSession.SessionID = tblAudit.SessionID
GROUP BY tblAudit.AisleID, tblSession.Auditor;

If I take away the concept of who it was and just have the 2 fields, it works perfectly.

 

[jdraw]

What is the name of the field you want to show, and what table is it in?

Please show the query SQL you have.

You realize that this thread (until your post) was 4 years old.

 

[widemonk]

SELECT tblInbAisleAudit.AisleID AS Expr1, Max(tblSession.AuditDate) AS MaxDate
FROM tblSession INNER JOIN tblInbAisleAudit ON tblSession.SessionID = tblInbAisleAudit.SessionID
GROUP BY tblInbAisleAudit.AisleID;

In tblSession I also have a field called 'Auditor'. I would like to include this field but limit the query to last aisle audit only, regardless of who did it. Not duplicate aisles when they were audited by someone different

(which is what this does, and I dont want this )

SELECT tblInbAisleAudit.AisleID AS Expr1, Max(tblSession.AuditDate) AS MaxDate, tblSession.Auditor
FROM tblSession INNER JOIN tblInbAisleAudit ON tblSession.SessionID = tblInbAisleAudit.SessionID
GROUP BY tblInbAisleAudit.AisleID, tblSession.Auditor;

 

[pbaldy]

I'm not visualizing your data, but is this appropriate?

http://www.baldyweb.com/LastValue.htm

 

[widemonk]

Perfect - thank you

 

[pbaldy]

Happy to help.

 

[Cirrostratus]

I know, this is an old topic but same question with a twist.

I have a form where a user updates the "capacity" data, I want to link a query to the form so the form updates the data via the query.

My data is like this:

CaptureDate Location Capacity
10/01/2013 A011 100
10/05/2013 A011 50
10/05/2013 A012 0

Tthe query needs to bring the last record for each location. But with the Max on the CaptureDate, it still shows the first and second row in the above example because the Capacity value is different. I want it to bring only the last per each Location. And needless to say, it cant be a multi query thing since I need the form to update the data. Any ideas? :banghead:

 

[pbaldy]

Did the link in post 15 not help?

 

[David R]

I think you mean post #11... yours is #15!