CheckBook Application: Skip Fields and Running Balance

#10 (**permalink**) 28-Jul-2006, 08:13 AM

Hello Douglas:

OK, you've convinced me, I won't store the running balance in the table.
That will definitely save me a lot of time in terms of writing a complex
script for a trivial benefit.

What did you mean when you said,
[just because Money and Quicken display a running balance does not
necessarily mean that it stores that running balance.]

If they aren't storing the running balance, what are they doing?

I will definitely look RunningSum property of a report. I haven't tried it
yet but it does indeed look as if it's pretty easy to implement.

Thanks so much for taking the time to answer my questions. Now if I can only
figure out why I can't use IE to pose questions and/or reply, I'll feel a
little better.

Robert

"Douglas J Steele" wrote:

> To be honest, I haven't looked at the sample database myself (and I can't,
> because I'm limited to Access 97 at the moment), but I did notice that it
> does what you're trying to do.
>
> Just because Money and Quicken display a running balance does not
> necessarily mean that it stores that running balance.
>
> While you certainly could replicate what you did in Alpha Five using VBA in
> Access, I'd recommend very strongly not to. It's fine and dandy to say that
> you're going to run the code every time the user changes something, but what
> happens if for some reason you use a query to update the data rather than
> using the form? Perhaps Alpha Five has triggers, but Access doesn't.
>
> If you want a running balance, it's trivial to do in a report (look at the
> RunningSum property that appears in the text box control on reports). For a
> form, I feel it's much safer to create a running sum in a query, and use
> that query as the basis for the report (rather than a table).
>
> If your table includes a field that represents the Date/Time when the entry
> was posted to table, you can calculate the running sum in the query as
> DSum("MyField", "MyTable", "MyDateTimeField <=" & CDbl([MyDateTimeField]))
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Robert T" wrote in message
> news:4F3058F8-AA01-4BBF-82B7-FA196AFA9517@microsoft.com...
> > Hello Doug:
> >
> > Yes I did download the application you mentioned earlier and it seems like
> > pretty basic stuff, I didn't find anything in there that I thought was
> > enlightening. Maybe I missed something in there so I'll definitely take
> > another look at the application later. Was there anything specific you
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/10531-checkbook-application-skip-fields-running-balance.html
> wanted
> > me to check out?
> >
> > In general I agree with both you and John Vinson about storing calculated
> > values in the table, however, let me add something different to the
> equation.
> > First, I like the idea of a running balance, that's obviously what Money
> and
> > Quicken do and I find that helps. So I worked hard and found a way to
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10531
> avoid
> > the problem you described.
> >
> > I used Xbasic [Alpha Five] to update all of the relevant records in the
> > table when a value is changed. So if the user goes back to an old record
> and
> > changes the amount of a check or deposit, that will change the running
> > balance for that record and every record that follows. I therefore
> utilized
> > a script that goes back to the previous record of the one that was
> changed,
> > captures the previous balance, and then the script recalculates the
> running
> > balance starting with the changed record, updating all subsequent records
> in
> > the table. It took a long time to figure out how to do such, but I'm sure
> > that with a little experience I could do such with Visual Basic.
> >
> > Robert
> >
> >
> > "Douglas J Steele" wrote:
> >
> > > Personally, since I can't think of different attributes between Debits
> and
> > > Credits, I'd be loathe to treat them as separate entities. That implies
> only
> > > one table. (I hinted at this before when I said I couldn't see why there
> > > would be null fields)
> > >
> > > I also don't think you should be storing a calculated value (the running
> > > balance) in a table. As fellow Access MVP John Vinson likes to say
> "Storing
> > > calculated data generally accomplishes only three things: it wastes disk
> > > space, it wastes time (a disk fetch is much slower than almost any
> > > reasonable calculation), and it risks data validity, since once it's
> stored
> > > in a table either the Total or one of the fields that goes into the
> total
> > > may be changed, making the value WRONG."
> > >
> > > It is possible (although usually inefficient) to calculate a running
> balance
> > > in a query. For example, you can use the DSum function to calculate the
> sum
> > > of all records prior to the current record.
> > >
> > > --
> > > Doug Steele, Microsoft Access MVP
> > > http://I.Am/DougSteele
> > > (no e-mails, please!)
> > >
> > >
> > > "Robert T" wrote in message
> > > news:12842D38-C129-498B-A9C4-BF6D7C04494E@microsoft.com...
> > > > Hi Doug:
> > > >
> > > > Once again, thanks for the prompt response showing me how to enable or
> > > > disable a field on a form. I just knew that type of option had to be
> > > > available.
> > > >
> > > > As for your question about why I'm doing this, I already have a
> checking
> > > > application in Alpha Five that works perfectly, plus I regularly use
> > > Quicken.
> > > > I'm not going through this process because I want a checking
> application
> > > in
> > > > Access. Since I need Access skills for my new job, I'm doing this
> strictly
> > > as
> > > > a learning experience. The skills I pick up by designing this
> application
> > > can
> > > > be applied to many other types of applications. Doug, I just happened
> to
> > > pick
> > > > the checking application randomly.
> > > >
> > > > I realize you can make a currency transaction positive or negative,
> but
> > > > most financial applications usually have separate fields for Debits
> and
> > > > Credits. Do you think using one field is a better idea?
> > > >
> > > > By the way, that brings me back to my previous question. What do you
> think
> > > > of the idea of creating two separate tables for transactions, one to
> > > record
> > > > checks and a 2nd one to record only deposits/credits? As I stated
> before,
> > > > that would eliminate null fields. Or do you prefer placing all
> > > transactions
> > > > in one table?
> > > >
> > > > By the way, any thoughts on my other question reference a running
> balance
> > > > field for each transaction?
> > > >
> > > > Thanks,
> > > > RT
> > > >
> > > > "Douglas J Steele" wrote:
> > > >
> > > > > "Robert T" wrote in message
> > > > > news:59F49FC9-0A5E-40AD-B6A5-CB8433EF578A@microsoft.com...
> > > > > > Hi Doug:
> > > > > >
> > > > > > Thanks for the prompt response.
> > > > > >
> > > > > > Since I'm relatively new to Access, would you be kind enough to
> give
> > > me an
> > > > > > example of how I can do such in Form Design? For example, if the
> > > > > > Type_Transaction is a "Debit", I want to ensure the Credits
> Currency
> > > field
> > > > > > will remain blank?
> > > > >
> > > > > It will depend on how your form is structured, but try something
> like
> > > the
> > > > > following:
> > > > >
> > > > > Private Sub Form_Current()
> > > > >
> > > > > If Me!txtType_Transaction = "Debit" Then
> > > > > Me![Credits Currency].Enabled = False
> > > > > Me![Debits Currenty].Enabled = True
> > > > > Else
> > > > > Me![Credits Currency].Enabled = True
> > > > > Me![Debits Currenty].Enabled = False
> > > > > End If
> > > > >
> > > > > End Sub
> > > > >
> > > > > Actually, that can be simplied to:
> > > > >
> > > > > Private Sub Form_Current()
> > > > >
> > > > > Me![Credits Currency].Enabled = (Me!txtType_Transaction <>
> "Debit")
> > > > > Me![Debits Currenty].Enabled = (Me!txtType_Transaction = "Debit")
> > > > >
> > > > > End Sub
> > > > >
> > > > >
> > > > > > Second, do you think using one Transaction Table is a good option
> or
> > > > > should
> > > > > > I divide it into two tables, Checks and Deposits/Credits, that
> would
> > > > > ensure
> > > > > > there are no NULL fields? That would obviously remove the need to
> skip
> > > > > fields
> > > > > > in this particular application.
> > > > >
> > > > > I don't see why you'd need Null fields. I don't believe you need to
> > > > > distinguish between Credits and Debits in your table: the sign of
> the
> > > entry
> > > > > should indicate for you.
> > > > >
> > > > > Just because you've got two fields on your form doesn't mean you
> must
> > > have
> > > > > two fields in your table.
> > > > >
> > > > > > Third, it's really nice setting the Skip Field Rule in the table
> > > because
> > > > > it
> > > > > > will be enforced no matter what data entry form is used. Just a
> > > thought.
> > > > >
> > > > > Perhaps, but there isn't, so there's nothing to be gained by going
> down
> > > that
> > > > > path...
> > > > >
> > > > > > Fourth, I have to use Firefox to post my questions/replies. When I
> > > click
> > > > > on
> > > > > > Post a Question or Reply in Internet Explorer, I get something
> similar
> > > to
> > > > > the
> > > > > > Windows hourglass and I never see the Post Question or Reply form.
> I
> > > know
> > > > > > this isn't an Access question but do you or anyone else have any
> idea
> > > why
> > > > > > that is happening in IE?
> > > > >
> > > > > Sorry, I never use the web interface.
> > > > >
> > > > > > Thanks
> > > > >
> > > > > Is this just something you're building for the heck of it? Strikes
> me
> > > that
> > > > > buying Quicken or Money would be a lot faster.
> > > > >
> > > > > You might also try downloading the Accounts ledger database from
> > > > > http://office.microsoft.com/en-au/te...175341033.aspx
> > > > >
> > > > >
> > > > > --
> > > > > Doug Steele, Microsoft Access MVP
> > > > > http://I.Am/DougSteele
> > > > > (no e-mails, please!)
> > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

