Expanding/Denormalizing

[croy]

I'm not even sure how to title this question...

Table_1 holds values for DATE, PLACE, TYPE_ID, SIZE_ID,
SHAPE_ID, and QUANTITY.

Table_2 (lookup) holds details for SIZE.

Table_3 (lookup) holds details for SHAPE.

Table_4 (lookup) has fields for TYPE_ID, SIZE_ID, SHAPE_ID,
and WEIGHT. Only those combinations of TYPE, SIZE and SHAPE
that have been weighed are present.

Now someone wants a query to return all the fields from
Table_1, plus the WEIGHT field from Table_4. But they want
all the Table_4 WEIGHT values to show for each record in
Table_1, displaying a QUANTITY of zero for for combinations
that are not present in Table_1.

I've run at this from so many different angles, I'm getting
wonky!

If anyone can understand my brain-fried explanation, and
cares to toss a bone, it would be much appreciated.

 

[Chris]

You would be better off to get rid of the lookup fields and just store the ID
in Table_1. If you do this, then you can get the data set you need by adding
Table_1 and Table_4 to a query with no joins. This gives you the Cartesian
set. You could use something like IIF(IsNull([WEIGHT]), 0, [WEIGHT]) to show
the value stored in WEIGHT or a zero.

Hope this makes sense.