SQL query/join syntax

"Spektre" wrote in message
news
Ken,

SUCCESS!!

sqlStr = "SELECT publishers.PubName, COUNT(*) FROM (((collection INNER
JOIN
issues ON collection.IssueID = issues.ID) INNER JOIN series ON
issues.SeriesID = series.ID) INNER JOIN publishers ON publishers.ID =
series.PubID) GROUP BY publishers.PubName ORDER BY COUNT(*)"

Works great...

Congratulations for persevering to the solution!



Could still use some help on the "Episodes not collected" query

Try this query (based on your updated table structure information):

SELECT Publisher.ID, Publisher.PubName,
Series.ID, Series.Bk_Name, Issues.ID
FROM ((Publisher INNER JOIN Series
ON Publisher.ID = Series.PubID) INNER JOIN
Issues ON Series.ID = Issues.SeriesID)
LEFT JOIN [Collection] ON
Issues.ID = [Collection].IssueID
WHERE [Collection].IssueID IS NULL
AND Series.ID IN
(SELECT DISTINCT I.SeriesID
FROM Issues AS I INNER JOIN
[Collection] AS C ON
I.ID = C.IssueID)


--

Ken Snell
MS ACCESS MVP