Wanted - Query with Union attributes but two different columns to be merged

[Looby]

Hi

I have two queries, one brings back sales data, the other brings back budget data. I am trying to combine the two, but there are a different number of records in both tables as products that were budgeted are no longer sold and products that are being sold, were not budgeted and to get a total budget vs sales figure I need all records to come back so the data is combined. A standard query will not give me the correct results because I am not looking for all the records to match up.

I have tried to build a union query
SELECT Budget.[Part Code], Budget.Description, Budget.[NSV Budget], Budget.[Calendar Wk/Yr], Budget.Name


FROM Budget;
UNION ALL SELECT Sales.[Part Code], Sales.Description, Sales.[SumofNSV], sales.[Calendar Wk/Yr], sales.Name
FROM Sales;

However, the Sumof NSV data is being reported in the same column as NSV budget, but on a different row, so I have 76 rows of data for one product but with some weeks having two rows - one is budget data, one is sales data, but it doesn't identify which is which as the column is headed up 'Budget' only.

Is it possible to do this? I am OK with access, but SQL and VBA are not something I'm familiar with, as the SQL above was heavily helped by various other web pages and stretches my skill to the limit!

many thanks for any help that is offered.

Louise

[jzwp11]

It would make more sense to have the sales & budget data for a part code in the same row. You cannot do that with a UNION query but you can join the budget & sales data via both the part code and the period [calendar wk/yr] but first you have to take care of the issue that not all part codes are in both the sales and budget tables.

Now to show data for all part codes, you need to do a left join between the table that holds all of your part codes and the budget table. The query would look something like this:

SELECT Parttable.[part code], budget.[NSV Budget],Budget.[Calendar Wk/Yr] ....
FROM parttable LEFT JOIN budget ON parttable.[part code]=budget.[part code]


Now do the same for the sales


SELECT Parttable.[part code], sales.[SumOfNSV] , sales.[Calendar Wk/Yr],....
FROM parttable LEFT JOIN sales ON parttable.[part code]=sales.[part code]

[RayMilhon]

Another option: Add the column Recordtype to each query.

SELECT Budget.[Part Code], Budget.Description, Budget.[NSV Budget], Budget.[Calendar Wk/Yr], Budget.Name,Recordtype:"budget"
FROM Budget;
UNION ALL SELECT Sales.[Part Code], Sales.Description, Sales.[SumofNSV], sales.[Calendar Wk/Yr], sales.Name,"Sales"
FROM Sales;

[RayMilhon]

actually the syntax in SQL is

SELECT Budget.[Part Code], Budget.Description, Budget.[NSV Budget], Budget.[Calendar Wk/Yr], Budget.Name, "Budget" as Recordtype
FROM Budget;
UNION ALL SELECT Sales.[Part Code], Sales.Description, Sales.[SumofNSV], sales.[Calendar Wk/Yr], sales.Name
FROM Sales;

"the recordtype:"budget" is the syntax within the Query designer"

[RayMilhon]

Damn keep hitting the post button to quick. forgot to include the "Sales" in the second part of the union on the second post.

[Looby]

Hello

Thank you for your replies, I have tried both approaches and am having more success with the query approach than SQL but still not achieving the results I hope are possible.

jzwp11 - I have created queries as you recommended above, I changed a couple of field names to improve similarity between the data sources.

Query1: Sales1 - this results in just over 4,000 rows of data and sales wks 1 to 26 as I would expect
SELECT Descriptors.[Part Code], sales.NSV, sales.[Week Number]
FROM Descriptors LEFT JOIN sales ON Descriptors.[Part Code]=sales.[Part Code];

Query2: Budget1 - this results in just over 10,000 rows of data with sales budgeted for all 52 weeks, again as I would expect
SELECT Descriptors.[Part Code], budget.[NSV Budget], budget.[Week Number]
FROM Descriptors LEFT JOIN budget ON Descriptors.[Part Code] = budget.[Part Code];

I have then created another query based on the 2 queries above and have joined them via the part code and week number fields as per your post, but the query is still only returning weeks 1 to 26 of data, not the full 52 weeks I am trying to see.

Query 3:
SELECT Budget1.[Part Code], Budget1.[NSV Budget], Budget1.[Week Number], Sales1.NSV
FROM Budget1 INNER JOIN Sales1 ON (Budget1.[Week Number] = Sales1.[Week Number]) AND (Budget1.[Part Code] = Sales1.[Part Code]);

I must be doing something wrong, but have tried various ways of trying to achieve all 52 wks of data with no success.

RayMilhon
If I take your SQL code and adapt to the new field names, it returns back a comment that the number of columns do not match so again I am unsuccessful with this approach:

SELECT Budget.[Part Code], Budget.[NSV Budget], Budget.[Week Number], "Budget" as Recordtype
FROM Budget;
UNION ALL SELECT Sales.[Part Code], Sales.[NSV], sales.[Week Number]
FROM Sales;

I apologise if I'm missing something really obvious, it's been a long week

many thanks
Louise

[recyan]

Till jzwp11's back, perhaps,

INNER JOIN Sales1 ON (Budget1.[Week Number] = Sales1.[Week Number])

is creating the problem.
If you are sure, your Budget1 has 52 weeks, then try

FROM Budget1 LEFT JOIN Sales1 ON (Budget1.[Week Number] = Sales1.[Week Number]) AND (Budget1.[Part Code] = Sales1.[Part Code]);

&
see what happens ?

Another option could perhaps be
a query which is a cartesian product of the Part Nos from Descriptors & Week Nos from 1 to 52,
which is then joined using Left Join on Budget & Sales.

Thanks

[jzwp11]

I assume that you may not have sales and or budget data for all parts for all week number combinations, so we would have to use a slightly different approach because an INNER JOIN will not work in that case. Do you have a table that holds all week numbers? If not, do either the budget or sales table have all week number periods? If so, you could create a query that just pulls that info and messes it with all part codes to get a Cartesian Product.

query name: qryPeriodPart

SELECT x.[Week number], Descriptors.[Part Code]
FROM x, Descriptors

Then create a new query with the above and your Sales1 and Budget1. Make a left join from qryPeriodPart to Sales1 via both part code and week number field. Do the same for Budget1.

recyan,

I just saw your post. We are thinking along the same lines on the Cartesian Product!

[Looby]

Hello

Many thanks for your replies.

I've run out of time for this week so will return to it when I get chance next week, however I have had some success with the Union query approach by having another go at RayMilhon's code and adding the record type for sales (which I think was what he meant with his last post). This returns data that I can export and pivot in excel for the moment.

I will have another go next week at achieving my ideal in access.

many thanks again.

Louise

[RayMilhon]

It was. Try using the union query as the source for a crosstab query see if that gets you what you need. However be advised that the weakness in Access with Crosstabs is you're only allowed 1 Value calculation.

SELECT Budget.[Part Code], Budget.Description, Budget.[NSV Budget], Budget.[Calendar Wk/Yr], Budget.Name, "Budget" as Recordtype
FROM Budget;
UNION ALL SELECT Sales.[Part Code], Sales.Description, Sales.[SumofNSV], sales.[Calendar Wk/Yr], sales.Name,"Sales"
FROM Sales;



Your crosstab rows would be
Name,Description,[part code],recordtype

Columns would be Calendar Wk/yr

Value would be SUM([NSV BUDGET])