Detect Rare Changes of Status in Very Large Recordset 1

[NWildblood]

I have a very large table that has five rows per Identifier (five rows represents five days' activity). There are many identifiers in the identifier column. Next to the identifier column is a "status" column. The (vast) majority of the time the status remains the same against each identifier, however I need some SQL to help report on any change in status against the occasional identifiers where this status change has happened.

Any help gratefully received !


(Note - I originally posted this last night in the Oracle forum as an SQL Developer solution, however the recommended solution contains

RANK () OVER ( PARTITION BY Id ORDER BY Status) Rk

On reading other forums, I have discovered that the SQL Query is entirely processed in MS Access, where the large dataset table sits. However MS Access does not support the RANK() OVER function)

So I’ve therefore come here to see if any MS Access Query/SQL gurus can help out….


No hay nada como un buitre cabrón como un ojo de cristal.

 

[PHV]

Something like this ?

SELECT IADM_UNIQUE_ID, REJECTION_REASON 
FROM Rejection_File_Master
GROUP BY IADM_UNIQUE_ID, REJECTION_REASON
HAVING Count(*)<5

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NWildblood]

That'll do nicely ! Many thanks PHV


No hay nada como un buitre cabrón como un ojo de cristal.