|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
||||
|
||||
|
Can I write multiple 'Select' queries in Access? Please Help!
I'm trying to code a query in Access 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. 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 This of course doesn't work. Please help, as I am going slightly mad!
__________________
DustyReagan.com |
|
#2
|
|||
|
|||
|
run the Select INTO first, in a seperate qry, then run the qry to select the desired records. If I remember correctly Access doesn't support multiple queries, must run each individually.
Or have you tried that already? |
|
#3
|
||||
|
||||
|
yeah, i think you have to run them separately
but that's not the main problem you cannot say this: WHERE ContactKey IN ( SELECT AddressLine1, Zip FROM Contacts GROUP BY AddressLine1, Zip HAVING COUNT(*) > 1) the subquery in this construction is allowed to return only one column and neither of the columns contains ContactKey values, so it wouldn't work anyway Edit: fix typo -- only one column Last edited by r937 : July 24th, 2004 at 06:45 PM. |
|
#4
|
||||
|
||||
|
Thanks for the replies! I found a solution to this problem.
But just out of curiosity, since I'm new to SQL, could someone give me an example of a subquery with multiple variables in the SELECT statement? What I was hoping to do in that statement was group AddressLine1 and Zip to find duplicates, then use those contact keys to list all the duplicates without being grouped. |
|
#5
|
|||
|
|||
|
Here's an example on how to find dups...basically group on the fields that will define a dup record, and do a count on a field that can uniquely id each row.
Here's a sample table w/some data... Code:
tCustomers --------- ID --int Identity (PK) Name --varChar address --varChar ID Name Address -- ---- ------- 1 John here 2 Adam there 3 Kelly overThere 4 John here 5 John here 6 Bonnie somewhere 7 Kelly overThere 8 Paul somewhere Else 9 Paul somewhere Else 10 Tracy nowhere To find the records that have dup info (in this case simply dup name and address), group by them while doing a count on the ID (unique value in the table)... Code:
select Name, Address, count(distinct ID) as cntID from Customers group by Name, Address having count(distinct ID) > 1 rs Name Address cntID ---- ------- ----- John here 3 Kelly overThere 2 Paul somewhere Else 2 To return to the orig table, I usually just join the table on the above qry using the fields I grouped on, and you're back @ the table viewing the records w/dup info... Code:
select ID, name, address from tCustomers C join ( select Name, Address, count(distinct ID) as cntID from Customers group by Name, Address having count(distinct ID) > 1 ) as A on C.Name = A.Name and C.Address = A.Address rs ID Name Address -- ---- ------- 1 John here 3 Kelly overThere 4 John here 5 John here 7 Kelly overThere 8 Paul somewhere Else 9 Paul somewhere Else ...is this the idea you were after? |
|
#6
|
|||
|
|||
|
oops...you're in Access, not SQL Server, syntax is a bit different.
In every case where I typed "join" replace that w/"inner join", and "count (distinct ID)" isn't allowed, simply use "count(ID)". Though since the ID is unique anyway, I don't even think the distinct was necessary in the above queries when I assumed SQL Server..."count (ID)" should work in both DB's. |
![]() |
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Can I write multiple 'Select' queries in Access? Please Help! |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|