autsum several fields

  • Thread starter Thread starter Anton Ayrapetov
  • Start date Start date
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.
 
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.
Click to expand...

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
 
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
 
Back
Top