My boss is grilling me to get this done. Can you help??

[tyler.deutsch]

Hi,

I need some help with a query. I believe that a Union Query is
appropriate, but I am unsure. I have two make table queries. One
makes a table called QTY_RECEIVED that has the following field names:
Part#, Fiscal_Week, Fiscal_Year, and Receipts.


The 2nd table is called SUPPLY_REQUIREMENT which has the following
fields: Part#, Fiscal_Week, Fiscal_Year, and Demand.


I want to see the history of Demand and Receipts for all the parts
that I have entered.


Problem is that some weeks there is no demand and some weeks there is
no Receipts.


If I link the two tables by Part# and Fiscal Week then it only shows
the the Weeks where this is both a Demand AND a Receipt.


If I try to change the Join Properties to include all records from
Demand and all from Receipts then it displays "ambiguous joins"
probably because my query is too complicated.


Please help me understand how to link these tables to show a Demand
and a Receipt everyweek even if the value for one of the two is null.


Thanks!

 

[Jeff Boyce]

One approach to handling this might be:

* create a query that lists all possible Part#, Fiscal_Week, Fiscal_Year
values for the date range you are reporting on.
* create another query that joins from the above query to each of your
two tables, using the directional join (ALL of the fields/rows from the
above query, plus ANY matching values for Receipts and Demands in their
respective tables.
* you can use the Nz() function to return a 0 instead of a Null if there
are any 'missing' values in your tables.

Regards

Jeff Boyce
Microsoft Office/Access MVP