Grand Total In Query 1

[labprof]

I could really use some help from some of my favorite tipsters here...

I have a table that has 3 fields: A1ILC, A2ILC, & A3ILC
I have totaled these in my query with:
=([A1ILC]+[A2ILC]+[A3ILC]) - this would be Expr1
I have another 3 fields called: A1TS, A2TS, & A3TS
I have totaled these in my query with:
= ([A1TS]+[A2TS]+[A3TS]) - this would be Expr2
I have then divided Expr2 by Expr1 to get a percentage, or what we call in our lab an index.

Each record in this part of the data base will have an index calculation.

There will be many records in this table.

My challenge is I have to come up with a way to calculate the GRAND Total of the indexes. Or another words take Expr 1 from all records and divide it by Expr 2 of all records in the table to display on a form the grand total index number.

I've written two queries, 1 to calculate the index for the indiviual record and then the second to calculate the summary of the record indexes but how do I write a GRAND Total Querie?

I hope I've been clear enough with this to give you a good idea of my challenge.

Any help would be sincerely appreciated.

Labprof

 

[Golom]

Probably the most correct way is

Select (Sum([A1ILC]) + Sum([A2ILC]) + Sum([A3ILC])) As E1,

       (Sum([A1TS]) + Sum([A2TS]) + Sum([A3TS])   ) As E2,

       ((Sum([A1ILC]) + Sum([A2ILC]) + Sum([A3ILC]) ) /
       ( Sum([A1TS]) + Sum([A2TS]) + Sum([A3TS])    )) As [Index]

From myTable

 

[labprof]

Golom,
Thank you ever so much for your reply and solution.

Do I do this in a query or somewhere on my form?

Bear with me.. I'm a little new to this coding. Sometimes
I'm not sure where the code is to go.

Labprof

 

[Golom]

You can paste it into SQL view in a query (make sure that you change the table name to the correct one.) Save it with some descriptive query name and then run the query.

Where and how you use it on a form depends on your specific application.