Outer join or Union or

[GPnew]

This is a cut down version of my problem. I am sure the answer is so simple
I have a sales and budget table
Sales
ID Period Sales
1 1 200
2 3 300
3 4 500

ID Period Budget
1 1 100
2 2 400
3 4 50

I would like a query to produce

Peron Sales Budget
1 200 100
2 0 400
3 300 0
4 500 50

Please help
Thanks

 

[combo]

You could first create helper query (union, distinct) to get all existing periods. If it is a fixed set, you can create table with periods instead. Next outer join this query (or table) with sales and budget tables.
Alternatively union sales and budget tables, with adding description column (Sales or Budget). Next create crosstab query. Drawback: nulls instead zeros, poor input for reports and other queries.

combo

 

[SkipVought]

I did my phd (piled higher & deeper) on oyher queries.

BTW, GPnew, welcome to Tek-Yips.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dhookom]

I love crosstabs but I think you can accomplish this with a UNION and a TOTALS query. Your union query would look like:

SELECT Period, SALES, 0 as Budget
FROM Sales
UNION SELECT Period, 0, Budget
FROM Budget

Then create a totals query:

SELECT Period, Sum(Sales) as TotSales, Sum(Budget) as TotBudget
FROM quniSalesBudget
GROUP BY Period

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016