#11 (**permalink**) 28-Jul-2006, 08:13 AM

While I don't have access to their source code, I would assume that they're
calculating the running balance on the fly.

Think of Excel: it's trivial there to define a cell as being the sum of the
cell above and the cell to the left.

Why you can't use IE but can use FireFox makes no sense to me either.
Otherway around, I could understand in a second: it wouldn't surprise me if
Microsoft deliberately doesn't play well with FireFox!

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Robert T" wrote in message
news:FBE6CAED-E989-4EB0-858E-8489DCA9AFDA@microsoft.com...
> Hello Douglas:
>
> OK, you've convinced me, I won't store the running balance in the table.
> That will definitely save me a lot of time in terms of writing a complex
> script for a trivial benefit.
>
> What did you mean when you said,
> [just because Money and Quicken display a running balance does not
> necessarily mean that it stores that running balance.]
>
> If they aren't storing the running balance, what are they doing?
>
> I will definitely look RunningSum property of a report. I haven't tried it
> yet but it does indeed look as if it's pretty easy to implement.
>
> Thanks so much for taking the time to answer my questions. Now if I can
only
> figure out why I can't use IE to pose questions and/or reply, I'll feel a
> little better.
>
> Robert
>
> "Douglas J Steele" wrote:
>
> > To be honest, I haven't looked at the sample database myself (and I
can't,
> > because I'm limited to Access 97 at the moment), but I did notice that
it
> > does what you're trying to do.
> >
> > Just because Money and Quicken display a running balance does not
> > necessarily mean that it stores that running balance.
> >
> > While you certainly could replicate what you did in Alpha Five using VBA
in
> > Access, I'd recommend very strongly not to. It's fine and dandy to say
that
> > you're going to run the code every time the user changes something, but
what
> > happens if for some reason you use a query to update the data rather
than
> > using the form? Perhaps Alpha Five has triggers, but Access doesn't.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10531
> >
> > If you want a running balance, it's trivial to do in a report (look at
the
> > RunningSum property that appears in the text box control on reports).
For a
> > form, I feel it's much safer to create a running sum in a query, and use
> > that query as the basis for the report (rather than a table).
> >
> > If your table includes a field that represents the Date/Time when the
entry
> > was posted to table, you can calculate the running sum in the query as
> > DSum("MyField", "MyTable", "MyDateTimeField <=" &
CDbl([MyDateTimeField]))
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no e-mails, please!)
> >
> >
> > "Robert T" wrote in message
> > news:4F3058F8-AA01-4BBF-82B7-FA196AFA9517@microsoft.com...
> > > Hello Doug:
> > >
> > > Yes I did download the application you mentioned earlier and it seems
like
> > > pretty basic stuff, I didn't find anything in there that I thought was
> > > enlightening. Maybe I missed something in there so I'll definitely
take
> > > another look at the application later. Was there anything specific you
> > wanted
> > > me to check out?
> > >
> > > In general I agree with both you and John Vinson about storing
calculated
> > > values in the table, however, let me add something different to the
> > equation.
> > > First, I like the idea of a running balance, that's obviously what
Money
> > and
> > > Quicken do and I find that helps. So I worked hard and found a way to
> > avoid
> > > the problem you described.
> > >
> > > I used Xbasic [Alpha Five] to update all of the relevant records in
the
> > > table when a value is changed. So if the user goes back to an old
record
> > and
> > > changes the amount of a check or deposit, that will change the running
> > > balance for that record and every record that follows. I therefore
> > utilized
> > > a script that goes back to the previous record of the one that was
> > changed,
> > > captures the previous balance, and then the script recalculates the
> > running
> > > balance starting with the changed record, updating all subsequent
records
> > in
> > > the table. It took a long time to figure out how to do such, but I'm
sure
> > > that with a little experience I could do such with Visual Basic.
> > >
> > > Robert
> > >
> > >
> > > "Douglas J Steele" wrote:
> > >
> > > > Personally, since I can't think of different attributes between
Debits
> > and
> > > > Credits, I'd be loathe to treat them as separate entities. That
implies
> > only
> > > > one table. (I hinted at this before when I said I couldn't see why
there
> > > > would be null fields)
> > > >
> > > > I also don't think you should be storing a calculated value (the
running
> > > > balance) in a table. As fellow Access MVP John Vinson likes to say
> > "Storing
> > > > calculated data generally accomplishes only three things: it wastes
disk
> > > > space, it wastes time (a disk fetch is much slower than almost any
> > > > reasonable calculation), and it risks data validity, since once it's
> > stored
> > > > in a table either the Total or one of the fields that goes into the
> > total
> > > > may be changed, making the value WRONG."
> > > >
> > > > It is possible (although usually inefficient) to calculate a running
> > balance
> > > > in a query. For example, you can use the DSum function to calculate
the
> > sum
> > > > of all records prior to the current record.
> > > >
> > > > --
> > > > Doug Steele, Microsoft Access MVP
> > > > http://I.Am/DougSteele
> > > > (no e-mails, please!)
> > > >
> > > >
> > > > "Robert T" wrote in message
> > > > news:12842D38-C129-498B-A9C4-BF6D7C04494E@microsoft.com...
> > > > > Hi Doug:
> > > > >
> > > > > Once again, thanks for the prompt response showing me how to
enable or
> > > > > disable a field on a form. I just knew that type of option had to
be
> > > > > available.
> > > > >
> > > > > As for your question about why I'm doing this, I already have a
> > checking
> > > > > application in Alpha Five that works perfectly, plus I regularly
use
> > > > Quicken.
> > > > > I'm not going through this process because I want a checking
> > application
> > > > in
> > > > > Access. Since I need Access skills for my new job, I'm doing this
> > strictly
> > > > as
> > > > > a learning experience. The skills I pick up by designing this
> > application
> > > > can
> > > > > be applied to many other types of applications. Doug, I just
happened
> > to
> > > > pick
> > > > > the checking application randomly.
> > > > >
> > > > > I realize you can make a currency transaction positive or
negative,
> > but
> > > > > most financial applications usually have separate fields for
Debits
> > and
> > > > > Credits. Do you think using one field is a better idea?
> > > > >
> > > > > By the way, that brings me back to my previous question. What do
you
> > think
> > > > > of the idea of creating two separate tables for transactions, one
to
> > > > record
> > > > > checks and a 2nd one to record only deposits/credits? As I stated
> > before,
> > > > > that would eliminate null fields. Or do you prefer placing all
> > > > transactions
> > > > > in one table?
> > > > >
> > > > > By the way, any thoughts on my other question reference a running
> > balance
> > > > > field for each transaction?
> > > > >
> > > > > Thanks,
> > > > > RT
> > > > >
> > > > > "Douglas J Steele" wrote:
> > > > >
> > > > > > "Robert T" wrote in message
> > > > > > news:59F49FC9-0A5E-40AD-B6A5-CB8433EF578A@microsoft.com...
> > > > > > > Hi Doug:
> > > > > > >
> > > > > > > Thanks for the prompt response.
> > > > > > >
> > > > > > > Since I'm relatively new to Access, would you be kind enough
to
> > give
> > > > me an
> > > > > > > example of how I can do such in Form Design? For example, if
the
> > > > > > > Type_Transaction is a "Debit", I want to ensure the Credits
> > Currency
> > > > field
> > > > > > > will remain blank?
> > > > > >
> > > > > > It will depend on how your form is structured, but try something
> > like
> > > > the
> > > > > > following:
> > > > > >
> > > > > > Private Sub Form_Current()
> > > > > >
> > > > > > If Me!txtType_Transaction = "Debit" Then
> > > > > > Me![Credits Currency].Enabled = False
> > > > > > Me![Debits Currenty].Enabled = True
> > > > > > Else
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10531
> > > > > > Me![Credits Currency].Enabled = True
> > > > > > Me![Debits Currenty].Enabled = False
> > > > > > End If
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > > Actually, that can be simplied to:
> > > > > >
> > > > > > Private Sub Form_Current()
> > > > > >
> > > > > > Me![Credits Currency].Enabled = (Me!txtType_Transaction <>
> > "Debit")
> > > > > > Me![Debits Currenty].Enabled = (Me!txtType_Transaction =
"Debit")
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > >
> > > > > > > Second, do you think using one Transaction Table is a good
option
> > or
> > > > > > should
> > > > > > > I divide it into two tables, Checks and Deposits/Credits, that
> > would
> > > > > > ensure
> > > > > > > there are no NULL fields? That would obviously remove the need
to
> > skip
> > > > > > fields
> > > > > > > in this particular application.
> > > > > >
> > > > > > I don't see why you'd need Null fields. I don't believe you need
to
> > > > > > distinguish between Credits and Debits in your table: the sign
of
> > the
> > > > entry
> > > > > > should indicate for you.
> > > > > >
> > > > > > Just because you've got two fields on your form doesn't mean you
> > must
> > > > have
> > > > > > two fields in your table.
> > > > > >
> > > > > > > Third, it's really nice setting the Skip Field Rule in the
table
> > > > because
> > > > > > it
> > > > > > > will be enforced no matter what data entry form is used. Just
a
> > > > thought.
> > > > > >
> > > > > > Perhaps, but there isn't, so there's nothing to be gained by
going
> > down
> > > > that
> > > > > > path...
> > > > > >
> > > > > > > Fourth, I have to use Firefox to post my questions/replies.
When I
> > > > click
> > > > > > on
> > > > > > > Post a Question or Reply in Internet Explorer, I get something
> > similar
> > > > to
> > > > > > the
> > > > > > > Windows hourglass and I never see the Post Question or Reply
form.
> > I
> > > > know
> > > > > > > this isn't an Access question but do you or anyone else have
any
> > idea
> > > > why
> > > > > > > that is happening in IE?
> > > > > >
> > > > > > Sorry, I never use the web interface.
> > > > > >
> > > > > > > Thanks
> > > > > >
> > > > > > Is this just something you're building for the heck of it?
Strikes
> > me
> > > > that
> > > > > > buying Quicken or Money would be a lot faster.
> > > > > >
> > > > > > You might also try downloading the Accounts ledger database from
> > > > > > http://office.microsoft.com/en-au/te...175341033.aspx
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Doug Steele, Microsoft Access MVP
> > > > > > http://I.Am/DougSteele
> > > > > > (no e-mails, please!)
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >