473,320 Members | 1,854 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,320 software developers and data experts.

Is this query possible? Please Help!

I'm trying to create a query that finds rows w/ duplicate
"ContactKeys" then finds duplicate "AddressLine1s" out of the list of
duplicate "ContactKeys." (I tried subqueries but it was really slow)

I am trying to create a new table with only duplicate ContactKey rows,
and then I wanted to use that table to pick out the duplicate
AddressLine1 rows.

****** BEGIN CODE **************
SELECT *
INTO dupContactKeys
FROM Contacts
WHERE ContactKey IN (
SELECT ContactKey
FROM Contacts
GROUP BY ContactKey
HAVING COUNT(*) > 1)

SELECT *
FROM dupContactKeys
WHERE ContactKey IN (
SELECT AddressLine1, Zip
FROM Contacts
GROUP BY AddressLine1, Zip
HAVING COUNT(*) > 1)
ORDER BY ContactKey, TypeKey;

drop table dupContactKeys
****** END CODE *****************

This of course doesn't work. Please help, as I am going slightly mad!

Dusty
Nov 13 '05 #1
1 1230
In the query-subquery scenario, have you tried:

SELECT C1.*
FROM Contacts AS C1
WHERE EXISTS (
SELECT C2.ContactKey
FROM Contacts AS C2
GROUP BY C2.ContactKey
HAVING C2.ContactKey=C1.ContactKey AND Count(C2.ContactKey)>1;)
AND EXISTS (
SELECT C3.AddressLine1
FROM Contacts AS C3
WHERE C3.ContactKey=C1.ContactKey
GROUP BY C3.AddressLine1
HAVING C3.AddressLine1=C1.AddressLine1 AND Count(C3.AddressLine1)>1;)
ORDER BY ContactKey, TypeKey;
See if this works for you.

"Dusty Reagan" <no*******@yahoo.com> wrote in message
news:5a**************************@posting.google.c om...
I'm trying to create a query that finds rows w/ duplicate
"ContactKeys" then finds duplicate "AddressLine1s" out of the list of
duplicate "ContactKeys." (I tried subqueries but it was really slow)

I am trying to create a new table with only duplicate ContactKey rows,
and then I wanted to use that table to pick out the duplicate
AddressLine1 rows.

****** BEGIN CODE **************
SELECT *
INTO dupContactKeys
FROM Contacts
WHERE ContactKey IN (
SELECT ContactKey
FROM Contacts
GROUP BY ContactKey
HAVING COUNT(*) > 1)

SELECT *
FROM dupContactKeys
WHERE ContactKey IN (
SELECT AddressLine1, Zip
FROM Contacts
GROUP BY AddressLine1, Zip
HAVING COUNT(*) > 1)
ORDER BY ContactKey, TypeKey;

drop table dupContactKeys
****** END CODE *****************

This of course doesn't work. Please help, as I am going slightly mad!

Dusty

Nov 13 '05 #2

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

Similar topics

20
by: | last post by:
If I need to check if a certain value does exist in a field, and return either "yes" or "not" which query would be the most effestive?
11
by: Surajit Laha | last post by:
I am firing a query like: SELECT TaskName, StartDate FROMTasks WHERE StartDate >= '01-Aug-2003' Now the result comes as: TaskName StartDate -------------------------- Task1 ...
5
by: anthonyberet | last post by:
I work for an organisation that uses a bespoke document imaging system, the database of which is an MS sql server. We have MS Access and already use it for some querying of the database. The...
4
by: sah | last post by:
I need some help with the following query: DECLARE @SRV VARCHAR(20), @date smalldatetime SET @SRV = (select @@servername) SET @date = '20040901' select Srv_Name = @SRV, DB_Name = 'DB_NAME',...
15
by: plaztik8 | last post by:
Hello, Can someone please help me with a query? The table looks like this: BookedRooms =========== CustomerID RoomID BookDateID
4
by: Max Harvey | last post by:
Hi, I have looked at the example called "Open Parameter queries from code" from the site http://www.mvps.org/access/queries/qry0003.htm I made up a test which I though looked pretty close...
5
by: Rated R1 | last post by:
I wrote this before in the NGs, so I am going to paste the responses that I got and see if someone can please help me. Email me and we can set something up as Id even be willing to pay for your...
4
by: d.p. | last post by:
Hi all, I'm using MS Access 2003. Bare with me on this description....here's the situation: Imagine insurance, and working out premiums for different insured properties. The rates for calculating...
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
6
by: tizmagik | last post by:
I am having a lot of difficulty generating a CrossTab Query based report. I have looked online for several tutorials and whatnot but I have not been able to really find what I'm looking for, nor...
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...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
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: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
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...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.