query a history table

[Guest]

Hello,
I am looking for a way to query the first record of an event change on a
table of history entries.

Here is a sample of the data.

event id status modified date
1234 new 1/2/2007
1234 open 1/3/2007
1234 open 1/4/2007
1234 fixed 1/7/2007
1234 open 1/8/2007
1234 open 1/9/2007
1234 fixed 1/10/2007
1234 closed 1/10/2007

I am looking for a query that would eliminate the subsequent records for
each status (so the 3rd and 6th records would be droped).

Thanks
Mark

 

[Jeff Boyce]

Mark

Are you saying you want the "earliest" modified date for each unique status?
If so, look into using Totals queries, grouping by status and using
"minimum" of the date field.

By the way, since all the example rows you gave have the same EventID, why
wouldn't you eliminate the 3rd, 5th, and 6th (they duplicate "open" on
1/3/2007), and eliminate the 7th (it duplicates the 4th)? I suspect the way
you would explain what to do to a person might be a bit more detailed...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

Hello,
I am looking for a way to query the first record of an event change on a
table of history entries.

Here is a sample of the data.

event id status modified date
1234 new 1/2/2007
1234 open 1/3/2007
1234 open 1/4/2007
1234 fixed 1/7/2007
1234 open 1/8/2007
1234 open 1/9/2007
1234 fixed 1/10/2007
1234 closed 1/10/2007

I am looking for a query that would eliminate the subsequent records for
each status (so the 3rd and 6th records would be droped).

Use a Subquery to select the earliest date for each status:

SELECT [Event ID], [Status], [Modified Date]
FROM yourtable
WHERE [Modified Date] =
(SELECT Min([Modified Date]) FROM yourtable As X
WHERE X.[Event ID] = yourtable.[Event ID]
AND X.Status = yourtable.Status);


John W. Vinson [MVP]

 

[Guest]

Hi Jeff,
What I am looking for is the earliset date for each time the status changes.
So, in the example I would need the records for the following.

1234 new 1/2/2007
1234 open 1/3/2007
1234 fixed 1/7/2007
1234 open 1/8/2007
1234 fixed 1/10/2007
1234 closed 1/10/2007

I need to know the first date, each time the status changed.
The fact that the event was opened and fixed a second time also need to be
recorded. That is why I did not wish to loose those records.

Mark

 

[Guest]

John,
I was able to get the query to run, however, it only pulled the earliest
date for each unique status. Is there a query that would pull the date for
each time the status changed? I would like these records so I know when the
status went from opened, to fixed, and then back to open, collecting all 3
dates.

Thanks
Mark

Hello,
I am looking for a way to query the first record of an event change on a
table of history entries.

Here is a sample of the data.

event id status modified date
1234 new 1/2/2007
1234 open 1/3/2007
1234 open 1/4/2007
1234 fixed 1/7/2007
1234 open 1/8/2007
1234 open 1/9/2007
1234 fixed 1/10/2007
1234 closed 1/10/2007

I am looking for a query that would eliminate the subsequent records for
each status (so the 3rd and 6th records would be droped).

Use a Subquery to select the earliest date for each status:

SELECT [Event ID], [Status], [Modified Date]
FROM yourtable
WHERE [Modified Date] =
(SELECT Min([Modified Date]) FROM yourtable As X
WHERE X.[Event ID] = yourtable.[Event ID]
AND X.Status = yourtable.Status);


John W. Vinson [MVP]

 

[Jeff Boyce]

Mark

One way to find when the status "changed" would be something of a
brute-force approach...

First, open a recordset containing all of EventID 1234's status records,
sorted by DateOfAction. (you could do the same for all your eventIDs,
sorting first by eventID)

Next, iterate through the records, one-by-one, checking if the status (?and
eventID) is the same as it was "last time through". If it is, move on. If
it is a different "status", write the data out to a new recordset/table (or
array, depending on how big your original data set is, whether you need
permanent info or are just processing the info, ...).

When this is done, only those that are different (i.e., changed) will be in
the new recordset.

(the above is untested. no warranty is expressed or implied. actual
mileage may vary.)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

John,
I was able to get the query to run, however, it only pulled the earliest
date for each unique status. Is there a query that would pull the date for
each time the status changed? I would like these records so I know when the
status went from opened, to fixed, and then back to open, collecting all 3
dates.

I'll defer to Jeff on this - as he says, It Ain't Easy.

John W. Vinson [MVP]

 

[Jamie Collins]

One way to find when the status "changed" would be something of a
brute-force approach...

First, open a recordset containing all of EventID 1234's status records,
sorted by DateOfAction. (you could do the same for all your eventIDs,
sorting first by eventID)

Next, iterate through the records, one-by-one, checking if the status (?and
eventID) is the same as it was "last time through". If it is, move on. If
it is a different "status", write the data out to a new recordset/table (or
array, depending on how big your original data set is, whether you need
permanent info or are just processing the info, ...).

When this is done, only those that are different (i.e., changed) will be in
the new recordset.

(the above is untested. no warranty is expressed or implied. actual
mileage may vary.)

I detect you find your own suggestion a little unsatisfactory too <g>.

The following uses a set-based approach:

SELECT DT1.[event id], DT1.status, DT1.[modified date],
DT1.previous_date, T3.status AS previous_status
FROM (
SELECT T2.[event id], T2.status, T2.[modified date], MAX(T1.[modified
date]) AS previous_date
FROM yourtable AS T1 RIGHT JOIN yourtable AS T2
ON T1.[event id] = T2.[event id]
AND T1.[modified date] < T2.[modified date]
GROUP BY T2.[event id], T2.status, T2.[modified date]
) AS DT1
LEFT JOIN yourtable AS T3
ON DT1.[event id] = T3.[event id]
AND DT1.previous_date = T3.[modified date]
WHERE DT1.status <> IIF(T3.status IS NULL, 'xxx', T3.status)
ORDER BY DT1.[event id], DT1.[modified date];

Jamie.

--