Dear Sir or madam,



I have a table which holds three values:



QueNumber(1-90) StatusID(AutoNumber) and Status(Number-code: 1=start, 

2=Stop)



I would like a query to return Group the Que numbers together, and then 

return the Max StatusID-Which would be the most recent entry. With these 

returned I only want the Que numnbers which have a Status of 1=Start.



can this be done in one query?

or code behind my form?

Please help

If I have understood your requirement correctly, the following SQL will do

what you want.



(Assuming your table is called "tblQue")





SELECT QueNumber, Max(StatusID) AS MaxOfStatusID

FROM tblQue

WHERE Status=1

GROUP BY QueNumber;





Ian Ashton







-----Original Message-----

From: KennethMungwira [mailto:KennethMungwira@Y...]

Sent: Tuesday, January 15, 2002 9:47 PM

To: Access

Subject: [access] Returning Max Value + Criteria





Dear Sir or madam,



I have a table which holds three values:



QueNumber(1-90) StatusID(AutoNumber) and Status(Number-code: 1=start,

2=Stop)



I would like a query to return Group the Que numbers together, and then

return the Max StatusID-Which would be the most recent entry. With these

returned I only want the Que numnbers which have a Status of 1=Start.



can this be done in one query?

or code behind my form?

Please help