Query using historical table

[kwilliams5675]

I am running a query with two tables:



1. Historical table of status codes and date status code changed.
2. Dataset of records.

I want to return only the records that have are currently sitting at an O status and exclude them if they have an S status..

Example:

Record 12345 was on O status on 03/13/2009 and changed to an S status on 06/26/2010... If it changed to an S status, I want to exclude that record from my dataset.

What criteria would be used to accomplish this?

[ConneXionLost]

First, build a query [qryMaxStatusDate] from your historical table that returns only the recordID and the statusdate fields. Set the recordID to "Group By" and the statusdate to "MAX".

Second, build another query [qryCurrentStatus] that joins the historical table with your [qryMaxStatusDate] on the recordID and the StatusDate - MaxOfStatusDate. Return everything from the historical table.

Finally, Build a third query [qryCurrentRecordStatus] that joins your "dataset of records" with [qryCurrentStatus] on the recordID. Add a criteria to the status field of "O".

Cheers,