Actuals vs Budget Report

[okron]

I have two tables with the following fields (simplified version):

Type (Actual or Budget)
Year
Month
Account
Amount

One table with Actuals and one with Budgeted amounts, the budget table has no duplicate accounts while the Actual table does. I built a union query to combine the two but I can't seem to figure out how to build a report showing three columns (again simplified)...Account, Actuals and Budget. I want the Actuals to show all rows but since there is only one Budget amount for an account I would like to show that budget amount along side the total of the Actuals (for a particular account).


Appreciate any help you can give me.

 

[mpb.vu2]

As you designed... I am not getting the idea why you made two different tables...
anyway for your case... you have to make a query for actual amount group-by account...

make another query with the table budget and the query of actual amount that joined by Account...

However, It would be better to redesign your table...

 

[okron]

mpb.vu2...

If I follow what you're saying I have created two queries, one for budget and one for actuals. Each query with Account and Amount fields...I then created a third query which joined the two on account....when I did this my output on the 3rd query has three fields - Account, Amount1 (for Actuals) and Amount2 (for Budget). For a particular account I will then have the individual detail for all the actuals but under the Amount2 column the budget amount repeats. I could not figure out how to get a report to only show the budget amount once and still get the correct totals (grand total).

If it helps any....it's still not too late to merge my tables and use the Type field to distinguish Budget vs Actuals. I actually added the Type field thinking it might help to merge the two tables but couldn't see how it would help me with my current problem so I didn't merge the two tables....but I still can.

Thanks

 

[okron]

Still looking for some help....anyone done something along these lines?

 

[mpb.vu2]

if your accounts are same in both tables there's no way to repeat...
check your queries;

in 1st query put column as account number - total as groupby, and put another column for actual - total as sum.
in 2nd query put one column as account numbe- total as groupby, and put another column for budget - total as sum.
in 3rd Q.. join these two by the account number for both tables...
if the account numbers for the both table are same... there's no way to repeat any field...