MS SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesMS SQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old July 22nd, 2004, 02:43 PM
dustyreagan's Avatar
dustyreagan dustyreagan is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Oct 2003
Location: Austin
Posts: 126 dustyreagan User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 40 m 3 sec
Reputation Power: 5
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

Reply With Quote
  #2  
Old July 22nd, 2004, 11:46 PM
Username=NULL Username=NULL is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Apr 2004
Location: TX
Posts: 249 Username=NULL User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 7 h 6 m 42 sec
Reputation Power: 5
Send a message via Yahoo to Username=NULL
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?

Reply With Quote
  #3  
Old July 23rd, 2004, 07:55 PM
r937's Avatar
r937 r937 is offline
SQL Consultant
Click here for more information.
 
Join Date: Feb 2003
Location: Toronto Canada
Posts: 17,581 r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level) 
Time spent in forums: 1 Month 3 Weeks 6 Days 5 h 20 m 45 sec
Reputation Power: 949
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
__________________
r937.com | rudy.ca

Last edited by r937 : July 24th, 2004 at 06:45 PM.

Reply With Quote
  #4  
Old July 26th, 2004, 09:42 PM
dustyreagan's Avatar
dustyreagan dustyreagan is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Oct 2003
Location: Austin
Posts: 126 dustyreagan User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 40 m 3 sec
Reputation Power: 5
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.

Reply With Quote
  #5  
Old July 28th, 2004, 12:14 AM
Username=NULL Username=NULL is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Apr 2004
Location: TX
Posts: 249 Username=NULL User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 7 h 6 m 42 sec
Reputation Power: 5
Send a message via Yahoo to Username=NULL
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?

Reply With Quote
  #6  
Old July 28th, 2004, 12:25 AM
Username=NULL Username=NULL is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Apr 2004
Location: TX
Posts: 249 Username=NULL User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 7 h 6 m 42 sec
Reputation Power: 5
Send a message via Yahoo to Username=NULL
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.

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesMS SQL Development > Can I write multiple 'Select' queries in Access? Please Help!


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 5 hosted by Hostway