Adding field to a query without filtering down records

[davyre]

Hi,
I have this query

SELECT TblCustOrder.PONumber, TblCustOrderUnit.UnitID, TblUnits.ModelID, TblUnits.BSUnitID, TblUnits.UnitDescription, TblCustOrderUnit.OrderMethod, TblCustOrder.CustomerUDD, TblCustomer.CustomerName, TblCustOrderUnit.Qty, TblCustOrder.CustOrderDate, TblCustOrder.CustOrderID
FROM TblUnits INNER JOIN ((TblCustomer INNER JOIN TblCustOrder ON TblCustomer.CustomerID = TblCustOrder.CustomerID) INNER JOIN TblCustOrderUnit ON TblCustOrder.CustOrderID = TblCustOrderUnit.OrderID) ON TblUnits.UnitID = TblCustOrderUnit.UnitID
ORDER BY TblCustOrder.CustomerUDD;

Lets assume this is the query that I wanted. Now I want to add one field from another table called TblAllocation. In this table, there are similar fields (i.e UnitID, OrderID, CustOrderID) but contains fewer records (because not all OrderID has been allocated). When I click SHOW TABLE button in the Design View, and I add this TblAllocation, the records were simply reduced/filtered down because it checks the matching orderID and custOrderID. Before I add the table, there are 52 records, but after I add the table, it become only 20 records. How can I prevent this? What I want is to keep the query to show 52 records, and add a field from TblAllocation (the field name is Qty). If the CustOrderID and OrderID matches from old query to TblAllocation, then show the Qty. If not then just leave it blank instead of remove it from query result.
Any idea?
Thanks

 

[davyre]

OK I found a solution using OUTER JOIN. Here's my new SQL

SELECT TblCustOrder.PONumber, TblCustOrderUnit.UnitID, TblUnits.ModelID, TblUnits.BSUnitID, TblUnits.UnitDescription, TblCustOrderUnit.OrderMethod, TblCustOrder.CustomerUDD, TblCustomer.CustomerName, TblCustOrderUnit.Qty, TblCustOrder.CustOrderDate, TblAllocation.OrderID
FROM (TblUnits INNER JOIN ((TblCustomer INNER JOIN TblCustOrder ON TblCustomer.CustomerID = TblCustOrder.CustomerID) INNER JOIN TblCustOrderUnit ON TblCustOrder.CustOrderID = TblCustOrderUnit.OrderID) ON TblUnits.UnitID = TblCustOrderUnit.UnitID) LEFT JOIN TblAllocation ON TblCustOrderUnit.OrderID = TblAllocation.CustOrderID AND TblCustOrderUnit.UnitID = TblAllocation.UnitID
ORDER BY TblCustOrder.CustomerUDD;

Thanks