Union Query for predicted vs actual sales

[antonycat]

Hi All

Need some help generating a report.

I have a table of products with their planned sales by week no & customer.
I can then gather the actual sales from the Orders but I need to be able to produce a report of planned vs actual, where in some cases, there are planned but no actual sales and actual but no planned sales and include a balance on the report, so I need to convert the nulls to zeroes.

Can I use a Union query to generate the data I need or a series of append queries ?

tblPlanned
Product, PlannedSales, WeekNo, Customer

tblActual
Product, ActualSales, WeekNo, Customer

I am not quite sure what the Union query would look like.

Any help greatly appreciated.
Regards
Antony

 

[PHV]

The basic idea:
SELECT ...
FROM tblPlanned P INNER JOIN tblActual A ON P.??? = A.???
UNION
SELECT ...
FROM tblPlanned P LEFT JOIN tblActual A ON P.??? = A.???
WHERE A.Product Is Null
UNION
SELECT ...
FROM tblPlanned P RIGHT JOIN tblActual A ON P.??? = A.???
WHERE P.Product Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[antonycat]

Thanks for that.

Never written any code before but this is the code so far:

SELECT tblPlannedSales.Code, tblPlannedSales.Week, tblPlannedSales.BatchCode, tblPlannedSales.Amt, tblActualSales.Code, tblActualSales.Week, tblActualSales.BatchCode, tblActualSales.Amt
FROM tblPlannedSales INNER JOIN tblActualSales ON (tblPlannedSales.BatchCode = tblActualSales.BatchCode) AND (tblPlannedSales.Week = tblActualSales.Week) AND (tblPlannedSales.Code = tblActualSales.Code)
UNION
SELECT tblPlannedSales.Code, tblPlannedSales.Week, tblPlannedSales.BatchCode, tblPlannedSales.Amt, tblActualSales.Code, tblActualSales.Week, tblActualSales.BatchCode, tblActualSales.Amt
FROM tblPlannedSales LEFT JOIN tblActualSales ON (tblPlannedSales.BatchCode = tblActualSales.BatchCode) AND (tblPlannedSales.Week = tblActualSales.Week) AND (tblPlannedSales.Code = tblActualSales.Code)
WHERE tblActualSales.BatchCode is Null
UNION SELECT tblPlannedSales.Code, tblPlannedSales.Week, tblPlannedSales.BatchCode, tblPlannedSales.Amt, tblActualSales.Code, tblActualSales.Week, tblActualSales.BatchCode, tblActualSales.Amt
FROM tblPlannedSales RIGHT JOIN tblActualSales ON (tblPlannedSales.Code = tblActualSales.Code) AND (tblPlannedSales.Week = tblActualSales.Week) AND (tblPlannedSales.BatchCode = tblActualSales.BatchCode)
WHERE tblPlannedSales.BatchCode is Null

but I would like to see my results as Code, Week, BatchCode, PlannedSales.Amt and ActualSales.Amt not as 8 columns of data. How do I do this ?

Obviously this query will generate null values but I need to calculate a PlannedSales - ActualSales balance. I have discovered the Nz function which I suppose i could use on the report's calculated field or would it be best to append the results of the union query to a table, update all null values to 0 and run the report from the table ?

Thanks again
Regards
Antony

 

[PHV]

SELECT P.Code, P.Week, P.BatchCode, P.Amt, A.Amt
FROM tblPlannedSales P INNER JOIN tblActualSales A ON P.BatchCode = A.BatchCode AND P.Week = A.Week AND P.Code = A.Code
UNION SELECT P.Code, P.Week, P.BatchCode, P.Amt, A.Amt
FROM tblPlannedSales P LEFT JOIN tblActualSales A ON P.BatchCode = A.BatchCode AND P.Week = A.Week AND P.Code = A.Code
WHERE A.BatchCode is Null
UNION SELECT A.Code, A.Week, A.BatchCode, P.Amt, A.Amt
FROM tblPlannedSales P RIGHT JOIN tblActualSales A ON P.BatchCode = A.BatchCode AND P.Week = A.Week AND P.Code = A.Code
WHERE P.BatchCode is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[antonycat]

Great. Worked perfectly but now the null values.

Obviously this query will generate null values but I need to calculate a PlannedSales - ActualSales balance. I have discovered the Nz function which I suppose i could use on the report's calculated field or would it be best to append the results of the union query to a table, update all null values to 0 and run the report from the table ?

Thanks for the help
Regards
Antony