Improve company productivity with a Business Account.Sign Up

x
?
Solved

Duplicate Records in Access table

Posted on 2007-11-28
11
Medium Priority
?
195 Views
Last Modified: 2010-03-20
I've an Access table with 3 Columns. Location, Title,  BookID.
Each Location can store multiple copies of the same title.
Similarly same title can be stored at multiple locations.

Now I need three quires.
1. All Titles with more than 1 copy.
2. All Titles with more than 1 copy from a particular location only.
3. Those titles ONLY found at different locations.

Consider following data
East, Book A, 1111
East, Book A, 2222
East, Book B, 3333
West Book C, 5555
Central, Book C, 666
Central, Book D, 777

Query 1 Should return Book A, Book C
Query 2 Should return Book A (For Location East)
Query 3 Should return Book C

I'he managed with first 2. The last one was a bit tricky.
Any help would be appreciated.

Regards
PCS
0
Comment
Question by:pcssecure
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 20365984
Select q.BookId, count(q.location) as Knt from
(Select Distinct Location, BookID from mytable) as Q
Group By BookId
Having Count(q.Location) >1
0
 

Author Comment

by:pcssecure
ID: 20366100
Hi Peter,
Thanks for the comment.
It almost give me what I want. But I need to output all the three columns.

Thanks
0
 
LVL 77

Expert Comment

by:peter57r
ID: 20366187
Select q.BookId,q.Title, count(q.location) as Knt from
(Select Distinct Location, BookID, Title from mytable) as Q
Group By BookId, Title
Having Count(q.Location) >1
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
LVL 5

Expert Comment

by:balmasri
ID: 20366864
To Return C>>>>>>>
select  table1.title AS Exp2,
FROM ( Select Distinct Location,Title from Table1.AS view1
group By title
having count(location)>1

0
 
LVL 25

Expert Comment

by:imitchie
ID: 20370394
I see that you have solved 1,2 yourself. For Q3, please have a look at

select title
from location
group by title
having count(*) = count(distinct location)
   and count(*) > 1

(must exist in multiple locations, and must have exactly one book per location)
0
 

Author Comment

by:pcssecure
ID: 20370767
Thanks for all the help. Looks like all the SQL statements got some errors.

Perter : only output 2 column. But I need all the columns and Rows.

balmasri: : Got Error in the select statement

imitchie: : Syntax error and concept wrong. Must return all the Records as long as the title exists in more than 1 location.
0
 

Author Comment

by:pcssecure
ID: 20370779
May be I wasn't clear enough.
I need to display all the records with all the columns of those titles exists in more than 1 location.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20370826
select * from Tbl where Title in (
select title
from Tbl
group by title
having count(distinct location) > 1
)
0
 

Author Comment

by:pcssecure
ID: 20370948
Thanks imitchie. But it generates an Error. If I remove distinct ir works but return all the duplicate records including only from same location. have you tested it  or am I missing something?
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 800 total points
ID: 20372135
The question has been answered based on the info you originally provided.

In order to provide an answer where ALL the fields are shown you have to list all the fields in the select statement because you are going to have use 'group by' on the Bookid and title and use a First() function on each of the other fields you want to display.
0
 
LVL 25

Accepted Solution

by:
imitchie earned 1200 total points
ID: 20372354

select Location, Title, Book
from Tbl
where Title in
(
  select Title from
  (
    select distinct Location, Title from y
  ) C
  group by Title
  having count(*) > 1
)

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Watch the video to know the process of migration of Exchange or Office 365 mailboxes in absence of MS Outlook. It is an eminent tool which can easily migrate Public, Archive user mailboxes from one another Exchange server and Office 365. Kernel Migr…

602 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question