Search code examples
ms-accesssubform

MS Access show entries in a subform that don't exist in the joined table

I am creating a SalesOrder form that uses a SalesOrderDetails subform for the line items. The part number and info are pulled from a joined inventory table.

Sometimes I'll need to add custom part numbers to the SalesOrderDetails. I don't want them added to the inventory table, I just want to add them to the sales order. This part actually seems to work fine, I can enter them and they appear in my SalesOrderDetails table. But when I refresh my SalesOrder form, the entries that do not have a match in the joined Inventory table will not appear in the subform anymore.

The query for the SalesOrderDetails Subform is

SELECT SalesOrderDetails.*
FROM Inventory INNER JOIN SalesOrderDetails ON Inventory.PartNumber = SalesOrderDetails.PartNumber;
Solution

  • I was looking for a RIGHT JOIN! This keeps my SalesOrderDetails data joined to the Inventory table, and shows all records from SalesOrderDetails.