Just to educate myself, I'll try an experiment later with unmatched data on purpose so I can see how that condition resolves using the unmatched data query.
What should I expect?
Not really sure where your confusion is.
If you create an actual relationship between two tables (on the ribbon: Database Tools -> Relationships, then drag the parent field (tblParts.PartID, for example) to the child/many table over the field you want to link it to (tblAssemblyItems.PartID), drop, and the relationship will be created. Then double-click the link itself make sure 'enforce referential integrity' is on when you select the relationship itself), Access literally prevents you from adding records to the child/many side without a matching entry in the parent/one side.
You can also change how the link works when used in queries by double clicking the join, selecting 'Join Options', then choosing from three options:
- 'Only include rows where both sides are equal' does just what it says. This is called an INNER JOIN.
- You can show all records from the table listed on the left and only matching ones from the table on the right. This is a LEFT OUTER JOIN, or LEFT JOIN. (They mean the same thing.)
- You can show all records from the table listed on the right and only matching ones from the table on the left. This is a RIGHT OUTER JOIN, or RIGHT JOIN.
Note that your find unmatched query uses a right join to do its thing. The two outer joins are used to show 'optional' data. One example: I work with medical claims being reviewed to see if they should have been paid by Medicare instead of my company. Most claims are done correctly, but a number turn out to have been sent to us incorrectly. For those, we generate what's called an 'A/R File' and send it to the Claims department, and we need to track when those files were created and, if necessary, followed up on. So you can create a query that joins the claims table we use with the ARFileHistory table. If I want to see only the claims that had an AR sent, I would use an INNER join. If I want to see all claims, along with the last AR we sent (if any), I would use an OUTER JOIN (I typically use LEFT JOINS; it's a personal preference, but most people do seem to use that convention).
Also in that screen when you double click a relationship are three options: enable referential integrity, cascade update joined fields, and cascade delete joined fields.
ENFORCE REFERENTIAL INTEGRITY
'Enforce referential integrity' makes Access reject any inserts or updates that would result in an invalid join, meaning a value on the MANY end that doesn't have a matching value on the ONE end.
As an example related to your application, if you have 'enforce referential integrity' turned on for the relationship and try to create an Assembly item with PartID 8765301, but tblParts has no part with that ID, you'll get an error message and the record won't be created. The same thing happens if you try to change PartID in tblAssemblyItems to one not in tblParts.
CASCADE UPDATE RELATED FIELDS
This one is pretty straightforward. If you update the related field on the ONE side, then all matching fields on the MANY side are updated to the same value. So if you edit tblParts.PartID from 41 to 141, then all records in tblAssemblyItems that have PartID 41 will ALSO have PartID changed to 141. This keeps the data good (data integrity) and prevents records from being accidentally orphaned. I always use this one.
CASCADE DELETE RELATED RECORDS
Again, straightforward. With this on, if you delete a record from the ONE side, then all related records from the MANY side are also deleted. I always leave this one off to help minimize the damage from accidental deletions. Instead, if I want to delete something and all related records, I do them in two separate deletes. There are others here who keep it enabled. Both approaches have uses and risks, and in the end really should be selected between based on the needs of the application and your data integrity requirements.
If you use relationships wherever possible and always enforce referential integrity (and, if necessary, the cascade options), you should find that you won't often have use for find unmatched queries to find orphaned records. It's still a useful concept to know, though, in case you ever need to create, say, a list of all parts that aren't used in any of your assemblies.