Showing computed and non-computed data in one column with SQL

I have an MS Access database that has several tables. Of these tables, I have a bin table that shows 84 bins with the bin numbers being the primary key. It has another attribute for TotalCapacity. I have another table for inbound tickets that contains an attribute that accepts a number for bushels of commodity taken in. A third table has an attribute for bushels sent out.

SELECT BinNumber AS Bin, (TotalCapacity - SUM(BushelsIn) + SUM(BushelsOut)) AS SpaceRemaining
FROM T_BIN, T_INBOUND, T_OUTBOUND
WHERE T_BIN.BinNumber = T_INBOUND.BinNumber_FK AND T_BIN.BinNumber = T_OUTBOUND.BinNumber_FK
GROUP BY BinNumber, TotalCapacity;

This query works but only returns bins that have inbound or outbound tickets related to their bin number.

What I’m trying to do is get a list of all bins and their space available even if their total capacity is unchanged. I’ve tried every combination I can think of but I’m relatively new to SQL commands and can’t seem to find the right way to go about this.

Answer

You want LEFT JOIN. In MS Access, I think this looks like:

SELECT B.BinNumber AS Bin,
       (TotalCapacity - NZ(SUM(BushelsIn), 0) + NZ(SUM(BushelsOut), 0)
       ) AS SpaceRemaining
FROM (T_BIN AS B LEFT JOIN
      T_INBOUND AS I
      ON B.BinNumber = I.BinNumber_FK
     ) LEFT JOIN
     T_OUTBOUND AS O
     ON B.BinNumber = O.BinNumber_FK
GROUP BY B.BinNumber, B.TotalCapacity;

This introduces table aliases so the query is easier to write and to read.

The NZ() function converts NULL values to 0, so the SUM() is correct.