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
58
