Reverse the query and make it a many-to-one relationship. Assuming your chapters table has a "chapter" field one or more "section" fields per chapter, then you left join the from the section table to the chapter table on the section field, and you display the section field from the section table and the chapter field from the chapter table.
The result is all of your sections and the chapter(s) that they are associated with, if any. Sections with a blank in the chapter field have yet to be assigned.
You need to use an outer join between your tables. The Access default is an inner join which only returns data where there is matching data in both tables. If you use an outer join (type 2 or 3 in Access terms), you specify that you want all of the records from sections and those records from chapters that match.
"...then you left join the from the section table..."
That's a "type 2" in Access. Sorry if it was confusing. Also note that an outer join and a left join are two different things and Access will not inherently handle a true outerjoin like say, SQL Server or DB2 will.
"...then you left join the from the section table..."
That's a "type 2" in Access. Sorry if it was confusing. Also note that an outer join and a left join are two different things and Access will not inherently handle a true outerjoin like say, SQL Server or DB2 will.
Sorry Moniker. I must have been speed reading and missed that in your post.
But a left join is a specific form of outer join, AFAIK. Left outer, right outer and full outer are the variations. Or am I wrong, since I've never had any formal db training in my life!
Correct on the full terminology. Briefly, here's the quick rundown for everyone (examples below):
INNER JOIN:
In English: Show me all the records only where there is a match in both tables .
LEFT OUTER JOIN
In English: Show me all the records on the left table and only the matching results from the right table.
RIGHT OUTER JOIN:
In English: The reverse of a LEFT OUTER JOIN; Show me all the records on the right table and only the matching results from the left table.
FULL OUTER JOIN:
In English: Return all the matching row from each table, and also return a copy of each non-matching row from both tables.
Sample Output:
Code:
INNER JOIN NameTable.ID ON JobTable.ID:
+-----------+ +---------+ ====================
|ID|NAME | |ID|JOB | ID NAME ID JOB
|--|--------| |--|------| ==========> -- -------- -- -----
|10|Sanders | |20|Sales | 20 Pernal 20 Sales
|20|Pernal | |30|Clerk | 30 Marenghi 30 Clerk
|30|Marenghi| |30|Mgr | 30 Marenghi 30 Mgr
+-----------+ |40|Sales |
|50|Mgr |
+---------+
LEFT JOIN NameTable.ID ON JobTable.ID:
+-----------+ +---------+ ======================
|ID|NAME | |ID|JOB | ID NAME ID JOB
|--|--------| |--|------| =========> -- -------- -- -----
|10|Sanders | |20|Sales | 10 Sanders - -
|20|Pernal | |30|Clerk | 20 Pernal 20 Sales
|30|Marenghi| |30|Mgr | 30 Marenghi 30 Clerk
+-----------+ |40|Sales | 30 Marenghi 30 Mgr
|50|Mgr |
+---------+
RIGHT JOIN NameTable.ID ON JobTable.ID:
+-----------+ +---------+ =======================
|ID|NAME | |ID|JOB | ID NAME ID JOB
|--|--------| |--|------| =========> -- -------- -- -----
|10|Sanders | |20|Sales | 20 Pernal 20 Sales
|20|Pernal | |30|Clerk | 30 Marenghi 30 Clerk
|30|Marenghi| |30|Mgr | 30 Marenghi 30 Mgr
+-----------+ |40|Sales | - - 40 Sales
|50|Mgr | - - 50 Mgr
+---------+
FULL OUTER JOIN NameTable.ID ON JobTable.ID (not inherently there in Access,
but can be done by combining the results of a LEFT and RIGHT query)
+-----------+ +---------+ ======================
|ID|NAME | |ID|JOB | ID NAME ID JOB
|--|--------| |--|------| =========> -- -------- -- -----
|10|Sanders | |20|Sales | 10 Sanders - -
|20|Pernal | |30|Clerk | 20 Pernal 20 Sales
|30|Marenghi| |30|Mgr | 30 Marenghi 30 Clerk
+-----------+ |40|Sales | 30 Marenghi 30 Mgr
|50|Mgr | - - 40 Sales
+---------+ - - 50 Mgr