Subtotal in a column

[Leonel]

Hi, thx for reading

I looked for a similar question, but I didn't find any, so here's the case.

I have a table with several records with budget, all grouped by department, and budget type:

Dep	Type	USD
A	Operation	500
A	Administration	150
B	Operation	300
B	Administration	200

How can I get a fourth column (calculation) that shows the subtotal of the budget for the given department? Here the illustration:

Dep	Type	USD	Subtotal
A	Operation	500	650
A	Administration	150	650
B	Operation	300	500
B	Administration	200	500

What I want is to then calculate the individual percentage per line for the budget type; for example: 500/650 = 77%, i.e. Operation is 77% of the total budget for Department A.

Thank you very much


Leonel

[ranman256]

1 make a query , qsBudgetTotalsByDept
do just that, sum the totals per dept.

in a new query, bring in qsBudgetTotalsByDept,
and the budget table (to see record by record)

Join the 2 on Dept,
bring down the table fields and qsBudgetTotalsByDept.Total

[June7]

Alternatively, build a report and use its Sorting & Grouping features with aggregate calcs.

[Leonel]

Thank you,

I was thinking more of having a query and a sub-query (similar to ranman's suggestion), but I'll go for separate queries.

Thank you all
lg

[Gicu]

you can also use dsum in the fourth column, if not a lot of data it should be quite fast: dsum ("[USD]","[YourTable]","[Dep]=""" & [Dep] & """")

Cheers,
Vlad