autsum several fields

[Anton Ayrapetov]

Hi I'm new to access , so I'm learning as it gettinig more involved. I
have a quiestion and appriciate any help.
The problem:
I have a form that has 10 line items, each line item includes:
Product Id, Description, Unit price, Qty, Total cost .
The total cost of each line item is calculated as =[Unit price]*
[Qty] .

I need to create a field at the end of the form TOTAL NOT TO EXCEED ,
that will automaticaly adds all total cost of each line item.

example:
| total cost 1
I total cost 2
I total cost 3
| ....
| total cost 10
------------------------------------------------------
Total not to exceed | ?

What expession should I build. Please help with syntax

Please advise.

Thank you,
A.A.

 

[Anton Ayrapetov]

Hi I'm new to access , so I'm learning as it gettinig more involved. I
have a quiestion and appriciate any help.
The problem:
I have a form that has 10 line items, each line item includes:
Product Id, Description, Unit price, Qty, Total cost .
The total cost  of each line item is calculated as =[Unit price]*
[Qty] .

I need to create a field at the end of the form TOTAL NOT TO EXCEED ,
that will automaticaly adds all total cost of each line item.

example:
                             | total cost 1
                             I total cost 2
                             I total cost 3
                             | ....
                             | total cost 10
------------------------------------------------------
Total not to exceed |     ?

What expession should  I  build. Please help with syntax

Please advise.

Thank you,
A.A.

In addition, the total has to show total only for current record. for
instans, if i complete fistrs form ,and move to 2nd , the total not
to exceed needs to show only currently opened record

 

[vanderghast]

Assuming that the ProductID describes the order to be use to perform the
running sum (generally, it would be a date_time, to the running sum will be
as the items have been entered, historically)

SELECT a.*, (SELECT SUM(b.qty*b.cost) FROM tableNameHere AS b WHERE
b.productID <= a.productID) As runningTotalCost
FROM tableNameHere AS a


in SQL view.

Alternatively, can be done graphically too, with a join (and can be a little
bit faster of execution)

Bring the table twice, one will get an _1 appended to its name.

Change the query to a TOTAL query.

Drag all the required fields from the table (not the one with the _1 ) to
the grid, keep the proposed GROUP BY.

Bring the productID field from the _1 table in the grid, change the GROUP BY
to WHERE and add the criteria:
<= [tableName].productID

In a new column, type:
RunningCost: SUM(tableName_1.qty * tableName_1.cost)
and change the GROUP BY to EXPRESSION. Use the real table name, with an _1
at the end


in SQL view, that should look like:

SELECT tableName.productID, ...list... ,
SUM(tableName_1.qty*tableName_1.cost) AS RunningCost
FROM tableName, tableName_1
WHERE tableName_1.productID <= tableName.productID
GROUP BY tableName.productID, ...list...



vanderghast, Access MVP