query for last date on multiple records (1 Viewer)

phamyh

Registered User.
Local time
Today, 09:33
Joined
Oct 29, 2008
Messages
19
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

Super Moderator
Local time
Today, 13:33
Joined
Jul 10, 2007
Messages
5,906
Check out the MAX function in Access help
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:33
Joined
Aug 30, 2003
Messages
36,118
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.
 

shenty

Registered User.
Local time
Today, 13:33
Joined
Jun 8, 2007
Messages
119
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

Retired
Local time
Today, 13:33
Joined
Jun 2, 2003
Messages
12,701
That's not similar, it is exactly the same with exactly the same solution.

Brian
 

shenty

Registered User.
Local time
Today, 13:33
Joined
Jun 8, 2007
Messages
119
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

Retired
Local time
Today, 13:33
Joined
Jun 2, 2003
Messages
12,701
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

Registered User.
Local time
Today, 13:33
Joined
Jun 8, 2007
Messages
119
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

Retired
Local time
Today, 13:33
Joined
Jun 2, 2003
Messages
12,701
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

Registered User.
Local time
Today, 13:33
Joined
Jun 8, 2007
Messages
119
Brilliant, thats just what i was looking for mate - thanks very much for your help.

Another little step on the learning curve ;)
 

widemonk

Registered User.
Local time
Today, 13:33
Joined
Jun 16, 2005
Messages
48
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

Super Moderator
Staff member
Local time
Today, 09:33
Joined
Jan 23, 2006
Messages
15,362
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

Registered User.
Local time
Today, 13:33
Joined
Jun 16, 2005
Messages
48
Code:
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 :( )
Code:
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

Wino Moderator
Staff member
Local time
Today, 06:33
Joined
Aug 30, 2003
Messages
36,118
Happy to help.
 

Cirrostratus

Registered User.
Local time
Today, 09:33
Joined
May 16, 2013
Messages
29
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

Wino Moderator
Staff member
Local time
Today, 06:33
Joined
Aug 30, 2003
Messages
36,118
Did the link in post 15 not help?
 

David R

I know a few things...
Local time
Today, 08:33
Joined
Oct 23, 2001
Messages
2,633
I think you mean post #11... yours is #15!
 

Users who are viewing this thread

Top Bottom