Minimum and Grouping

[naungsai]

Dear Friends

I have a table as below.

PCode…………Status………..StatusDate
001.....………….3…………….1/1/2009
001.....………….1…………….2/1/2009
001.....………….2…………….3/1/2009
002.....………….1…………….1/3/2009
002.....………….2…………….2/3/2009
002.....………….3…………….3/3/2009

I want the query to run as followed.

PCode…………Status………..StatusDate
001....………….1…………….2/1/2009
002....………….1…………….1/3/2009

Although it is convenient to find the "Status" value by "min" function, I don't know how to make the "StatusDate" of respective row appear.

Please help me.

 

[CameronM]

I am not sure if there is a better way but try this. I have called the table tblCode - replace this with whatever your table is called.

Build a query with just the Pcode and MIN grouped by PCode.

SELECT tblPcode.PCode, Min(tblPcode.Status) AS MinOfStatus
FROM tblPcode
GROUP BY tblPcode.PCode

Then add this in a second query with a link on the Pcode and Status.

SELECT tblPcode.PCode, tblPcode.Status, tblPcode.StatusDate
FROM qryPCode_Grouped INNER JOIN tblPcode ON (qryPCode_Grouped.PCode = tblPcode.PCode) AND (qryPCode_Grouped.MinOfStatus = tblPcode.Status)

 

[naungsai]

Dear Cameron

Thank you very much. It works beautifully.

I have learnt great thing from you.

Best

 

[lemo]

sorry to resurrect this, but i have an additional question.

if we sort the data above by Scode then by Status and then use the First command to select the necessary date, it doesn't work.
but why?

l

 

[boblarson]

You should avoid using FIRST with dates (Microsoft has an article about that but I don't know where it is at the moment). Use Min/Max for dates instead of First/Last.