Totalling group footer fields

[Carbontrader]

I have a report in which I total the hours and costs spent to date from a large number of timekeeping records. I don't print the records (there are several thousand), just the total of the records for each subproject. I do this by turning off displaying of the detail fields, displaying just the sum([Quantity]) and sum([Amount]) for the total hours and $ respectively for each subproject in the subproject group footer. Works just fine.

I want to compare these sums with the budgets for each subproject. Budgets are contained in a separate table. The budget table and the timekeeping table are linked by the subproject code in a query. I introduce the budget amounts into the report by placing the relevant budget fields from the query into the subproject group footer. Again, works just fine. I can calculate stats on each subproject: % hours used, % $ used, amounts remaining etc.

Problem I'm having is that I can't total the subproject budgets. The query lists the subproject budget at every timekeeping record, so the totals are humungous!

Is this an easy fix, or is my whole approach flawed?

[June7]

If you don't care about detail records, maybe better to do a saved aggregate (Totals) query grouped on subproject then join the budget table to that query and use as report Recordsource. The alternative might be a DLookup to the budget table.

[Carbontrader]

The saved aggregate query you describe sounds like the right approach. Can I build one within Access or is it a SQL construct? I'm not a particularly sophisticated user...

[June7]

Use the query builder. Create a SELECT query then click the Totals button from ribbon. Access Help has more info on using the query builder.

[Carbontrader]

Your idea of using Dlookup made me look into using aggregate functions. I ended up changing the RecordSource for the report to the Budget table, and using the Dsum function to total the cost from the detail timekeeping table.I don't need to use a query at all! Thanks for the tip.