Why do I get a prompt when running this query?

[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".

 

[Bob Barrows]

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.

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