Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Duplicate Records in Access table

Posted on 2007-11-28
11
Medium Priority
?
193 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
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 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

886 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