I am pretty much an Access and SQL newbie. I have Access 2019. I have a query from multiple tables. Most of my joins are inner joins, but I need one left or right and I can't get it to work. Here are the tables in my query:
Here is a draft version of my query:
SELECT [tblAuthor]![Surname] & ", " & [tblAuthor]![GivenName] & " " & [tblAuthor]![OtherName(s)] AS FullName,
tblSection.Section, tblAuthor.BillsIntent, tblAuthor.RatingGoodreads AS tblAuthor_RatingGoodreads,
tblWork.Title, tblWork.YearPublished, tblWork.RatingGoodreads AS tblWork_RatingGoodreads,
tblReading.FormatHeld, tblReading.[Own?], tblReading.[Read?],
tblSeries.SeriesName, tblmmSeriesWork.OrderInSeries
FROM ((tblWork
INNER JOIN ((tblAuthor INNER JOIN tblmmAuthorWork ON tblAuthor.[AuthorID] = tblmmAuthorWork.[AuthorIDFK]) INNER JOIN tblSection ON tblAuthor.[SectionTypicalIDFK] = tblSection.[SectionID])
ON tblWork.[WorkID] = tblmmAuthorWork.[WorkIDFK])
INNER JOIN tblReading ON tblWork.[WorkID] = tblReading.[WorkIDFK])
INNER JOIN (tblSeries INNER JOIN tblmmSeriesWork ON tblSeries.[SeriesID] = tblmmSeriesWork.[SeriesIDFK]) ON tblWork.[WorkID] = tblmmSeriesWork.[WorkIDFK]
ORDER BY tblAuthor.BillsIntent;
This query works as far as it goes. However, the series info is not populated for every book (I use "work"). I don't want to limit the output to only rows where a series-work relation is populated in the many-to-many table tblmmSeriesWork. My naive approach was to use a LEFT JOIN to bring in the tblSeries stuff: "LEFT JOIN (tblSeries INNER JOIN tblmmSeriesWork ON...", but that fails, I guess because of the following INNER JOIN I take it. If I replace the series joining stuff with
"LEFT JOIN tblmmSeriesWork ON tblWork.[WorkID] = tblmmSeriesWork.[WorkIDFK]"
that works - I'm getting the output rows I want - but that just gets me an autonumber ID for Series, and I want tblSeries.SeriesName.
How can I fix my query to allow for blank Series.Name values in the output? Thanks.