Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Duplicate Records in Access table

Posted on 2007-11-28
11
Medium Priority
?
192 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

721 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