Query omitting records where FK unassigned

[Pawtang]

I have what to me feels like a bit of a hairy problem here, but I'm sure to some of you folks will be a simple solution.



I have a query used as the basis of a search form that searches all of my records based on a variety of optional criteria.

In the returns process, the field failureCategory is not filled out until the part has been evaluated. So it's possible for a record to temporarily have no related value in that field. But because I reference the table tblFailureCategory to relate the name of the category to the numeric value stored in the tblPartsReturned table, this query is not returning any tblPartsReturned records where the failureCategory is not defined. When I run the query, I have ten fewer records displayed than the total amount of records in tblPartsReturned, and that's how many records in tblPartsReturned have not yet been assigned a failureCategory.

My thought would be to establish a default value for failureCategory that acts as an undefined placeholder, with an ID of 0, and some sort of textual placeholder (or I suppose empty?) for its related text field. Is this a smart way to do this?

Here is a view of the relationships:


Here is the query, which was built by the design view:

SELECT tblRMAs.RMA_nb, tblRMAs.cust_nb, tblPartsReturned.partNumber, tblRMAs.dateReceived, tblFailureCategory.failureCategory, tblPartsReturned.credit, tblRMAs.status, tblRMAs.flagFROM tblRMAs INNER JOIN (tblFailureCategory INNER JOIN tblPartsReturned ON tblFailureCategory.ID = tblPartsReturned.failureCategory) ON tblRMAs.RMA_nb = tblPartsReturned.RMA_nb;

[mike60smart]

Hi

Change the Join to an OUTER JOIN

[Pawtang]

Hi

Change the Join to an OUTER JOIN

Cannot do an OUTER JOIN here but a RIGHT JOIN did the trick in the nested join