Need only most recent record for each...

[Guest]

Hi everyone! Hope you are all having a wonderful week! I'm using A02 on XP.

I've been asked to extract a list of the most recent statements for each
contract in my PartStmtTable. The fields I'm using are contract number
[PlanNum], statement date [StmtDate] and number of employees
[ParticipantCount].

My data may contain these records:

G1234 10/31/2006 25
G1234 11/31/2006 26
G1234 12/31/2006 25

G5678 11/31/2006 78
G5678 12/31/2006 78
G5678 01/31/2007 78

G9012 10/31/2006 15
G9012 11/31/2006 16

And I need to extract this:

G1234 12/31/2006 25
G5678 01/31/2007 78
G9012 11/31/2006 16

Hope I'm describing it correctly. Tried to play with the query property
'Unique Values' and 'Unique Records'. I can pull a list of contract numbers
with 'Unique Values' but...

Thanks in advance for any help or advice. I really appreciate everything I
learn here.

 

[Guest]

I do not know subqueries so I used two to do it.

Bonnie_A ---
SELECT PartStmtTable.PlanNum, Max(PartStmtTable.StmtDate) AS MaxOfStmtDate
FROM PartStmtTable
GROUP BY PartStmtTable.PlanNum;

SELECT Bonnie_A.PlanNum, PartStmtTable.StmtDate,
PartStmtTable.ParticipantCount
FROM Bonnie_A INNER JOIN PartStmtTable ON (Bonnie_A.MaxOfStmtDate =
PartStmtTable.StmtDate) AND (Bonnie_A.PlanNum = PartStmtTable.PlanNum);

 

[Marshall Barton]

Hi everyone! Hope you are all having a wonderful week! I'm using A02 on XP.

I've been asked to extract a list of the most recent statements for each
contract in my PartStmtTable. The fields I'm using are contract number
[PlanNum], statement date [StmtDate] and number of employees
[ParticipantCount].

My data may contain these records:

G1234 10/31/2006 25
G1234 11/31/2006 26
G1234 12/31/2006 25

G5678 11/31/2006 78
G5678 12/31/2006 78
G5678 01/31/2007 78

G9012 10/31/2006 15
G9012 11/31/2006 16

And I need to extract this:

G1234 12/31/2006 25
G5678 01/31/2007 78
G9012 11/31/2006 16

Hope I'm describing it correctly. Tried to play with the query property
'Unique Values' and 'Unique Records'. I can pull a list of contract numbers
with 'Unique Values' but...

Couple of ways, but they all require a subquery. Try this:

SELECT PlanNum, StmtDate, ParticipantCount
FROM PartStmtTable
WHERE StmtDate = (SELECT Max(X.StmtDate)
FROM PartStmtTable As X
WHERE X.PlanNum = PartStmtTable.PlanNum)

 

[Guest]

Another way of doing it, using SubQueries but with TOP instead of Max, that
way by hanging the number after the TOP you can retrieve 2,3 etc records for
each PlanNum.


SELECT T1.PlanNum, T1.StmtDate, T1.ParticipantCount
FROM PartStmtTable As T1
WHERE T1.StmtDate In (SELECT TOP 1 T2.StmtDate
FROM PartStmtTable As T2
WHERE T2.PlanNum = T1.PlanNum
Order By T2.StmtDate Desc)