Compute Totals

  • Thread starter Thread starter Mick Horan
  • Start date Start date
M

Mick Horan

I have a table that has the following format. The first column is the field
name the second column is the sample data.



Date = 4/9/04

Unit = 1SW

Shift = NIGHT

L1 = 1

L2 = 1

L3 = 1

L4 = 1

L5 = 1

L6 = 1

L7 = 1



I need to create a query that computes some totals based on the data in each
record.



First I need to SUM the L1 to L7 fields. Using my sample data the total
would be 7. (Call it SumLtotal = 7)



Then I need to multiply each of these same L1-L7 fields times the value of
the sample data in that field against the associated number of that "L"
field. In other words L1=1, L2=2, L3=3, L4=4, L5=5, L6=6 and L7=7 . For
example L1 is 1x1=1 while L7 is 7x1=7. In this case the total would be 28.
(Call it TimesLtotal = 28)



Finally I need to divide TimesLtotal (28) by SumLtotal (7). The result
would be 4.



Can all of this be done in one query so that I can use it in a report.



Thanks, Mick
 
Mick,

No, you can't do it in one query. You can do the sum & the times in one
query:
First I need to SUM the L1 to L7 fields. Using my sample data the total
would be 7. (Call it SumLtotal = 7)
Click to expand...
SumLtotal:[L1]+[L2]+...........+[L7] 'note the colon after the SumLtotal
Then I need to multiply each of these same L1-L7 fields times the value of
the sample data in that field against the associated number of that "L"
field. In other words L1=1, L2=2, L3=3, L4=4, L5=5, L6=6 and L7=7 . For
example L1 is 1x1=1 while L7 is 7x1=7. In this case the total would be 28.
(Call it TimesLtotal = 28)
Click to expand...
TimesLtotal:[L1]*[L2]*..........*[L7]

However the:
TimesDivBySum:[TimesLtotal]/[SumLtotal]
would need to be in a second query referencing the first query or it can be
done in the report.

You could do it all in one query if you used this instead
TimesDivBySum::[L1]*[L2]*..........*[L7]/:[L1]+[L2]+...........+[L7]

Hope that helps

Nelson
 
Back
Top