How can I show missing matches within my query results?

[Guest]

Hello,

I need some help with a query. I have two tables and I'm trying to figure
out how many pallets of product I have. This means that for every SKU in
stock, I need to have a pallet equivalent (units per pallets) for each sku.
Within my query, I'd like to retrieve the SKUs that are missing a pallet
equivalent so that I can easily see where I'm missing a match. Given my
example below, I know how to link my tables for SKUs A,B, and C to get their
pallet equivalents. What I don't know how to do is for SKU D, how can I get
my query to show "missing units per pallet" (see Query 1 desired input). SKU
D doesn't have a match because it's a SKU in inventory (in Table 1) that
doesn't have a pallet equivalent (missing from Table 2).

TABLE 1:
SKU QTY
A 90
B 50
C 130
D 45

TABLE 2:
SKU Qty per Pallet
A 9
B 10
C 10

QUERY 1 - desired output
SKU PALLETS
A 10
B 5
C 13
D "missing units per pallet" <--- This is the step I can't
figure out in my query

Thanks in advance for your input.
-Scott

 

[pietlinden]

use an outer join and NZ...

SELECT Table1.SKU, Table1.Qty, Nz([Table2]![QtyPerPallet],0) AS
FixedQty, [Qty]-nz([QtyPerPallet],0) AS Remaining
FROM Table1 LEFT JOIN Table2 ON Table1.SKU = Table2.SKU;