Union Query

[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!

 

[John W. Vinson]

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!

A UNION query would show demands and receipts in separate records, even if
they're in the same week - probably not ideal, but if that's what you want,
try

SELECT [Part#], Fiscal_Week, Fiscal_Year, Receipts AS Amount,"Received" AS
Type FROM QTY_RECIEVED
UNION ALL
SELECT [Part#], Fiscal_Week, Fiscal_Year, Demand AS Amount, "Demanded" AS Type
FROM SUPPLY_REQUIREMENT
ORDER BY Fiscal_Year, Fiscal_Week;

To get the data from both tables into one record you'll need to UNION two
outer-join queries:

SELECT [QTY_RECEIVED].[Part#],[QTY_RECEIVED].Fiscal_Week,
[QTY_RECEIVED].[Fiscal_Year], [QTY_RECEIVED].Receipts,
[SUPPLY_REQUIREMENT].Demand
FROM [QTY_RECEIVED] LEFT JOIN [SUPPLY_REQUIREMENT]
ON [QTY_RECEIVED].[Part#] = [SUPPLY_REQUIREMENT].[Part#]
AND [QTY_RECEIVED].[Fiscal_Year] = [SUPPLY_REQUIREMENT].[Fiscal_Year]
AND [QTY_RECEIVED].[Fiscal_Week] = [SUPPLY_REQUIREMENT].[Fiscal_Week]
UNION
SELECT [SUPPLY_REQUIREMENT].[Part#],[SUPPLY_REQUIREMENT].Fiscal_Week,
[SUPPLY_REQUIREMENT].[Fiscal_Year], [QTY_RECEIVED].Receipts,
[SUPPLY_REQUIREMENT].Demand
FROM [QTY_RECEIVED] RIGHT JOIN [SUPPLY_REQUIREMENT]
ON [QTY_RECEIVED].[Part#] = [SUPPLY_REQUIREMENT].[Part#]
AND [QTY_RECEIVED].[Fiscal_Year] = [SUPPLY_REQUIREMENT].[Fiscal_Year]
AND [QTY_RECEIVED].[Fiscal_Week] = [SUPPLY_REQUIREMENT].[Fiscal_Week]


John W. Vinson [MVP]

 

[tyler.deutsch]

Hi,

Yes, that seemed to work, but now I am receiving multiple entries for
both Demand and Receipts. Do you know what could be causing that?
There should only be one Demand and one Receipt per week, but I am
getting multiple entries per week.

Thanks

 

[John W. Vinson]

Hi,

Yes, that seemed to work, but now I am receiving multiple entries for
both Demand and Receipts. Do you know what could be causing that?
There should only be one Demand and one Receipt per week, but I am
getting multiple entries per week.

Thanks

Please post your SQL. I'd guess you're joining to some other table.

John W. Vinson [MVP]

 

[tyler.deutsch]

Here is my Query:

The 2 tables are RECEIPTS and SUPPLY_REQUIREMENT.

The two columns that I want to include only one result for each week
are QTY_RECEIVED and DEMAND.


SELECT [RECEIPTS].[Part#], [RECEIPTS].FISCAL_WEEK_NUM,
[RECEIPTS].[FISCAL_YEAR_NUM], [RECEIPTS].QTY_RECEIVED,
[SUPPLY_REQUIREMENT].DEMAND
FROM [RECEIPTS] LEFT JOIN [SUPPLY_REQUIREMENT]
ON [RECEIPTS].[Part#] = [SUPPLY_REQUIREMENT].[Part#]
AND [RECEIPTS].[FISCAL_YEAR_NUM] = [SUPPLY_REQUIREMENT].
[FISCAL_YEAR_NUM]
AND [RECEIPTS].[FISCAL_WEEK_NUM] = [SUPPLY_REQUIREMENT].
[FISCAL_WEEK_NUM]
UNION SELECT [SUPPLY_REQUIREMENT].[Part#],
[SUPPLY_REQUIREMENT].FISCAL_WEEK_NUM,
[SUPPLY_REQUIREMENT].[FISCAL_YEAR_NUM], [RECEIPTS].QTY_RECEIVED,
[SUPPLY_REQUIREMENT].DEMAND
FROM [RECEIPTS] RIGHT JOIN [SUPPLY_REQUIREMENT]
ON [RECEIPTS].[Part#] = [SUPPLY_REQUIREMENT].[Part#]
AND [RECEIPTS].[FISCAL_YEAR_NUM] = [SUPPLY_REQUIREMENT].
[FISCAL_YEAR_NUM]
AND [RECEIPTS].[FISCAL_WEEK_NUM] = [SUPPLY_REQUIREMENT].
[FISCAL_WEEK_NUM];


Let me know what you think,
Thanks

 

[John W. Vinson]

Here is my Query:

The 2 tables are RECEIPTS and SUPPLY_REQUIREMENT.

The two columns that I want to include only one result for each week
are QTY_RECEIVED and DEMAND.


SELECT [RECEIPTS].[Part#], [RECEIPTS].FISCAL_WEEK_NUM,
[RECEIPTS].[FISCAL_YEAR_NUM], [RECEIPTS].QTY_RECEIVED,
[SUPPLY_REQUIREMENT].DEMAND
FROM [RECEIPTS] LEFT JOIN [SUPPLY_REQUIREMENT]
ON [RECEIPTS].[Part#] = [SUPPLY_REQUIREMENT].[Part#]
AND [RECEIPTS].[FISCAL_YEAR_NUM] = [SUPPLY_REQUIREMENT].
[FISCAL_YEAR_NUM]
AND [RECEIPTS].[FISCAL_WEEK_NUM] = [SUPPLY_REQUIREMENT].
[FISCAL_WEEK_NUM]
UNION SELECT [SUPPLY_REQUIREMENT].[Part#],
[SUPPLY_REQUIREMENT].FISCAL_WEEK_NUM,
[SUPPLY_REQUIREMENT].[FISCAL_YEAR_NUM], [RECEIPTS].QTY_RECEIVED,
[SUPPLY_REQUIREMENT].DEMAND
FROM [RECEIPTS] RIGHT JOIN [SUPPLY_REQUIREMENT]
ON [RECEIPTS].[Part#] = [SUPPLY_REQUIREMENT].[Part#]
AND [RECEIPTS].[FISCAL_YEAR_NUM] = [SUPPLY_REQUIREMENT].
[FISCAL_YEAR_NUM]
AND [RECEIPTS].[FISCAL_WEEK_NUM] = [SUPPLY_REQUIREMENT].
[FISCAL_WEEK_NUM];

I assume that there are two or more records in [SUPPLY_REQUIREMENT] for each
[PART#]. If you want to include only one result for each week, and there are
multiple records, WHICH record do you want to include? How can Access tell?

John W. Vinson [MVP]