Performing math operations on query results

[Ace71425]

Hello all....I need to know how to perform math on query results...Here's what im looking at in the query

Area1 3 8
Area2 4 8
Area3 5 8
Area4 2 8

Now here's what I need it to do, firstly add up the first column

Area1 3 8
Area2 4 8
Area3 5 8
Area4 2 8
14

Next multiply the second column value by 60 but only once because it's always going to be the same value...

Area1 3 8
Area2 4 8
Area3 5 8
Area4 2 8
14 480

Then divide the first column by its sum

Area1 3 8 .21
Area2 4 8 .28
Area3 5 8 .35
Area4 2 8 .14
14 480

Next multiply that now "3rd" column by the second column's sum

Area1 3 8 .21 100.8
Area2 4 8 .28 134.4
Area3 5 8 .35 168
Area4 2 8 .14 67.2
14 480

Finally divide that new column by 60...

Area1 3 8 .21 100.8 1.68
Area2 4 8 .28 134.4 2.23
Area3 5 8 .35 168 2.8
Area4 2 8 .14 67.2 1.12
14 480

Fun right? Now I don't need everything listed out like that in the query or a report but I do need that final column results displayed in the query or a report...I just cannot for the life of me figure out how to do manipulte query results (or table fields or anything for that matter) in access using math equations. Let me know if you would know how to do this or at least a starting point for me...

Oh and importantly I need to do the math on the results of the query...I cannot directly pull the information from the table to do the math on it if that makes sense because those numbers come from specific criteria ie user's login and date of work...Let me know if you need more info...Thanks all!

 

[shobolos]

Excel 2010
	A	B	C	D	E	F
1	Area1	3	8	0.21	102.86	1.71
2	Area2	4	8	0.29	137.14	2.29
3	Area3	5	8	0.36	171.43	2.86
4	Area4	2	8	0.14	68.57	1.14
5		14	480
Sheet1

Cell Formulas
Range	Formula
D1	=B1/$B$5
D2	=B2/$B$5
D3	=B3/$B$5
D4	=B4/$B$5
E1	=D1*$C$5
E2	=D2*$C$5
E3	=D3*$C$5
E4	=D4*$C$5
F1	=E1/60
F2	=E2/60
F3	=E3/60
F4	=E4/60
B5	=SUM(B1:B4)
C5	{=60*(C1:C4)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[james_lankford]

this is an access forum, not an excel forum

 

[Micron]

I may be off base here, but maybe you could create a report where you pull in the two fields and use calculated textbox controls to hold the equations. With proper grouping levels, you should be able to do it but the trick may be figuring out how many groups you need and where to put the controls. 14 is simply the SUM of the 1st field in a grouping footer. Rather than create another group for 480, I think it's SUM(fieldName/(Count(fieldName)*60) in the same footer. [Then divide the first column by its sum]. This gets trickier because I doubt that a calculated control on the row will reference a value in the footer. It would require code in the report's formattng event or judicious use of where to put another group footer. The other option I can think of is that you will have to create a table and use update queries with calculated fields to effect changes on that table. If you use a Totals query instead, note that you can append records with it, but you cannot update.