Comparing certain fields within a duplicate records query

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

re: Comparing certain fields within a duplicate records query

On 27 Apr 2004 08:44:22 -0700, jason.uberti@prudential.com (Jason)
wrote:

Ad a sentence to your message: To download a sample version of my
database (Access version xxxxx), surf to: xxxxxxxxxxxx
For me at least, it is impossible to answer your question without
seeing the db design and some data in the relevant tables. Be sure NOT
to include all 650,000 rows, or we'll break out the tar and
feathers...

-Tom.

[color=blue]
>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[/color]

re: Comparing certain fields within a duplicate records query

do u have another table with only the unique records?
if yes,
u may try to compare this unique table with the 650000 records table and
pull out the duplicates.

--

Thank you.

______________________________________
Lawrence Lee


"Jason" <jason.uberti@prudential.com> wrote in message
news:1a0ee2b.0404270744.6130c296@posting.google.co m...
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