Selecting a field based on Max date

[Heather]

HI

I want to only select tickets based on Max Update date and if it is equal to
a specific assignment.

Max Update date = Assignments needed = fib, fbsi, ibg, ibgnf

Ticket Update date Problem Status Assignments
IM0052 12/01/2005 Open ais
IM0052 12/02/2005 Working ibg
IM0777 12/01/2005 Open dsmt
IM0777 12/06/2005 Pending nf

So Only Ticket IM0052 would be selected because the max Update date and the
Assignments are equal to ibg (Which is one of the Assignments needed).

So it would return;
Ticket Update date Problem Status Assignments
IM0052 12/02/2005 Working ibg


Thanks in advance for any help......!

 

[OfficeDev18 via AccessMonster.com]

Hi, Heather,

Try two levels of queries, as follows:

SELECT Ticket, Max([Update date]) As [MaxOfUpdate date] FROM tblTableName
GROUP BY Ticket;

Call that subquery, say, qryTicketsSub1. Besides that subquery, make another
query:

SELECT * FROM tblTableName INNER JOIN qryTicketsSub1 ON (tblTableName.Ticket
= qryTicketsSub1.Ticket) And (tblTableName.[Update date] = qryTicketsSub1.
[MaxOfUpdate date]) WHERE Assignments IN('fib', 'fbsi', 'ibg', 'ibgnf');

HTH

 

[John Spencer]

The SQL for your query would look something like the following

SELECT Ticket, [Update date], [Problem Status], Assignments
FROM [Your table] As Y
WHERE Y.Assignments in ("fib","fbsi","ibg","ibgnf")
AND Y.[Update Date] =
(SELECT Max(T.[Update Date])
FROM [Your Table] as T
WHERE T.Assignments in ("fib","fbsi","ibg","ibgnf"))

 

[Heather]

Thank You for your help....!

The SQL for your query would look something like the following

SELECT Ticket, [Update date], [Problem Status], Assignments
FROM [Your table] As Y
WHERE Y.Assignments in ("fib","fbsi","ibg","ibgnf")
AND Y.[Update Date] =
(SELECT Max(T.[Update Date])
FROM [Your Table] as T
WHERE T.Assignments in ("fib","fbsi","ibg","ibgnf"))

HI

I want to only select tickets based on Max Update date and if it is equal to
a specific assignment.

Max Update date = Assignments needed = fib, fbsi, ibg, ibgnf

Ticket Update date Problem Status Assignments
IM0052 12/01/2005 Open ais
IM0052 12/02/2005 Working ibg
IM0777 12/01/2005 Open dsmt
IM0777 12/06/2005 Pending nf

So Only Ticket IM0052 would be selected because the max Update date and the
Assignments are equal to ibg (Which is one of the Assignments needed).

So it would return;
Ticket Update date Problem Status Assignments
IM0052 12/02/2005 Working ibg

Thanks in advance for any help......!