Checkbook Register

[Edd]

I am trying to create a checkbook register with running balance in the
query in Access

I have spent hours and hours researching this with multiple
suggestions, none of which have been successful.

File=CheckRegister
Fields=ID, Seq, Date, Ck#, CkAmt, Deposit.
Desired Result=Balance

I have put in sequence rather than depending on the autonumber ID so
that if I have something listed out of the desired sequence, I can
alter the sequence number and resort without having to re-enter the 2
records.

Most of the suggestions have included the function DSum which I have
not used before.

Any help would be appreciated. I would like to have this functional
by 01-01-10

Thanks

 

[Bob Barrows]

I am trying to create a checkbook register with running balance in the
query in Access

I have spent hours and hours researching this with multiple
suggestions, none of which have been successful.

File=CheckRegister
Fields=ID, Seq, Date

Date, being a reserved keyword, is a horrible name for your field. It's also
the name of a VBA function, so if you fail to rename the field, someday you
will run into the dreaded "unrecognized function" error that will take you
days to debug. Change the name now. Call it "TransactionDate" or something.

, Ck#, CkAmt, Deposit.

Does CkAmt contain positive or negative numbers? I'm going to assume it
contains positive numbers and negate them in the calculation. Change the
subtraction to addition in my expressions if I've guessed wrong.

Desired Result=Balance

I have put in sequence rather than depending on the autonumber ID so
that if I have something listed out of the desired sequence, I can
alter the sequence number and resort without having to re-enter the 2
records.

Most of the suggestions have included the function DSum which I have
not used before.

Any help would be appreciated. I would like to have this functional
by 01-01-10

You should do the running balance in a report rather than in a query. Access
reports have this functionality built in IIRC.

If you absolutely must have this in a query, then yes, DSum is one way to do
it, although I prefer using a correlated subquery. I will show both ways:

SELECT ID, Seq, TransactionDate, [Ck#], CkAmt, Deposit,
SELECT Sum(Deposit - CkAmt)
FROM CheckRegister as r where r.Seq <= c.Seq) as Balance
FROM CheckRegister as c

