Query for duplicates across two tables (1 Viewer)

Notiophilus

Registered User.
Local time
Today, 11:36
Joined
Jan 18, 2015
Messages
42
I have two tables in a one-to-many relationship, tblBooks and tblBookAuthor. A book may have multiple authors, and several books may have the same title.

Code:
[FONT=Courier New][tblBook]                         [tblBookAuthor]
BookID (PK)  Title                [/FONT][FONT=Courier New][FONT=Courier New]BookID (PK)   AuthorID (PK)[/FONT]
-----------  --------------       [/FONT][FONT=Courier New][FONT=Courier New]------------  -------------[/FONT] 
1            The Stranger         [/FONT][FONT=Courier New][FONT=Courier New]1             11
[/FONT]2            Evolution            2             12
3            Evolution            2             13
4            The Stranger         3             14
                                  4             11
[/FONT]

A book is considered a duplicate when both Title and AuthorID are the same (i.e. books 1 & 4 above, but not books 2 & 3).
I'm trying to make a query that will list duplicate books, but I can't make it work. The duplicates wizard only works on one table, so I can get duplicate titles OR duplicate authors, but never both. The only solution seems to be to make a second query on the first:

Code:
[qryDupeTitle]
SELECT tblBook.Title, tblBook.BookID, tblBookAuthor.AuthorID
FROM tblBook INNER JOIN tblBookAuthor ON tblBook.BookID = tblBookAuthor.BookID
WHERE (((tblBook.[Title]) In (SELECT [AuthorID] FROM [tblBook] As Tmp GROUP BY [Title] HAVING Count(*)>1 )))
ORDER BY tblBook.Title;

And 

[qryDuplicates]
SELECT qryDupeTitle.AuthorID, qryDupeTitle.Title, qryDupeTitle.BookID
FROM qryDupeTitle
WHERE (((qryDupeTitle.AuthorID) In (SELECT [AuthorID] FROM [qryDupeTitle] As Tmp GROUP BY [AuthorID],[Title] HAVING Count(*)>1  And [Title] = [qryDupeTitle].[Title])))
ORDER BY qryDupeTitle.AuthorID, qryDupeTitle.Title;

Is it possible to make this into a single query? I tried a subquery but my SQL's pretty shaky and I'm not sure I understand how GROUP BY works.

(The reason I'm using a query and not just a DCount / DLookup is that I want to use a recordset to retrieve the BookIDs of the duplicates, and DLookup will only return one ID.)
 

apr pillai

AWF VIP
Local time
Today, 16:06
Joined
Jan 20, 2005
Messages
735
Use the Query Wizard, there you will find a specific option to FIND DUPLICATES QUERY WIZARD.
 

Notiophilus

Registered User.
Local time
Today, 11:36
Joined
Jan 18, 2015
Messages
42
Read my post: I used the query wizard, which gave me the two queries I listed above. The wizard doesn't let you make a single query for two tables, which is what I want.
 

sxschech

Registered User.
Local time
Today, 03:36
Joined
Mar 2, 2010
Messages
791
Create a base standard select query that joins the tables you need to produce the results and then use the query find duplicates wizard on that query.
 

Notiophilus

Registered User.
Local time
Today, 11:36
Joined
Jan 18, 2015
Messages
42
Again, see my original post. That is essentially what I did (though this is maybe a mite neater). What I want to know is: can I skip the intermediate query?
 
Last edited:

apr pillai

AWF VIP
Local time
Today, 16:06
Joined
Jan 20, 2005
Messages
735
Try the following Total Query SQL:
Code:
SELECT First([tblBook].BookID) AS FirstOfBookID, [tblBook].Title, [tblBookAuthor].AuthorID, Count([tblBookAuthor].AuthorID) AS CountOfAuthorID
FROM tblBook INNER JOIN tblBookAuthor ON [tblBook].BookID=tblBookAuthor.BookID
GROUP BY [tblBook].Title, [tblBookAuthor].AuthorID
ORDER BY First([tblBook].BookID);

The Query result image is attached. The Last column takes a count of duplicate authors. When duplicates are there the count will be more than 1.

Cross-check the table names and field names in the SQL is correct, make changes, if required.
 

Attachments

  • Duplicate Author.jpg
    Duplicate Author.jpg
    22.8 KB · Views: 108

Users who are viewing this thread

Top Bottom