Query for duplicates across two tables

[Notiophilus]

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.

[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:

[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]

Use the Query Wizard, there you will find a specific option to FIND DUPLICATES QUERY WIZARD.

 

[Notiophilus]

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]

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]

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?

 

[apr pillai]

Try the following Total Query SQL:

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.