Running sums on a FORM?? 
Author Message
 Running sums on a FORM??

Got the running sum on the report..its great for doing a checkbook -
balances.

Now, i want to show the same data on a form.  How can this be done?

thanks
James



Mon, 26 Jan 2004 08:36:24 GMT  
 Running sums on a FORM??
On a report, the data is fixed, so RunningSum is easy.

Your form could be filtered or reordered at any time. Should Access
show only the running sum for the filtered records? Should the
balance be reworked if the records are reordered?

The checkbook balance is usually a matter of using a calculated
control that has a DSum() expression. Assuming:
  - primary key named "ID", and
  - Amount field that's positive for credits and negative for debits,
the ControlSource of your text box will be something like this:
  = DSum("Amount", "YourTable", "ID <= " & [ID])

Quote:

> Got the running sum on the report..its great for doing a checkbook -
> balances.

> Now, i want to show the same data on a form.  How can this be done?

--
Perth, Western Australia
Tips for MS Access users at:
        http://odyssey.apana.org.au/~abrowne


Mon, 26 Jan 2004 10:37:37 GMT  
 Running sums on a FORM??
Hi James,

You may refer to the following articles documented in Microsoft Knowledge
Base:

ACC2000: How to Use DSum to Create a Running Sum on a Form    [acc2000kb]
ID: Q210495
http://support.microsoft.com/support/kb/articles/q210/4/95.asp

ACC2000: Sample Function to Create a Running Sum on a Form    [accvba2000]
ID: Q210338
http://support.microsoft.com/support/kb/articles/q210/3/38.asp

Hope that helps!

Simon Liao



Mon, 26 Jan 2004 11:20:23 GMT  
 Running sums on a FORM??
thanks to you both.

Quote:
>>Should Access

show only the running sum for the filtered records? Should the
balance be reworked if the records are reordered?

yes to both of these.  if i use ID <= " & [ID]) and the order is by date,
that will throw off the running sum.  same if sorted by payee.  maybe i
should just not allow that?

James


Quote:
> Hi James,

> You may refer to the following articles documented in Microsoft Knowledge
> Base:

> ACC2000: How to Use DSum to Create a Running Sum on a Form    [acc2000kb]
> ID: Q210495
> http://support.microsoft.com/support/kb/articles/q210/4/95.asp

> ACC2000: Sample Function to Create a Running Sum on a Form    [accvba2000]
> ID: Q210338
> http://support.microsoft.com/support/kb/articles/q210/3/38.asp

> Hope that helps!

> Simon Liao



Mon, 26 Jan 2004 23:24:51 GMT  
 Running sums on a FORM??
All the posts make one undiscussed assumption, i.e. that you have an
ID that is sequential from the top of your list to the current record
when using the DSum function.

What about the situation where you have a set of transactions (each of
which DO have a unique key) but there is no guarantee that the ID's
are in date sequence?

I am doing a checkbook program, but cannot guarantee that the ID's
will necessarily sort lowest to largest by date. This could happen if
a person entered the transactions out of date sequence.

E.g.
AutoNo  Date      Amount
1       1/1/01     +46.45
2       1/4/01     -35.23
3       1/3/01     +245.33
etc.

Keith

Quote:

> On a report, the data is fixed, so RunningSum is easy.

> Your form could be filtered or reordered at any time. Should Access
> show only the running sum for the filtered records? Should the
> balance be reworked if the records are reordered?

> The checkbook balance is usually a matter of using a calculated
> control that has a DSum() expression. Assuming:
>   - primary key named "ID", and
>   - Amount field that's positive for credits and negative for debits,
> the ControlSource of your text box will be something like this:
>   = DSum("Amount", "YourTable", "ID <= " & [ID])


> > Got the running sum on the report..its great for doing a checkbook -
> > balances.

> > Now, i want to show the same data on a form.  How can this be done?



Thu, 12 Feb 2004 08:29:58 GMT  
 Running sums on a FORM??
You would need to sort by date in the DSum. Howver, if you're not storing
the time as well as the date (which seems likely) this method is more prone
to error since many records will have the same date entry.You could sort by
date AND ID, like this:

  = DSum("Amount", "YourTable", "Date* <= #" & txtDate** & "# AND ID <= " &
[ID])

but this may also give unexpected results if you care about the order within
a certain date. However, it's really the only option, so you should pobably
go for it.

*I hope this isn't really the name of your field. "Date" is a reserved word
and should be avoided when considering field (or other object) names. Look
up reserved wordsin online help.

**Your form control names should be different from the fields in the bound
recordset. I usually use a prefix designating the type of control I am
naming: "txt" for textbox, "cmb" for combo, etc.

HTH,
Bob Barrows

Quote:
> All the posts make one undiscussed assumption, i.e. that you have an
> ID that is sequential from the top of your list to the current record
> when using the DSum function.

> What about the situation where you have a set of transactions (each of
> which DO have a unique key) but there is no guarantee that the ID's
> are in date sequence?

> I am doing a checkbook program, but cannot guarantee that the ID's
> will necessarily sort lowest to largest by date. This could happen if
> a person entered the transactions out of date sequence.

> E.g.
> AutoNo  Date      Amount
> 1       1/1/01     +46.45
> 2       1/4/01     -35.23
> 3       1/3/01     +245.33
> etc.

> Keith




- Show quoted text -

Quote:
> > On a report, the data is fixed, so RunningSum is easy.

> > Your form could be filtered or reordered at any time. Should Access
> > show only the running sum for the filtered records? Should the
> > balance be reworked if the records are reordered?

> > The checkbook balance is usually a matter of using a calculated
> > control that has a DSum() expression. Assuming:
> >   - primary key named "ID", and
> >   - Amount field that's positive for credits and negative for debits,
> > the ControlSource of your text box will be something like this:
> >   = DSum("Amount", "YourTable", "ID <= " & [ID])


> > > Got the running sum on the report..its great for doing a checkbook -
> > > balances.

> > > Now, i want to show the same data on a form.  How can this be done?



Thu, 12 Feb 2004 21:45:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Article: How to create a running sum on a form

2. Running Sums in Forms

3. Help!, running sum on form!

4. How to do a Running Sum with Only Negatives

5. Running sum

6. Running sum question again

7. How to Count with Running Sum

8. Running Sums with queries

9. AM I missing something - Sum and Cumulative Sum

10. Running Sum

11. Creating a running sum query

12. running sum

 

 
Powered by phpBB® Forum Software