Access DB do line item calculation on total of a column before knowing the total

Question

[ edit ]

For the record, here's the problem portion of the query, which is now working:

SELECT 
 m.groupNum, 
 t.ea,
 ( t.ea - ( t.ea * m.margin )) AS estCost,
 (( t.ea - ( t.ea * m.margin )) * t.quantity ) AS salesSub,
 ((( t.ea - ( t.ea * m.margin )) * t.quantity ) / 
   (
    SELECT SUM(( t2.ea - ( t2.ea * m.margin )) * t2.quantity )
    FROM temp as t2
    INNER JOIN masters as m2 
    ON t2.mod = m2.mod
    WHERE m2.groupNum = m.groupNum
    )
  ) AS salesPercent
etc...

[ end edit ]

I think I need a query that can recursively update itself based on the total of a column's values after inserting values on all the rest of the records for a given (groupNum) range.

I already have the estCost and salesSub fields. Now I need to do calculations on the salesPercent field, which involves knowing the total amount of all salesSub records in a given set (groupNum).

salesPercent = 
  ( salesSub / [the sum total of all salesSub amounts for each groupNum] )

(snip)

SELECT 
 m.id, 
 t.priceEach,
 ( t.priceEach - ( t.priceEach * m.margin )) AS estCost,
 (( t.priceEach - ( t.priceEach * m.margin )) * t.quantity ) AS salesSub
 -- is it possible to perform calculation on salesPercent here?
INTO output

FROM financeMasters AS m
INNER JOIN temp AS t .......

(end snip)

I have this...

------
output
---------------------------------------------------------------
id | groupNum | priceEach | estCost | salesSub | salesPercent |
---------------------------------------------------------------
1  | apple    | 150.00    |  90.00  | 90.00    |   
2  | apple    | 100.00    |  60.00  | 60.00    |   
3  | apple    |  50.00    |  30.00  | 30.00    |

but how can I calculate salesPercent on the salesSub total (in this case 180.00) before knowing the total?

Answer 1

You'll probably have to use a subselect to do the math on each row, there's no way to "go back" and change previous rows.

Something like this:

SELECT 
 m.id, 
 t.priceEach,
 ( t.priceEach - ( t.priceEach * m.margin )) AS estCost,
 (( t.priceEach - ( t.priceEach * m.margin )) * t.quantity ) AS salesSub,
 ((( t.priceEach - ( t.priceEach * m.margin )) * t.quantity )
     / (SELECT SUM(( t2.priceEach - ( t2.priceEach * m.margin )) * t2.quantity )
        FROM financeMasters AS m2
            INNER JOIN temp AS t2 .....
        WHERE m2.groupNum = m.groupNum))
     AS salesPercent
INTO output

FROM financeMasters AS m
INNER JOIN temp AS t .......

Yes, it's pretty ugly. I had to leave out some details due to not knowing how you were doing the join, and also I'm not sure which table that groupNum is coming from, you didn't show that anywhere.

Answer 2

Like Chad Birch said, a subselect is probably your best bet. You could make it less ugly by storing the subselect in a separate query.

Answer 3

What you are going to do is call that query from another query (and join that with the original). Assuming your query is called salesCalc. It looks like this (I've left out the join):

SELECT m.id, 
    t.priceEach,
    ( t.priceEach - ( t.priceEach * m.margin )) AS estCost,
    (( t.priceEach - ( t.priceEach * m.margin )) * t.quantity ) AS salesSub
INTO output

FROM financeMasters AS m
INNER JOIN temp AS t .......

You will create another query called salesSum. It uses the first query. It will probably look exactly like this:

select sum(salesSub) as salesGroupTotal, groupNum
from salesCalc
group by groupNum;

You will create a third query called salesPercent. It uses both the first and second. It will look like this (I've left out the join here as well):

select salesCalc.*,  salesCalc.SalesSub/salesSum.salesGroupTotal
from salesCalc, salesSum
inner join ...

(YMMV)