Duplicate Records in Access table

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
pcssecureAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
imitchieConnect With a Mentor Commented:

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
 
peter57rCommented:
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
 
pcssecureAuthor Commented:
Hi Peter,
Thanks for the comment.
It almost give me what I want. But I need to output all the three columns.

Thanks
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
peter57rCommented:
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
 
balmasriCommented:
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
 
imitchieCommented:
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
 
pcssecureAuthor Commented:
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
 
pcssecureAuthor Commented:
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
 
imitchieCommented:
select * from Tbl where Title in (
select title
from Tbl
group by title
having count(distinct location) > 1
)
0
 
pcssecureAuthor Commented:
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
 
peter57rConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.