Running total

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

I am using Access 2002 on XP Pro

Is there a way to show running totals in a query run?

I have 40 accounts with about 60 transactions in each.
Can I show account 1 amount and have a RunTot that will
show the first, sorted on date, amount totaled in TotRun
then the second transaction amount added to the first in
TotRun and so on?

Using the Sum function I just get the accounts ultimate
total in each line.

Thanks

Ken
 
-----Original Message-----
Have you tried usling the Running Sum property set to OverGroup on you TotRun field?


.
No, I hadn't tried the runningsum. Thanks for turning me
Click to expand...
on to that it will be useful in the future.

But I am curious if this can be done in a query. My
reading suggest this only works in forms, reports, or data
access pages. Is it possible to do this in a query?

Thanks

Ken
 
Ken said:
on to that it will be useful in the future.

But I am curious if this can be done in a query. My
reading suggest this only works in forms, reports, or data
access pages. Is it possible to do this in a query?

Thanks

Ken
Click to expand...

Ken-

Yes, it can be done in a query. You'll want to probably sort by whatever you
want (name, ID, whatever you choose) and then write a custom expression
like:
RunningSum: (([UnitPrice]*[Quantity])-(1-[Discount]))
This expression goes in a new window on the query (not under another item).

You have to have your named group sorted however, otherwise all the records
will get the same treatment (I use the Group By sort on {whatever})

HTH-
Gary
 
Gary,

Thanks for your response but this still is not quite what
I'm looking for. The query results would look something
like this:

PayeeID AmtPaid RunningSum
1 10.00 0
1 4.00 14.00
1 124.92 138.92

Is this possible in a query?

Thanks so much

Ken
-----Original Message-----

me
on to that it will be useful in the future.

But I am curious if this can be done in a query. My
reading suggest this only works in forms, reports, or data
access pages. Is it possible to do this in a query?

Thanks

Ken
Click to expand...

Ken-

Yes, it can be done in a query. You'll want to probably sort by whatever you
want (name, ID, whatever you choose) and then write a custom expression
like:
RunningSum: (([UnitPrice]*[Quantity])-(1-[Discount]))
This expression goes in a new window on the query (not under another item).

You have to have your named group sorted however, otherwise all the records
will get the same treatment (I use the Group By sort on {whatever})

HTH-
Gary


.
Click to expand...
 
Hi,


You need a date_time_of_transaction field, or something that specify the
order (the records are NOT necessary in a fixed order in a table; from time
to time, they can move relatively among themselves, as pages are split,
etc.)


SELECT a.PayeeID, a.DateTime, LAST(a.AmtPaid), SUM(b.AmtPaid) As RunningSum
FROM myTable As a INNER JOIN myTable As b
ON a.payeeID=b.payeeID AND a.DateTime >= b.DateTime
GROUP BY a.PayeeID, a.DateTime
ORDER BY a.PayeeID, a.DateTime



not that the order by clause is not necessary for the logic, but enhances
the presentation.

Basically, the second reference to your table, b, keep an eye on all the
records which, for a given PayeeID, did occur before time a.DateTime. It is
then a matter to SUM the amount paid of those to get the running sum, ie,
the sum of "what occurred before".



Hoping it may help,
Vanderghast, Access MVP


Ken said:
Gary,

Thanks for your response but this still is not quite what
I'm looking for. The query results would look something
like this:

PayeeID AmtPaid RunningSum
1 10.00 0
1 4.00 14.00
1 124.92 138.92

Is this possible in a query?

Thanks so much

Ken
-----Original Message-----

-----Original Message-----
Have you tried usling the Running Sum property set to
OverGroup on you TotRun field?

:

I am using Access 2002 on XP Pro

Is there a way to show running totals in a query run?

I have 40 accounts with about 60 transactions in each.
Can I show account 1 amount and have a RunTot that will
show the first, sorted on date, amount totaled in
TotRun
then the second transaction amount added to the first
in
TotRun and so on?

Using the Sum function I just get the accounts ultimate
total in each line.

Thanks

Ken

.
No, I hadn't tried the runningsum. Thanks for turning me
on to that it will be useful in the future.

But I am curious if this can be done in a query. My
reading suggest this only works in forms, reports, or data
access pages. Is it possible to do this in a query?

Thanks

Ken
Click to expand...

Ken-

Yes, it can be done in a query. You'll want to probably sort by whatever you
want (name, ID, whatever you choose) and then write a custom expression
like:
RunningSum: (([UnitPrice]*[Quantity])-(1-[Discount]))
This expression goes in a new window on the query (not under another item).

You have to have your named group sorted however, otherwise all the records
will get the same treatment (I use the Group By sort on {whatever})

HTH-
Gary


.
Click to expand...
Click to expand...
 
Back
Top