Solved

Duplicate Records in Access table

Posted on 2007-11-28
11
190 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 200 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 300 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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

751 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