Query Joint Property 1

[UongSaki]

I have two tables, Actual & Budget. I would like to create a query that would give me the result like this (I cut & Pasted on Wordpad)

Fiscal year Posting period Costcenter Cost element Actual Budget
V6/2004 1 11000 501001 $40,077.90
V6/2004 1 11000 501998 $7,525.97
V6/2004 1 11000 502000 $3,760.17
V6/2004 1 11000 502010 $1,512.93
V6/2004 1 11000 502014 $2,972.58
V6/2004 1 11000 502015 $2,890.90
V6/2004 1 11000 502030 $609.73
V6/2004 1 11000 502033 $3,444.66
V6/2004 1 11000 502034 $5,572.82
V6/2004 1 11000 502050 $117.84
V6/2004 1 11000 502070 $281.73
V6/2004 1 11000 502080 $220.66
V6/2004 1 11000 502110 $392.00
V6/2004 1 11000 502120 $1,953.34
V6/2004 1 11000 502140 $88.40
V6/2004 1 11000 509015 $49.26
V6/2004 1 11000 509432 $30.71
V6/2004 1 11000 509505 $545.00 $90.00
V6/2004 1 11000 509510 $2,180.00 $631.00
V6/2004 1 11000 509516 $29,708.00 $9,821.00
V6/2004 1 11000 509530 $855.44 $1,000.00
V6/2004 1 11000 513980 $38,368.52
V6/2004 1 11000 502502 $24,917.00
V6/2004 1 11000 503248 $90.00
V6/2004 1 11000 504350 $180.00
V6/2004 1 11000 509401 $83.00
V6/2004 1 11000 512470 $180.00
V6/2004 1 11000 563800 $166,600.00
V6/2004 1 11000 599998 $1,057.00
V6/2004 1 11000 599999 $1,250.00
V6/2004 1 11000 600052 ($11,808.28)

Table One - Actual

Fiscal year Posting period Costcenter Cost element Amount
V6/2004 1 11000 501001 $40,077.90
V6/2004 1 11000 501998 $7,525.97
V6/2004 1 11000 502000 $3,760.17
V6/2004 1 11000 502010 $1,512.93
V6/2004 1 11000 502014 $2,972.58
V6/2004 1 11000 502015 $2,890.90
V6/2004 1 11000 502030 $609.73
V6/2004 1 11000 502033 $3,444.66
V6/2004 1 11000 502034 $5,572.82
V6/2004 1 11000 502050 $117.84
V6/2004 1 11000 502070 $281.73
V6/2004 1 11000 502080 $220.66
V6/2004 1 11000 502110 $392.00
V6/2004 1 11000 502120 $1,953.34
V6/2004 1 11000 502140 $88.40
V6/2004 1 11000 509015 $49.26
V6/2004 1 11000 509432 $30.71
V6/2004 1 11000 509505 $545.00
V6/2004 1 11000 509510 $2,180.00
V6/2004 1 11000 509516 $29,708.00
V6/2004 1 11000 509530 $855.44
V6/2004 1 11000 513980 $38,368.52

Table Two - Budget

Fiscal year Posting period Costcenter Cost element Amount
V6/2004 1 11000 502502 $24,917.00
V6/2004 1 11000 503248 $90.00
V6/2004 1 11000 504350 $180.00
V6/2004 1 11000 509401 $83.00
V6/2004 1 11000 509505 $90.00
V6/2004 1 11000 509510 $631.00
V6/2004 1 11000 509516 $9,821.00
V6/2004 1 11000 509530 $1,000.00
V6/2004 1 11000 512470 $180.00
V6/2004 1 11000 563800 $166,600.00
V6/2004 1 11000 599998 $1,057.00
V6/2004 1 11000 599999 $1,250.00
V6/2004 1 11000 600052 ($11,808.28)

The problem is it does not matter what Joint Property I used, I just could not get the result I'm loonkig for.

Thank you

Uong Saki

 

[dhookom]

I would first create a UNION query of both tables
Actual

SELECT Fiscalyear, PostingPeriod, CostCenter, CostElement, Amount As Actual, 0 As Budget
FROM Actual
UNION ALL
SELECT Fiscalyear, PostingPeriod, CostCenter, CostElement, 0, Amount
FROM Budget

You can then create a totals query from this union query that groups by Fiscalyear, PostingPeriod, CostCenter, CostElement and totals Actual and Budget.



Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[UongSaki]

Thank you very much Duane for your help. That's just what I'm looking for. Again, thank you.

Saki