473,327 Members | 1,967 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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
Nov 12 '05 #1
2 2728
On 27 Apr 2004 08:44:22 -0700, ja**********@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.

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


Nov 12 '05 #2
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" <ja**********@prudential.com> wrote in message
news:1a*************************@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
Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Rado | last post by:
Hi All, This might quite simple process for some but I am finding it really difficult to do. What is required is not a standard Duplicate query but a variation on it. For example I have...
8
by: Riegnman | last post by:
Hey guys, I'm in need of a little help. I am very new to access but have been trying to learn. My problem is as follows. . . We have time clocks that dump the badge punches into a .log file on...
2
by: Jason | last post by:
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). ...
0
by: Dale Ring | last post by:
Sorry about the dup post, somehow my original post was put on an existing thread instead of a new one. Access 2000 I am working with a service call database and would like to create a query...
0
by: Dale Ring | last post by:
Sorry about the dup post, somehow my original post was put on an existing thread instead of a new one. Access 2000 I am working with a service call database and would like to create a query...
8
by: Maxi | last post by:
There is a lotto system which picks 21 numbers every day out of 80 numbers. I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21) Here is the structure and sample data: ...
2
by: steevp | last post by:
Hi, Please excuse the newbie type question, but I am wracking my brains to solve what should be a simple query. I have three fields in a table F1, F2, F3. Any of the fields may contain...
5
by: redstamp | last post by:
Try as I might I cannot find a way to write an access query to return a result set with the records from my database WITHOUT a certain set of values within a field. To explain, I have a table of...
4
by: BeeMarie | last post by:
Hi, I've been trying to figure out this query for several days. I'm not that familiar with writing SQL statements, but in order to get the results I want, I think I need a subquery which requires...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.