Why do I get a prompt when running this query?

  • Thread starter Thread starter Dorian
  • Start date Start date
D

Dorian

I get a prompt for 'Query1.TicketID' when running this query:

SELECT TicketID, TeamID, AssignDate
FROM tblAssign AS A
WHERE AssignDate = (SELECT MAX(AssignDate) FROM tblAssign WHERE TicketID =
A.TicketID);

Why? This is Access 2003.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Dorian said:
I get a prompt for 'Query1.TicketID' when running this query:

SELECT TicketID, TeamID, AssignDate
FROM tblAssign AS A
WHERE AssignDate = (SELECT MAX(AssignDate) FROM tblAssign WHERE
TicketID = A.TicketID);

Why? This is Access 2003.
Click to expand...
That's strange ... try this:

SELECT TicketID, TeamID, AssignDate
FROM tblAssign AS A
WHERE AssignDate = (SELECT MAX(AssignDate) FROM tblAssign As B WHERE
B.TicketID =
A.TicketID);

You might also try:

SELECT TicketID, TeamID, AssignDate
FROM tblAssign AS A
WHERE AssignDate In (SELECT MAX(AssignDate) FROM tblAssign As B WHERE
B.TicketID =
A.TicketID);

or

SELECT TicketID, TeamID, AssignDate
FROM tblAssign AS A Inner Join
(SELECT TickeID,MAX(AssignDate) FROM tblAssign GROUP BY TicketID) As B
ON A.TicketID=B.TicketID AND A.AssignDate=B.AssignDate
 
Back
Top