Help creating query with aggregate function

[Fred Boer]

Hello!

I can't figure out how to do this...

A book can have multiple authors, but only one of these authors should be
marked as the Primary Author. Sometimes a book is mistakenly assigned more
than one primary author. I need a query which would return all Book_ID
numbers which have more than one Primary Author. I've been trying, but
haven't come close to getting something that works. I expect this needs a
Count() in the query but I keep running into errors about aggregate
functions, and I'm confused about how to structure this.

Thanks!
Fred Boer

Tbl_Library

Book_ID (Autonumber)
Title
Etc...

Tbl_Library

Author_ID (Autonumber)
AuthorFirstname
Etc...


Tbl_BookAuthor

BookAuthor_ID (Autonumber)
Book_ID
Author_ID
PrimaryAuthor (Boolean)

 

[Van T. Dinh]

I am guessing you have Many-to-Many relationship set-up. Try:

****Untested SQL****
SELECT B.*
FROM tblBook As B
WHERE B.BookID IN
(
SELECT BA.frg_BookID FROM tblBookAuthor AS BA
WHERE BA.Primary = True
GROUP BY frg_BookID
HAVING Count(frg_BookID) >= 2
)
****SQL Ends****

 

[Fred Boer]

Dear Van:

Thank you very much! That is what I need... I don't think I'd have figured
that out for a looong time. Obviously I need to work on my query skills..
(he said... adding it to a long, long list...)

Cheers!
Fred

 

[Van T. Dinh]

You'll get there in no time reading these newsgroups and skim through the
JET SQL Reference section in Access Help.