I have a table of 650,000 records associated to contracts. Some of
the contracts have multiple records but each contract only has one
Active Record (there might be several inactive records). There are
dates associated with each of the records (whether active or
inactive). I need to compare the dates between the active and
inactive contract records.
So far, I've created a "find duplicates query" for contract to
identify contracts that have active and inactive records. I then
tried to create a new query and an expression as follows:
(([Date]) and ([Status])="Active") < (([Date]) and
([Status])="Inactive"
This does not work. What I realy need to do is to find any contract
where the Date associated with the Active record is less than the Date
associated with the Inactive record for the same contract. Can
someone help to create this query/expression?
Thanks,
Jason A. Uberti