How to join a budget and an actual table using a query (1 Viewer)

[andy_dyer]

Hi,

I think this may be a union query solution but I cannot work out how to do a union query...

I have two queries exactly the same except one has Budget Amount the other has Actual Amount

I want to join the two together in a new query that displays everything...

Currently using a select query I can play around with the joins and either get all cost centres with budget or all cost centres with actuals

But if i want a list that shows all cost centres regardless if they have an actual without a budget or a budget without an actual or indeed they have both...

Help???

 

[Trevor G]

Andy do you have access to watch YouTube if so you can find examples and demos on YouTube here is an example

http://www.youtube.com/watch?v=-tUMfbsJVTA

 

[andy_dyer]

Thanks Trevor just watched that and that makes sense when you do have two identical tables or queries.

But mine is identical apart from the budget and actual columns...

Say for example both queries simply have two columns Cost Centre and Amount - so I want to join these two queries together but where the cost centres match have two amount columns

So from

Cost Centre
Budget Amount

and

Cost Centre
Actual Amount

to

Cost Centre
Budget Amount
Actual Amount

but the trick is where I have some cost centres with actuals and no budget and some with budget with no actual... and i can't get a join combo to work and give me both...

 

[Trevor G]

Andy can you upload some sample data in the 2 tables and I will take a look for you. You can set Fields to show 0 if no information is found in a record so that should account for ensuring all records from both tables have data in them.

 

[vbaInet]

By the way, if they are both the same you should be able to have them both in one query from the start.