Create Totals and Sub Totals in a Query

[plaguna]

I’m trying to get some calculations in a Query. Four fields are from a table x, and base on those fields I need to get some combination of Totals. This is the scenario.

Total A : [field 1]+[field 2] from table x
Total B : [field 3]+[field 4] from table x

So far so good. I don’t have any problem to have totals until this point. My question is: how can I create these next calculations.

Total C : (Total A) / (Total B)
Total D : (Total B) * (Total C)

If anyone can help me with any ideas for completing this task I would be really grateful.

Plaguna

[Stewart Ross]

Hi Plaguna. You should check by substitution that you are not causing yourself unnecessary work here. From your definitions Total D is just Total A:

Total D = Total B * Total C
Total C = Total A / Total B, therefore
Total D = Total B * (Total A / Total B) = Total A

In a previous post we looked at how to calculate percentages of question respondents, and the approach is similar here.

It is simplest to devise a query which calculates your totals A and B, then use this to feed another query:

SELECT[list of all fields you are grouping on], [field 1] + [field 2] 
AS [Total A], [field 3] + [field 4] as [Total B] 
FROM [your table]
GROUP BY[list of all fields you are grouping on], [field 1] + [field 2], [field 3] + [field 4]

After you save this query you can then use it in other queries to do the calculation of Total C.

Alternatively, if you want to do this all in one query you will need to substitute for Total A and Total B by performing the field additions repeatedly:

SELECT[list of all fields you are grouping on], [field 1] + [field 2] As [Total A], [field 3] + [field 4] as [Total B], (([field 1]+[field 2])/([field 3 + field 4])) as [Total C]
FROM [your table]
GROUP BY[list of all fields you are grouping on], [field 1] + [field 2], [field 3] + [field 4], (([field 1]+[field 2])/([field 3 + field 4]))

It is easiest to use the Access query editor to assist you with this, selecting View, Totals to set the grouping on, then adding calculated fields as necessary.

-Stewart

[plaguna]

Stewart,

Thank you again. I didn’t realize the redundancy of Total D. You saved me a lot a headaches. It’s so much flexible working with SQL statements. It works perfectly. I really appreciate it.

Plaguna