Multi-level totals in a single query? 1

[kagard]

Greetings:

I have written a specialized accounting application, but I'm having trouble with a report my clients want. Here's the setup:

- The company has multiple Divisions, stored in tblDivisions.
- Each Division has multiple Suppliers, stored in tblSuppliers.
- Each Supplier has a Budget for each calendar year, stored in tblCategoryBudgets.
- Each Budget has multiple Transactions posted against specific Categories, stored in tblTransactions.

I'm trying to create a query that will be the basis for an Expense Report by Division and Category. The Transactions total up fine, but I can't figure out how to make the Budget add up. Here is an example:

Division = "Beer" (seriously)
Suppliers = "Coors, Miller, Samuel Adams"
Categories = "Free goods, glassware, media"

The client wants to total the expenses for "free goods" for all three suppliers, AND, on the same line, to total up the "free goods" budget for all three suppliers. Coors may have 12 $50 expenses posted against "free goods" and the total shows up fine. But its "free goods" budget shows up on each Coors transaction record. If their free goods budget was $1000, it would show up as $12000. If I were working with one supplier, I could divide by 12 and display that has the total expense, but Miller might have a $1500 free goods budget and 10 transactions at $50. What I'm getting is:

Free Goods
Expenses: $1100 Budget: $27000

What I need to show is:

Free Goods
Expenses: $1100 Budget: $2500

Any idea how I can get this result? TIA

Keith

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kagard]

Hi PHV:

Sorry - I didn't include SQL because I'm looking for a general approach that I can apply to 3 different types of queries. IOW, I was looking for: "When I need totals for different groupings to appear in the same record, I use a DSum calculation in a totals query..." or something like that, rather than a specific SQL statement.

Here's the SQL I'm currently using for the the Expenses by Division and Category Report:

SELECT tblSupplier.strDivision, tblCategoryBudget.strCategoryName, Sum(IIf([strStatus]="Expense",[curCost],0)) AS xcurExpense, Sum(IIf([strStatus]="Committed",[curCost],0)) AS xcurCommitted, Sum(IIf([strStatus]="Credit",[curCost],0)) AS xcurCredit, Sum(tblTransactions.curCost) AS Net, Count(tblCategoryBudget.strCategoryName) AS CatCount, Sum(tblCategoryBudget.curCategoryBudget) AS BudSum, [BudSum]/[CatCount] AS BudReal
FROM (tblSupplier RIGHT JOIN tblCategoryBudget ON tblSupplier.strSupplierID = tblCategoryBudget.strSupplierID) RIGHT JOIN tblTransactions ON (tblCategoryBudget.strSupplierID = tblTransactions.strSupplierID) AND (tblCategoryBudget.dtmSuppBdgtStartDate = tblTransactions.dtmSuppBdgtStartDate) AND (tblCategoryBudget.strCategoryName = tblTransactions.strCategoryName)
WHERE (((tblTransactions.dtmSuppBdgtStartDate)=[Forms]![frmExpensesByCategoryReportOptions]![lstBudgetBegins]))
GROUP BY tblSupplier.strDivision, tblCategoryBudget.strCategoryName
HAVING (((tblSupplier.strDivision)=[Forms]![frmExpensesByCategoryReportOptions]![lstFor]));

I don't show detail records in the report, and I group by Category. I total the expenses in the group footer and the report footer, and both display properly. Trying to show the total budget in the category footer drastically overstates the budget amounts.

Keith

 

[PHV]

I'm looking for a general approach
Create a query for the expenses, a query for the budget and finally join them on the grouped columns.

If you're confident with SQL you may use inline views (subqueries) to get the expected result in a single query.

FYI, in your posted code, the HAVING clause should be merged with the WHERE clause as no criteria evolves an aggregated column.


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kagard]

Thanks PHV. I was so focused on solving it one way that I didn't even think to break the problem in two. I appreciate your reply. It's working well.

Keith