SELECT ID, Seq, TransactionDate, [Ck#], CkAmt, Deposit,
DSum("[Deposit]-[CkAmt]", "[CheckRegister]","[Seq] <= " & c.Seq) as Balance
FROM CheckRegister as c

HTH,
Bob Barrows

 

[Edd]

I am trying to create a checkbook register with running balance in the
query in Access

I have spent hours and hours researching this with multiple
suggestions, none of which have been successful.

File=CheckRegister
Fields=ID, Seq, Date

Date, being a reserved keyword, is a horrible name for your field. It's also
the name of a VBA function, so if you fail to rename the field, someday you
will run into the dreaded "unrecognized function" error that will take you
days to debug. Change the name now. Call it "TransactionDate" or something.

, Ck#, CkAmt, Deposit.

Does CkAmt contain positive or negative numbers?  I'm going to assume it
contains positive numbers and negate them in the calculation. Change the
subtraction to addition in my expressions if I've guessed wrong.

Desired Result=Balance

I have put in sequence rather than depending on the autonumber ID so
that if I have something listed out of the desired sequence, I can
alter the sequence number and resort without having to re-enter the 2
records.

Most of the suggestions have included the function DSum which I have
not used before.

Any help would be appreciated.  I would like to have this functional
by 01-01-10

You should do the running balance in a report rather than in a query. Access
reports have this functionality built in IIRC.

If you absolutely must have this in a query, then yes, DSum is one way todo
it, although I prefer using a correlated subquery. I will show both ways:

SELECT ID, Seq, TransactionDate, [Ck#], CkAmt, Deposit,
SELECT Sum(Deposit - CkAmt)
FROM CheckRegister as r where r.Seq <= c.Seq) as Balance
FROM CheckRegister as c

SELECT ID, Seq, TransactionDate, [Ck#], CkAmt, Deposit,
DSum("[Deposit]-[CkAmt]", "[CheckRegister]","[Seq] <= " & c.Seq) as Balance
FROM CheckRegister as c

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

I do want this in a query because several different reports can be
generated from this one query where the balance function would not be
required. Such as year end total payments to a specific payee, check
# & amounts in check# sequence, etc. thereby minimizing the number of
queries I will be needing to generate the reports I require.

I will change Date to TDate. However, ID, Date, and some other fields
such as check#, payee, purpose are inconsequential to the purpose of
the function I am trying to create.

I am used to using the expression builder and have no clue where to
enter the recommendation you made.

I'm just wanting to be able to get the following
results.

CkAmt Deposit Balance

0 100.00 100.00

25.00 0 75.00

20.00 0 55.00

0 45.00 100.00

My ignorance of terminology and limited experience with making complex
statements in access perhaps confused you for which I apologize.

Thank you for your prompt attention to my request.

 

[Edd]

Date, being a reserved keyword, is a horrible name for your field. It'salso
the name of a VBA function, so if you fail to rename the field, somedayyou
will run into the dreaded "unrecognized function" error that will take you
days to debug. Change the name now. Call it "TransactionDate" or something.

Does CkAmt contain positive or negative numbers?  I'm going to assumeit
contains positive numbers and negate them in the calculation. Change the
subtraction to addition in my expressions if I've guessed wrong.

You should do the running balance in a report rather than in a query. Access
reports have this functionality built in IIRC.

If you absolutely must have this in a query, then yes, DSum is one way to do
it, although I prefer using a correlated subquery. I will show both ways:

SELECT ID, Seq, TransactionDate, [Ck#], CkAmt, Deposit,
SELECT Sum(Deposit - CkAmt)
FROM CheckRegister as r where r.Seq <= c.Seq) as Balance
FROM CheckRegister as c

SELECT ID, Seq, TransactionDate, [Ck#], CkAmt, Deposit,
DSum("[Deposit]-[CkAmt]", "[CheckRegister]","[Seq] <= " & c.Seq) as Balance
FROM CheckRegister as c

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

I do want this in a query because several different reports can be
generated from this one query where the balance function would not be
required.  Such as year end total payments to a specific payee, check
# & amounts in check# sequence, etc. thereby minimizing the number of
queries I will be needing to generate the reports I require.

I will change Date to TDate. However, ID, Date, and some other fields
such as check#, payee, purpose are inconsequential to the purpose of
the function I am trying to create.

I am used to using the expression builder and have no clue where to
enter the recommendation you made.

I'm just wanting to be able to get the following
results.

CkAmt              Deposit       Balance

0               100.00         100.00

25.00                       0            75.00

20.00                       0            55.00

0                 45.00          100.00

My ignorance of terminology and limited experience with making complex
statements in access perhaps confused you for which I apologize.

Thank you for your prompt attention to my request.- Hide quoted text -

- Show quoted text -

I forgot to mention that Seq will be the <= criteria, factor or
whatever it is called.

 

[John Spencer]

Try something like the following (This query can only be built in SQL view due
the use of a non-equi join

SELECT A.ID, A.Seq, A.Date, A.[Ck#], A.CkAmt, A.Deposit
, Sum(B.Deposit) - Sum(B.CkAmt) as Balance
FROM CheckRegister as A INNER JOIN CheckRegister as B
ON A.SEQ >= B.SEQ
GROUP BY A.ID, A.Seq, A.Date, A.[Ck#], A.CkAmt, A.Deposit


Alternative that can be built in query design view, but is not as efficient
SELECT A.ID, A.Seq, A.Date, A.[Ck#], A.CkAmt, A.Deposit
, Sum(B.Deposit) - Sum(B.CkAmt) as Balance
FROM CheckRegister as A, CheckRegister as B
WHERE A.Date >= B.Date
GROUP BY A.ID, A.Seq, A.Date, A.[Ck#], A.CkAmt, A.Deposit

In query design view:
== Add the table TWICE
== SELECT all the fields you want to see from the first table
== SELECT Deposit and CkAmt from the second table
== Select View: Totals from the menu
== Change Group By to Sum under the second instance of Deposit and ckAmt
== Enter the following as criteria under the first Seq field

=[CheckRegister_1].[Seq]

== If you want to get the balance you need to enter the expression in another
field "cell"
Balance: Sum([CheckRegister_1].[Deposit]) - Sum([CheckRegister_1].[CkAmt])
== Change GROUP BY to Expression under this calculated field




John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Bob Barrows]

I am used to using the expression builder and have no clue where to
enter the recommendation you made.

None of this is easily buildable with the expression builder. This is
the time you need to bite the bullet and start learning SQL (the
language, not the database system).

You need to create a query and switch it to SQL View. Then paste in the
query I recommended, deleting the fields you are not interested in.
Switch to Design View to see how it would have been entered in the
Design grid.