Summing a column

[Guest]

My table and form have fields for CommissionAmount and BonusAmount. The form
has a calculated field where the two are added together to form the
TotalPaidPerSale field. The TotalPaidPerSale column is also on a query where
the result set shows the total to be paid for each sale. What I am trying to
do now is create a TotalCheck column in the query and form that shows a
running total for each sale.

I have tried TotalCheck=Sum*TotalPaidPerSale and have found it does not
work. What do I need to do to sum the TotalPaidPerSale column? The
TotalCheck column would give the total check amount that I am supposed to be
paid.

Thank you.

 

[Michel Walsh]

Hi,


SELECT a.*, (SELECT SUM( b.qty * b.unitprice FROM myTable As b WHERE
b.dateTimeField <= a.dateTimeField) As runningSum
FROM myTable As a
ORDER BY a.dateTimeField


is a possible solution.

 

[Jeff Boyce]

Check Access HELP on Totals queries, which includes a "Sum".

By the way, if your table already has the two fields needed to calculate
TotalPaidPerSale, you rarely need to save the value of the calculation in
the table. This can be re-calculated, on the fly, in a query (and that way,
you don't run up against data synchronization issues).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Michel Walsh]

Missing a ")"



SELECT a.*, (SELECT SUM( b.qty * b.unitprice)
FROM myTable As b
WHERE b.dateTimeField <= a.dateTimeField) As runningSum
FROM myTable As a
ORDER BY a.dateTimeField



Hoping it may help,
Vanderghast, Access MVP

 

[MGFoster]

My table and form have fields for CommissionAmount and BonusAmount. The form
has a calculated field where the two are added together to form the
TotalPaidPerSale field. The TotalPaidPerSale column is also on a query where
the result set shows the total to be paid for each sale. What I am trying to
do now is create a TotalCheck column in the query and form that shows a
running total for each sale.

I have tried TotalCheck=Sum*TotalPaidPerSale and have found it does not
work. What do I need to do to sum the TotalPaidPerSale column? The
TotalCheck column would give the total check amount that I am supposed to be
paid.

See this site:

http://support.microsoft.com/kb/q138911/

for an explanation of how to get running sums.

 

[Guest]

I do not understand SELECT a.* What is "a"? Then SELECT SUM (
b.qty*b.unitprice--these do not apply in my table. I have a commission
amount and a bonus amount that are added together for each sale. Then I want
to calculate the sum of all of these for the total check amount. Let's try
again. Thank you for your efforts though.

 

[Douglas J. Steele]

In the context of Michel's response, a and b are usually referred to as
"correlation variables"

Since the query refers to myTable twice, it's necessary to be able to keep
track of which reference to myTable you're talking about. Consequently,
Michel named the reference inside the subquery b, and the outer reference a.
That's what the "As b" and "As a" statements do.

 

[Michel Walsh]

Hi,



It is better explained than what I could have come with, and with all the
concision I like too.

Faxylady, my example was about the fact you can use an expression inside the
SUM( )... with the right closing parenthesis. You can use just a field too,
that is just simpler. If you need an addition, that is also allowed:

SUM( b.amount + b.bonus )



Hoping it may help,
Vanderghast, Access MVP