Budget and Cost Report

I am currently working on the above report.  The monthly budgets are contained in one table, the costs in another.  If work from the budgets table to costs I can see all costs against a cost code that has a budget, but if costs are incurred against a cost code that doesn't have a budget this will be missed.  I could work the other way from costs to budgets but the report wouldn't show costs codes that had a budget but where no costs had been incurred.

Please can someone advise on the best approach?
DamozzAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
dqmqConnect With a Mentor Commented:
Use an outer join to get all costs with their budgets (or lack thereof)

Select...
from Costs c left join budgets b on c.costcode = b.costcode

go the other way to get all budgets with their costs (or lack thereof)
Select...
from Costs c right join budgets b on c.costcode = b.costcode

Most reports are best developed with one of the above forms.  To see it both ways, probably best to use two reports or two sub-reports.


You can also use full outer join  to get all budgets and all costs at one time, but it gets a little tricky developing a meaningful report when sometimes the budgets are missing and sometimes the costs are missing.

Select...
from Costs c full outer join budgets b on c.costcode = b.costcode
0
All Courses

From novice to tech pro — start learning today.