Query min date for change in data element

  • Thread starter Thread starter Dave
  • Start date Start date
Data example:
DATE CODE
8/8/1990 P
8/30/1990 P
9/26/1994 C
11/16/1994 P
4/10/1995 A
5/30/1995 A
5/4/2009 P

I want to show the min DATE for each change in CODE.
I tried By Group but the result showed the min DATE for "P" but not where
the CODE changed to "P" again.
The result should be:
DATE CODE
8/8/1990 P
9/26/1994 C
11/16/1994 P
4/10/1995 A
5/4/2009 P

Dave
 
Try these two queries --
DaveCodeList --
SELECT Q.Date, Q.Code, (SELECT COUNT(*) FROM [Dave] Q1
WHERE Q1.[Date] < Q.[Date]
AND Q1.
Code: 
 = Q.[Code])+1 AS Rank, (SELECT COUNT(*) FROM [Dave] Q2
WHERE Q2.[Date] < Q.[Date]
AND Q2.[Code] < Q.[Code])+1 AS Rank1
FROM Dave AS Q
ORDER BY Q.Date, Q.Code;

SELECT DaveCodeList.Date, DaveCodeList.Code
FROM DaveCodeList
WHERE (((DaveCodeList.Rank1)=[Rank]));
 
Data example:
DATE CODE
8/8/1990 P
8/30/1990 P
9/26/1994 C
11/16/1994 P
4/10/1995 A
5/30/1995 A
5/4/2009 P

I want to show the min DATE for each change in CODE.
I tried By Group but the result showed the min DATE for "P" but not where
the CODE changed to "P" again.
The result should be:
DATE CODE
8/8/1990 P
9/26/1994 C
11/16/1994 P
4/10/1995 A
5/4/2009 P
Click to expand...

You're assuming that the order of records in the table is relevant. It isn't!
If you group by CODE then it will do exactly that - put all of the code A
values together, all of the code C, all of the code P.

Are the DATE fields in chronological order, or some other arbitrary order? Are
there any other fields in the table which could be used to define a sort
order?
 
Thank you for the queries...but they still give only the earliest date that
say "P" occurs and not the earliest dates when the CODE changes to "P"
throughout the ordered DATE field.

The order of the DATE field is relevant in that the CODE can change from "P"
to "C" then to "P" again, which is important.

Does the table need another field that make the DATE, CODE fields unique?

Dave
 
I'm not sure this will work, but you can try the following solution


First Query (Saved as QRank):

SELECT A.Date, A.Code, Count(B.Date) as Rank
FROM [Your Table] as A LEFT JOIN [Your Table] As B
ON A.Date > B.Date
GROUP BY A.Date, A.Code

Second Query:
SELECT A.Date, A.Code
FROM QRank as A LEFT JOIN QRank as B
ON A.Rank = B.Rank -1
WHERE A.Code <> B.Code
OR B.Code is Null

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
That just about worked, but I could see the logic and made the following
change:
"ON A.Rank = B.Rank -1" to "ON A.Rank -1 = B.Rank ". And that worked fine.
This code will have many uses.
Thank you

Dave

John Spencer said:
I'm not sure this will work, but you can try the following solution


First Query (Saved as QRank):

SELECT A.Date, A.Code, Count(B.Date) as Rank
FROM [Your Table] as A LEFT JOIN [Your Table] As B
ON A.Date > B.Date
GROUP BY A.Date, A.Code

Second Query:
SELECT A.Date, A.Code
FROM QRank as A LEFT JOIN QRank as B
ON A.Rank = B.Rank -1
WHERE A.Code <> B.Code
OR B.Code is Null

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thank you for the queries...but they still give only the earliest date that
say "P" occurs and not the earliest dates when the CODE changes to "P"
throughout the ordered DATE field.

The order of the DATE field is relevant in that the CODE can change from "P"
to "C" then to "P" again, which is important.

Does the table need another field that make the DATE, CODE fields unique?

Dave
Click to expand...
Click to expand...
 
That just about worked, but I could see the logic and made the following
change:
'ON A.Rank = B.Rank -1" to "ON A.Rank -1 = B.Rank". And that worked fine.
This script will have many uses.

Thank you

Dave

John Spencer said:
I'm not sure this will work, but you can try the following solution


First Query (Saved as QRank):

SELECT A.Date, A.Code, Count(B.Date) as Rank
FROM [Your Table] as A LEFT JOIN [Your Table] As B
ON A.Date > B.Date
GROUP BY A.Date, A.Code

Second Query:
SELECT A.Date, A.Code
FROM QRank as A LEFT JOIN QRank as B
ON A.Rank = B.Rank -1
WHERE A.Code <> B.Code
OR B.Code is Null

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thank you for the queries...but they still give only the earliest date that
say "P" occurs and not the earliest dates when the CODE changes to "P"
throughout the ordered DATE field.

The order of the DATE field is relevant in that the CODE can change from "P"
to "C" then to "P" again, which is important.

Does the table need another field that make the DATE, CODE fields unique?

Dave
Click to expand...
Click to expand...
 
Back
Top