Just one laat step to finish

[Jan Il]

Hi all - Access 2002 - Windows ME

I am in hopes that someone can give me a bit of direction on my last step to
complete my project. I have all the tables, queries, forms and such
completed, and I need just one last piece of the puzzle to put it all
together.

I need to calculate a balance amount. Yep, still there. I had tried
everything I can think of, After Update the form, Sum, SubSum, MaybeaSum,
Don'tWantaSum in the form control. Then Balance: Nz, Where, When, Who, If,
Maybe, I Dunno, Lookup, Lookdown, Dlookup, Youlookup ...for criteria in the
query.

This is the very last piece I need, and I have looked at all the Help and
KB's I could think of, but, so far, I've come up with nothing bet errors.
Here is the current SQL of the query for the form I now have:

SELECT MyCheckRegister.CheckNo, MyCheckRegister.CheckDate,
MyCheckRegister.Transaction, MyCheckRegister.CheckAmt,
MyCheckRegister.DepositAmt, Sum([CheckAmt]-[DepositAmt]) AS Balance,
MyCheckRegister.TransactionType, MyCheckRegister.Comment
FROM MyCheckRegister
GROUP BY MyCheckRegister.CheckNo, MyCheckRegister.CheckDate,
MyCheckRegister.Transaction, MyCheckRegister.CheckAmt,
MyCheckRegister.DepositAmt, MyCheckRegister.TransactionType,
MyCheckRegister.Comment
ORDER BY MyCheckRegister.CheckDate;

I realize this is not correct, but, at this point, just not sure where I
need to make the correction.

And, no, this is not just an exercise, it is for actual use. And yes, I do
have good reason to engage in such a project. Let's just say that, the 3rd
time losing all my personal banking info due to a simple upgrade of a very
inexpensive program which can do this task so much easier, but, which is
very poorly supported, is 3 times too many. 'kay. ;-((

I would truly appreciate any suggestions, or perhaps a reference to a
website that would provide information that might address this issue.

Very best regards,
Jan

 

[Tom Ellison]

Dear Jan:

For my own reference, I have reformatted your query a bit:

SELECT CheckNo, CheckDate, Transaction, CheckAmt,
DepositAmt, Sum([CheckAmt]-[DepositAmt]) AS Balance,
TransactionType, Comment
FROM MyCheckRegister
GROUP BY CheckNo, CheckDate, Transaction, CheckAmt,
DepositAmt, TransactionType, Comment
ORDER BY CheckDate;

I assume you want to have a running balance. Here are the steps I'd use:

1. Forget having any grouping. This is not a 'Total' query. You want to
see each and every check.

SELECT CheckNo, CheckDate, Transaction, CheckAmt,
DepositAmt, [CheckAmt]-[DepositAmt] AS Balance,
TransactionType, Comment
FROM MyCheckRegister
ORDER BY CheckDate;

2. The running balance must sort uniquely. That is, within a given date,
you must specify the order of the transactions. Adding CheckNo to the
ordering should do nicely, assuming your deposits have some unique value in
this column as well.

SELECT CheckNo, CheckDate, Transaction, CheckAmt,
DepositAmt, [CheckAmt]-[DepositAmt] AS Balance,
TransactionType, Comment
FROM MyCheckRegister
ORDER BY CheckDate, CheckNo;

3. The running sum is performed with a "Correlated Subquery" which may be a
new thing for you. I'm going to put this column last - I think that may
make good sense. Also, the balance should go up with a deposit and down
with a check, so I'm going to use DepositAmt - CheckAmt (reversing the
subtraction you had).

SELECT CheckNo, CheckDate, Transaction, CheckAmt,
DepositAmt, TransactionType, Comment,
(SELECT SUM(DepositAmt- CheckAmt)
FROM MyCheckRegister T1
WHERE T1.CheckDate < T,CheckDate
OR (T1.CheckDate = T.CheckDate
AND T1.CheckNo <= T.CheckNo))
AS RunningBalance
FROM MyCheckRegister T
ORDER BY CheckDate, CheckNo;

This last piece is a bit more advanced. You may wish to study topics
"subqueries" and "aliasing table names" to understand it. It says to
calculate the "Running Balance" by adding up the difference (DepositAmt -
CheckAmt) of all the preceding checks and deposits plus the current
check/deposit amount. This is the big reason why I added CheckNo to the
ordering of the results. Within a specific date, the checks or deposits
will be accumulated in a specific order. Without this distinction, the
running balance would be a "daily balance" that would change once per day
and not for each item. I'm guessing that's not what you want.

Please let me know if this helped, and if I can be of any other assistance.

--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Hi all - Access 2002 - Windows ME

I am in hopes that someone can give me a bit of direction on my last step to
complete my project. I have all the tables, queries, forms and such
completed, and I need just one last piece of the puzzle to put it all
together.

I need to calculate a balance amount. Yep, still there. I had tried
everything I can think of, After Update the form, Sum, SubSum, MaybeaSum,
Don'tWantaSum in the form control. Then Balance: Nz, Where, When, Who, If,
Maybe, I Dunno, Lookup, Lookdown, Dlookup, Youlookup ...for criteria in the
query.

This is the very last piece I need, and I have looked at all the Help and
KB's I could think of, but, so far, I've come up with nothing bet errors.
Here is the current SQL of the query for the form I now have:

SELECT MyCheckRegister.CheckNo, MyCheckRegister.CheckDate,
MyCheckRegister.Transaction, MyCheckRegister.CheckAmt,
MyCheckRegister.DepositAmt, Sum([CheckAmt]-[DepositAmt]) AS Balance,
MyCheckRegister.TransactionType, MyCheckRegister.Comment
FROM MyCheckRegister
GROUP BY MyCheckRegister.CheckNo, MyCheckRegister.CheckDate,
MyCheckRegister.Transaction, MyCheckRegister.CheckAmt,
MyCheckRegister.DepositAmt, MyCheckRegister.TransactionType,
MyCheckRegister.Comment
ORDER BY MyCheckRegister.CheckDate;

I realize this is not correct, but, at this point, just not sure where I
need to make the correction.

And, no, this is not just an exercise, it is for actual use. And yes, I do
have good reason to engage in such a project. Let's just say that, the 3rd
time losing all my personal banking info due to a simple upgrade of a very
inexpensive program which can do this task so much easier, but, which is
very poorly supported, is 3 times too many. 'kay. ;-((

I would truly appreciate any suggestions, or perhaps a reference to a
website that would provide information that might address this issue.

Very best regards,
Jan

 

[Jan Il]

Hi Tom!

Dear Jan:

For my own reference, I have reformatted your query a bit:

SELECT CheckNo, CheckDate, Transaction, CheckAmt,
DepositAmt, Sum([CheckAmt]-[DepositAmt]) AS Balance,
TransactionType, Comment
FROM MyCheckRegister
GROUP BY CheckNo, CheckDate, Transaction, CheckAmt,
DepositAmt, TransactionType, Comment
ORDER BY CheckDate;

I assume you want to have a running balance. Here are the steps I'd use:

Yes, the running balance is what I want to get, so that it will adjust to
the check or deposit amount.

1. Forget having any grouping. This is not a 'Total' query. You want to
see each and every check.

SELECT CheckNo, CheckDate, Transaction, CheckAmt,
DepositAmt, [CheckAmt]-[DepositAmt] AS Balance,
TransactionType, Comment
FROM MyCheckRegister
ORDER BY CheckDate;

2. The running balance must sort uniquely. That is, within a given date,
you must specify the order of the transactions. Adding CheckNo to the
ordering should do nicely, assuming your deposits have some unique value in
this column as well.

SELECT CheckNo, CheckDate, Transaction, CheckAmt,
DepositAmt, [CheckAmt]-[DepositAmt] AS Balance,
TransactionType, Comment
FROM MyCheckRegister
ORDER BY CheckDate, CheckNo;

3. The running sum is performed with a "Correlated Subquery" which may be a
new thing for you. I'm going to put this column last - I think that may
make good sense. Also, the balance should go up with a deposit and down
with a check, so I'm going to use DepositAmt - CheckAmt (reversing the
subtraction you had).

True, I have not done a correlated subquery. I see where reversing the
subtraction from what I had would make a difference in how the balance
adjusts.

SELECT CheckNo, CheckDate, Transaction, CheckAmt,
DepositAmt, TransactionType, Comment,
(SELECT SUM(DepositAmt- CheckAmt)
FROM MyCheckRegister T1
WHERE T1.CheckDate < T,CheckDate
OR (T1.CheckDate = T.CheckDate
AND T1.CheckNo <= T.CheckNo))
AS RunningBalance
FROM MyCheckRegister T
ORDER BY CheckDate, CheckNo;

This last piece is a bit more advanced. You may wish to study topics
"subqueries" and "aliasing table names" to understand it. It says to
calculate the "Running Balance" by adding up the difference (DepositAmt -
CheckAmt) of all the preceding checks and deposits plus the current
check/deposit amount. This is the big reason why I added CheckNo to the
ordering of the results. Within a specific date, the checks or deposits
will be accumulated in a specific order. Without this distinction, the
running balance would be a "daily balance" that would change once per day
and not for each item. I'm guessing that's not what you want.

Your guess is correct, the per item balance is what I want, not just a daily
total. With the per item method I will know what the balance is after each
transaction. It will also make reconciliation easier if I happen to make an
error along the way, say, transposing numbers. It would much easier than
having to go back through the whole wazoolie of transactions to find the
error.

Please let me know if this helped, and if I can be of any other

assistance.

Thank you very much for your time to assist with this issue, and for the
time to explain and walk me though the process. I'll have a go with the
information you have kindly provided, and let you know how I fare. I'll
also study the subqueries and aliasing table names topics as you suggest.
I'm sure it will help me better understand the 'why' along with the 'how.'

Very best regards,
Jan

--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Hi all - Access 2002 - Windows ME

I am in hopes that someone can give me a bit of direction on my last

step

to
complete my project. I have all the tables, queries, forms and such
completed, and I need just one last piece of the puzzle to put it all
together.

I need to calculate a balance amount. Yep, still there. I had tried
everything I can think of, After Update the form, Sum, SubSum, MaybeaSum,
Don'tWantaSum in the form control. Then Balance: Nz, Where, When, Who, If,
Maybe, I Dunno, Lookup, Lookdown, Dlookup, Youlookup ...for criteria in the
query.

This is the very last piece I need, and I have looked at all the Help and
KB's I could think of, but, so far, I've come up with nothing bet errors.
Here is the current SQL of the query for the form I now have:

SELECT MyCheckRegister.CheckNo, MyCheckRegister.CheckDate,
MyCheckRegister.Transaction, MyCheckRegister.CheckAmt,
MyCheckRegister.DepositAmt, Sum([CheckAmt]-[DepositAmt]) AS Balance,
MyCheckRegister.TransactionType, MyCheckRegister.Comment
FROM MyCheckRegister
GROUP BY MyCheckRegister.CheckNo, MyCheckRegister.CheckDate,
MyCheckRegister.Transaction, MyCheckRegister.CheckAmt,
MyCheckRegister.DepositAmt, MyCheckRegister.TransactionType,
MyCheckRegister.Comment
ORDER BY MyCheckRegister.CheckDate;

I realize this is not correct, but, at this point, just not sure where I
need to make the correction.

And, no, this is not just an exercise, it is for actual use. And yes, I do
have good reason to engage in such a project. Let's just say that, the 3rd
time losing all my personal banking info due to a simple upgrade of a very
inexpensive program which can do this task so much easier, but, which is
very poorly supported, is 3 times too many. 'kay. ;-((

I would truly appreciate any suggestions, or perhaps a reference to a
website that would provide information that might address this issue.

Very best regards,
Jan

 

[Jan Il]

Hi Tom!

I have one quick question...you show T1 and T in the code. I'm thinking
these mean Transaction, but not sure. Would you clarify this reference for
me please. I'm getting a syntax error for this part. I have learned never to
make any changes unless instructed to do so, therefore, I want to check back
with you first.

(SELECT SUM(DepositAmt- CheckAmt)
FROM MyCheckRegister T1
WHERE T1.CheckDate < T,CheckDate
OR (T1.CheckDate = T.CheckDate
AND T1.CheckNo <= T.CheckNo))

Thank you.

Jan

Dear Jan:

For my own reference, I have reformatted your query a bit:

SELECT CheckNo, CheckDate, Transaction, CheckAmt,
DepositAmt, Sum([CheckAmt]-[DepositAmt]) AS Balance,
TransactionType, Comment
FROM MyCheckRegister
GROUP BY CheckNo, CheckDate, Transaction, CheckAmt,
DepositAmt, TransactionType, Comment
ORDER BY CheckDate;

I assume you want to have a running balance. Here are the steps I'd use:

1. Forget having any grouping. This is not a 'Total' query. You want to
see each and every check.

SELECT CheckNo, CheckDate, Transaction, CheckAmt,
DepositAmt, [CheckAmt]-[DepositAmt] AS Balance,
TransactionType, Comment
FROM MyCheckRegister
ORDER BY CheckDate;

2. The running balance must sort uniquely. That is, within a given date,
you must specify the order of the transactions. Adding CheckNo to the
ordering should do nicely, assuming your deposits have some unique value in
this column as well.

SELECT CheckNo, CheckDate, Transaction, CheckAmt,
DepositAmt, [CheckAmt]-[DepositAmt] AS Balance,
TransactionType, Comment
FROM MyCheckRegister
ORDER BY CheckDate, CheckNo;

3. The running sum is performed with a "Correlated Subquery" which may be a
new thing for you. I'm going to put this column last - I think that may
make good sense. Also, the balance should go up with a deposit and down
with a check, so I'm going to use DepositAmt - CheckAmt (reversing the
subtraction you had).

SELECT CheckNo, CheckDate, Transaction, CheckAmt,
DepositAmt, TransactionType, Comment,
(SELECT SUM(DepositAmt- CheckAmt)
FROM MyCheckRegister T1
WHERE T1.CheckDate < T,CheckDate
OR (T1.CheckDate = T.CheckDate
AND T1.CheckNo <= T.CheckNo))
AS RunningBalance
FROM MyCheckRegister T
ORDER BY CheckDate, CheckNo;

This last piece is a bit more advanced. You may wish to study topics
"subqueries" and "aliasing table names" to understand it. It says to
calculate the "Running Balance" by adding up the difference (DepositAmt -
CheckAmt) of all the preceding checks and deposits plus the current
check/deposit amount. This is the big reason why I added CheckNo to the
ordering of the results. Within a specific date, the checks or deposits
will be accumulated in a specific order. Without this distinction, the
running balance would be a "daily balance" that would change once per day
and not for each item. I'm guessing that's not what you want.

Please let me know if this helped, and if I can be of any other assistance.

--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Hi all - Access 2002 - Windows ME

I am in hopes that someone can give me a bit of direction on my last

step

to
complete my project. I have all the tables, queries, forms and such
completed, and I need just one last piece of the puzzle to put it all
together.

I need to calculate a balance amount. Yep, still there. I had tried
everything I can think of, After Update the form, Sum, SubSum, MaybeaSum,
Don'tWantaSum in the form control. Then Balance: Nz, Where, When, Who, I f,
Maybe, I Dunno, Lookup, Lookdown, Dlookup, Youlookup ...for criteria in the
query.

This is the very last piece I need, and I have looked at all the Help and
KB's I could think of, but, so far, I've come up with nothing but errors.
Here is the current SQL of the query for the form I now have:

SELECT MyCheckRegister.CheckNo, MyCheckRegister.CheckDate,
MyCheckRegister.Transaction, MyCheckRegister.CheckAmt,
MyCheckRegister.DepositAmt, Sum([CheckAmt]-[DepositAmt]) AS Balance,
MyCheckRegister.TransactionType, MyCheckRegister.Comment
FROM MyCheckRegister
GROUP BY MyCheckRegister.CheckNo, MyCheckRegister.CheckDate,
MyCheckRegister.Transaction, MyCheckRegister.CheckAmt,
MyCheckRegister.DepositAmt, MyCheckRegister.TransactionType,
MyCheckRegister.Comment
ORDER BY MyCheckRegister.CheckDate;

I realize this is not correct, but, at this point, just not sure where I
need to make the correction.

And, no, this is not just an exercise, it is for actual use. And yes, I do
have good reason to engage in such a project. Let's just say that, the 3rd
time losing all my personal banking info due to a simple upgrade of a very
inexpensive program which can do this task so much easier, but, which is
very poorly supported, is 3 times too many. 'kay. ;-((

I would truly appreciate any suggestions, or perhaps a reference to a
website that would provide information that might address this issue.

Very best regards,
Jan

 

[Jan Il]

Okay..never mind this part, I got it now. Should have done my homework
first.

...you show T1 and T in the code. I'm thinking
these mean Transaction, but not sure. Would
you clarify this reference for me please.

But, still get the syntax error here,

(SELECT SUM(DepositAmt- CheckAmt)
FROM MyCheckRegister T1
WHERE T1.CheckDate < T,CheckDate
OR (T1.CheckDate = T.CheckDate
AND T1.CheckNo <= T.CheckNo))

Thank you.

 

[Tom Ellison]

Please change the comma to a period as I suggested.

 

[Jan Il]

Hi Tom!

Please change the comma to a period as I suggested.

I have copied and pasted the current SQL for the query, having changed the
comma to a period in the as you instructed.

It should read:

WHERE T1.CheckDate < T.CheckDate

The SQL is no longer thowing any errors, however, there does not appear to
be any records being returned in the RunningBalance field, or in the
associated control on the form now based on this query. I do have a few
current check and deposit entries in the table in order to test with. I am
truly sorry to be so dense, obviously, I have made an error some place
and just can't see it. All other information is being properly displayed.

SELECT T.CheckNo, T.CheckDate, T.Transaction, T.CheckAmt, T.DepositAmt,
T.TransactionType, T.Comment,
(SELECT SUM(DepositAmt- CheckAmt)
FROM MyCheckRegister T1
WHERE T1.CheckDate < T.CheckDate
OR (T1.CheckDate = T.CheckDate
AND T1.CheckNo <= T.CheckNo)) AS RunningBalance
FROM MyCheckRegister AS T
ORDER BY T.CheckDate, T.CheckNo;

Thank you for all your help, and I truly appreciate your patience.

Best regards,
Jan

 

[Tom Ellison]

Dear Jan:

I'm not sure why it isn't working, but then you don't really tell me what is
wrong.

"there does not appear to be any records being returned in the
RunningBalance field"

Do you mean this column is blank? Or is it zero?

So, I'm going to take a guess. I'm going to guess that the value for
DepositAmt - CheckAmt is coming out null because one or the other of them is
always null. So, a fix for that is:

SELECT T.CheckNo, T.CheckDate, T.Transaction, T.CheckAmt,
T.DepositAmt, T.TransactionType, T.Comment,
(SELECT SUM(Nz(DepositAmt, 0) - Nz(CheckAmt, 0))
FROM MyCheckRegister T1
WHERE T1.CheckDate < T.CheckDate
OR (T1.CheckDate = T.CheckDate
AND T1.CheckNo <= T.CheckNo)) AS RunningBalance
FROM MyCheckRegister AS T
ORDER BY T.CheckDate, T.CheckNo;

This is because you cannot do arithmetic on null values. So, by replacing
them with zero when they are null, this would solve such a problem, if
indeed that is the problem.

If this doesn't fix it, please explain just what the results look like.

 

[Jan Il]

Hi Tom,
....

Dear Jan:

I'm not sure why it isn't working, but then you don't really tell me what is
wrong.

"there does not appear to be any records being returned in the
RunningBalance field"

Do you mean this column is blank? Or is it zero?

Sorry Tom, you're right, and I know better..just mentally still
troubleshooting I guess ...yes, the column was blank.

So, I'm going to take a guess. I'm going to guess that the value for
DepositAmt - CheckAmt is coming out null because one or the other of them is
always null. So, a fix for that is:

SELECT T.CheckNo, T.CheckDate, T.Transaction, T.CheckAmt,
T.DepositAmt, T.TransactionType, T.Comment,
(SELECT SUM(Nz(DepositAmt, 0) - Nz(CheckAmt, 0))
FROM MyCheckRegister T1
WHERE T1.CheckDate < T.CheckDate
OR (T1.CheckDate = T.CheckDate
AND T1.CheckNo <= T.CheckNo)) AS RunningBalance
FROM MyCheckRegister AS T
ORDER BY T.CheckDate, T.CheckNo;

This is because you cannot do arithmetic on null values. So, by replacing
them with zero when they are null, this would solve such a problem, if
indeed that is the problem.

If this doesn't fix it, please explain just what the results look like.

'k....I now have records in the RunningABalance column (as listed below),
however, they are showing the check amounts as a minus, and not
picking up the deposit amounts. If you notice in the list below, there
are two sets of three identical balance amount numbers in the
RunningBalance field, and this is just speculation on my part,
as I am not sure, but, the best I can determine from all this...

1) There was no beginning balance amount field inserted from the table into
the query, again, my mistake, but, didn't know for sure if this should be
included. Thus, it would seem there is nothing for the check amounts to be
calculated against at the start, therefore, they are all showing a minus
balance amount. Should I add the beginning balance field to the query to be
calculated against? It is called BeginBal.

2) The deposit and less cash amounts are not being calculated, because,
although they have a date, they do not have a check number. As the
calculations for RunningBalance are based upon the CheckNo, if there is no
check number in that field, the transaction is not being recognized, and the
last check amount balance is being carried over into the RunningBalance
again.

This is just what *seems* to be happening as best I can determine from the
information below. If you need additional information please let me know:

RunningBalance
-31.03
-51.07
-101.07
-151.07
-191.07
-314.27
-571.53 (last check written, last bal.)
-571.53 (deposit made) no check number
-571.53 (less cash amt deducted) no check number
-665.37
-176.39
-253.77 (last check written, last bal.)
-253.77 (deposit made) no check number
-253.77 (less cash amt deducted) no check number

Thank you very much for your continued assistance, and patience..

Regards,
Jan

 

[Jan Il]

Hi Tom,

"> >

them

'k....I now have records in the RunningABalance column (as listed below),
however, they are showing the check amounts as a minus, and not
picking up the deposit amounts. If you notice in the list below, there
are two sets of three identical balance amount numbers in the
RunningBalance field, and this is just speculation on my part,
as I am not sure, but, the best I can determine from all this...

1) There was no beginning balance amount field inserted from the table into
the query, again, my mistake, but, didn't know for sure if this should be
included. Thus, it would seem there is nothing for the check amounts to be
calculated against at the start, therefore, they are all showing a minus
balance amount. Should I add the beginning balance field to the query to be
calculated against? It is called BeginBal.

2) The deposit and less cash amounts are not being calculated, because,
although they have a date, they do not have a check number. As the
calculations for RunningBalance are based upon the CheckNo, if there is no
check number in that field, the transaction is not being recognized, and the
last check amount balance is being carried over into the RunningBalance
again.

This is just what *seems* to be happening as best I can determine from the
information below. If you need additional information please let me know:

RunningBalance
-31.03
-51.07
-101.07
-151.07
-191.07
-314.27
-571.53 (last check written, last bal.)
-571.53 (deposit made) no check number
-571.53 (less cash amt deducted) no check number
-665.37
-176.39
-253.77 (last check written, last bal.)
-253.77 (deposit made) no check number
-253.77 (less cash amt deducted) no check number

Thank you very much for your continued assistance, and patience..

Regards,
Jan

As a follow-up on this, as a test, I copied the original query and added the
BeginBal to the SQL of the copy as thus;

SELECT T.BeginBal, T.CheckNo, T.CheckDate, T.Transaction, T.CheckAmt,
T.DepositAmt, T.TransactionType, T.Comment, (SELECT SUM(Nz(DepositAmt, 0) -
Nz(CheckAmt, 0) + Nz(BeginBal, 0))
FROM MyCheckRegister T1
WHERE T1.CheckDate < T.CheckDate
OR (T1.CheckDate = T.CheckDate
AND T1.CheckNo <= T.CheckNo)) AS RunningBalance
FROM MyCheckRegister AS T
ORDER BY T.CheckDate, T.CheckNo;

And, from this I got this results in the RunningBalance column, which
according to my check amount calculations, the balance amounts are correct.
While I am sure this is not the proper way to write this entry, it does
indicate that the beginning balance will influence the RunningBalance
amounts for the checks. However, it does not address the issue with the
entries that do not have check numbers, such as the less cash and deposit
entries. These are still indicated by the additional duplicate balance
amounts.

RunningBalance
1040.04
1020
970
920
880
756.8
499.54
499.54
499.54
405.7
894.68
817.3
817.3
817.3

Thank you,
Jan

 

[Jan Il]

Hi Dan

-----Original Message-----
Hi,
What about having your date field have a default value of

Now() ?

I tried setting the date fields default value to Now()as
you suggested, and it did work. However, if there was a
situation where I could not enter the check(s) into the
register on the date they were written, I can not enter
the actual date of the check in the date field. While
this situation is an exception, not the rule, it does
sometimes happen that I must wait until the next day or so
to get the check entered in the register, such as when I
am visiting out of town, or just don't have time to make
the entry on the day the check is written. The same could
be true of deposits.

Perhaps there is some method that says, if there is no
check number, then use this. In the case of a deposit
where there is no check number to be entered, then go to
the CheckDate or DepositAmt field. I've tried to think of
ways this might be done, but, so far nothing I've come up
with has allowed the deposits or other transactions that
do not require a check number to be recognized and
calculated. That is the only thing at this point that is
not working.

Thank you for your assistance and patience. I do
appreciate it.

Regards,
Jan

 

[Jan Il]

Hi Dan!

Hi Jan,
Well, the simplest solution would be to enter the time as well:
7/28/2003 5:56:11 PM
The Query will then work.

Yes, you are correct, it does work, and does cause the RunningBalance to
correctly update with both the CheckAmt and DepositAmt. )

However, with this default setting, I can not enter a previous date. Which
means, that I can not go back and finish entering my checks for the month of
July, as it will not allow me to enter any date or time prior to what is
automatically entered into the CheckDate field now. It also means that, if I
might yet write a check today, and for some reason be unable to enter it
before midnight, I will not be able to go back and enter the check with
today's date, the actual date the check was written.

If this is not what is happening on your end, please let me know, as there
may be some other problem that is preventing me from being able to enter a
previous date and time from the Now() autoentry in the CheckDate field, and
I may need to make some changes or adjustments on my end.

Thank you very much for all your time and help. The pictures a wrap, but,
there is just a bit of fine tuning that needs to be done to get the action
scenes just right. Although I'm a novice and have never done this kind of
project before, I really am trying, and checking as many avenues as I can
think of on my end. I truly appreciate your help and patience.

Very best regards,
Jan

 

[Tom Ellison]

'k....I now have records in the RunningABalance column (as listed below),

however, they are showing the check amounts as a minus, and not
picking up the deposit amounts. If you notice in the list below, there
are two sets of three identical balance amount numbers in the
RunningBalance field, and this is just speculation on my part,
as I am not sure, but, the best I can determine from all this...

1) There was no beginning balance amount field inserted from the table into
the query, again, my mistake, but, didn't know for sure if this should be
included. Thus, it would seem there is nothing for the check amounts to be
calculated against at the start, therefore, they are all showing a minus
balance amount. Should I add the beginning balance field to the query to be
calculated against? It is called BeginBal.

2) The deposit and less cash amounts are not being calculated, because,
although they have a date, they do not have a check number. As the
calculations for RunningBalance are based upon the CheckNo, if there is no
check number in that field, the transaction is not being recognized, and the
last check amount balance is being carried over into the RunningBalance
again.

I think you have that correct. The running sum is calculated in steps that
cannot be "one record at a time" unless you have some columns that UNIQUELY
order these rows. I would have though there would be some identifying
number to deposits, and that this would be stored in the CheckNumber column.
If there were, this would reduce the problem. However, I did not know you
had "less cash amt deducted" which I think means you have 2 records for one
transaction. I may be best to make TransactionType a third part of the
unique order of the rows, and adjust the subquery to use this. Unless the
ordering of the rows which is also implemented in the running sum sub-query
is a unique order, then you are going to see times where several rows are
applied simultaneously. That's part of what you need to know when you
design the table, so you won't have surprises later on. But don't dispair -
nobody just jumps in and know to hold all these pieces together at first.

So, I'll be bold enough to suggest how I might design such a database
portion.

There would be a TransactionType table and each TransactionType would have a
"sign" value of +1 or -1 (-1 for checks and cash back, +1 for deposits and
initial balance).

In the TransactionTable I would have:

TransactionDate / TransactionNumber / TransactionType / Amount / Comment

I would not have two separate Amount columns for checks and deposits.

There would be a unique key imposed on the table of the first 3 columns
above. This would allow a deposit to have a deposit amount and a cash back
amount on two separate rows. An INNER JOIN to the TransactionType table
would retrieve the sign to multiply with the Amount. Then a running sum
could be performed. The running sum would be over the same first 3 columns,
so it would be guaranteed to be unique.

BeginBal would just be another TransactionType.

Make sense?

 

[Jan Il]

Hi Tom,

to

I think you have that correct. The running sum is calculated in steps that
cannot be "one record at a time" unless you have some columns that UNIQUELY
order these rows. I would have though there would be some identifying
number to deposits, and that this would be stored in the CheckNumber column.
If there were, this would reduce the problem.

In as much as the issue of a deposit slip number had never been involved
with my previous My Money program, I have to accept that this is my mistake
in not recognizing this fact, and letting you know that my bank does not
provide a number on my deposits slips that I can record. I truly apologize
for this lapse and any confusion it may have caused you.

However, I did not know you had "less cash amt deducted" which I think means
you have 2 records for one

transaction.

Again, this is a failure on my part to be more specific in explaining, and I
do know better. Perhaps it may be outside normal accounting practices, but,
I prefer to show the total amount of my deposit in the deposit column, and,
if necessary, any less cash deductions and their purpose as a separate
entry. This just helps me keep track of expenditures better.

It may be best to make TransactionType a third part of the unique order of
the rows, and adjust the subquery to use this. Unless the ordering of the
rows which is also implemented in the running sum sub-query is a unique
order, then you are going to see times where several rows are applied
simultaneously. That's part of what you need to know when you
design the table, so you won't have surprises later on. But don't despair -
nobody just jumps in and know to hold all these pieces together at first.

I did create a table for TreasactionType, but, did not know for sure if it
was necessary to have a separate table for this. There are so many
different aspects offered as to if things should be set up in separate
tables, or in one.

So, I'll be bold enough to suggest how I might design such a database
portion.

There would be a TransactionType table and each TransactionType would have a
"sign" value of +1 or -1 (-1 for checks and cash back, +1 for deposits and
initial balance).

In the TransactionTable I would have:

TransactionDate / TransactionNumber / TransactionType / Amount / Comment

I would not have two separate Amount columns for checks and deposits.

There would be a unique key imposed on the table of the first 3 columns
above. This would allow a deposit to have a deposit amount and a cash back
amount on two separate rows. An INNER JOIN to the TransactionType table
would retrieve the sign to multiply with the Amount. Then a running sum
could be performed. The running sum would be over the same first 3 columns,
so it would be guaranteed to be unique.

*There would be a TransactionType table and each TransactionType would have
a

"sign" value of +1 or -1 (-1 for checks and cash back, +1 for deposits and
initial balance).*

As just a bit of clarification...should there be a separate field to enter
the + or - information into? Do I need to add an additional column in
which to enter the - or + signs in order to associate them to the actual
Transaction type?

BeginBal would just be another TransactionType.

This is not problem..will do

Make sense?

Yes. Believe it or not, I really *am* following you Tom, and I can see
where his is supposed to be going. And, I can also see the 'why'
behind the 'how' now. I will work on this tomorrow as I can and get
back to you. It is the bewitching hour here for me, 11:49P, and I
have to rise and shine at 5:00a. to go play with my train set )
(at work)..so, I'll get back to you as soon as I have a chance to
work this out.

Thank you very much for your help, I really do appreciate all your
time and effort to see this through, and especially, your patience.

g'nite..

Regards,
Jan

 

[Dan Artuso]

HI Jan,
Not sure of your setup but, you should be able to change a default
value to whatever you want. Just make sure you enter the time value in
the format shown below. Or you can go with Tom's suggestion which I'm sure will
work as well

 

[Tom Ellison]

Hi Tom,

is

In as much as the issue of a deposit slip number had never been involved
with my previous My Money program, I have to accept that this is my mistake
in not recognizing this fact, and letting you know that my bank does not
provide a number on my deposits slips that I can record. I truly apologize
for this lapse and any confusion it may have caused you.

However, I did not know you had "less cash amt deducted" which I think means
you have 2 records for one

Again, this is a failure on my part to be more specific in explaining, and I
do know better. Perhaps it may be outside normal accounting practices, but,
I prefer to show the total amount of my deposit in the deposit column, and,
if necessary, any less cash deductions and their purpose as a separate
entry. This just helps me keep track of expenditures better.

It may be best to make TransactionType a third part of the unique order of
the rows, and adjust the subquery to use this. Unless the ordering of the
rows which is also implemented in the running sum sub-query is a unique
order, then you are going to see times where several rows are applied
simultaneously. That's part of what you need to know when you
design the table, so you won't have surprises later on. But don't despair -
nobody just jumps in and know to hold all these pieces together at first.

I did create a table for TreasactionType, but, did not know for sure if it
was necessary to have a separate table for this. There are so many
different aspects offered as to if things should be set up in separate
tables, or in one.

So, I'll be bold enough to suggest how I might design such a database
portion. have


*There would be a TransactionType table and each TransactionType would have

As just a bit of clarification...should there be a separate field to enter
the + or - information into? Do I need to add an additional column in
which to enter the - or + signs in order to associate them to the actual
Transaction type?

I was suggesting a separate table TransactionType with two columns:
TransactionType and Sign. The thinking is as follows. First, you shouldn't
have users typing in a Transaction Type without limiting them to a specific
list of choices. You could give these selections in a combo box using this
new table. Also, when a new Transaction Type is needed (say for "Service
Charge", "Interest Paid", or "Returned Check Fee"), you can simply add this
to the TransactionType table and it should work immediately.

Now the Sign column will allow you to enter both Checks and Deposits as
positive numbers. When you are summing them, as in the Running Balance
column, you will multiply each by 1 or -1 so they add and subtract
correctly. You make an INNER JOIN on TransactionType between the two table
to get this value. Again, this will allow you to add new TransactionTypes
without any reprogramming of the queries. I find this so very nicely
powerful I would generally always include such features in my design.

 

[Jan Il]

Hi Dan,

HI Jan,
Not sure of your setup but, you should be able to change a default
value to whatever you want. Just make sure you enter the time value in
the format shown below. Or you can go with Tom's suggestion which I'm sure will
work as well

Yes, I did change the date/time default from Now(), and set the Format to
the
General date-time format you have below, which did allow me to change the
date part.

While this did also allow the RunningBalance amount to be calculated, it
still does not allow the balance amount to be listed in proper sequence
according to the check and deposit record entries. I have posted this
information in my reply to Tom along with the same results with the
additional
information he has provided. It is just so close, we'll get it yet.

As I mentioned to Tom, I think it has to do with the empty fields in the
CheckNo list. It is just my novice view based upon the way the
RunningBalance seems to be calculated and posted in comparison
to the CheckNo and Deposit entries are concerned.

If you have any other thoughts or suggestions, I would really be most happy
to hear them.

Thank you for all your help, Dan, it has given me two avenues to try, and I
have learned two ways to accomplish the same goal. That is the great
part about the newsgroup. It is not just a one-size-fits-all place to
learn.
Thanks to all the kind and generous folks who give of their time and
experience to help others, you can learn such a wide variety of methods
and techniques of doing things. So, if that if one does not exactly meet
your needs, you can try another and improve your knowledge and skills.

I do appreciate your time and help very much.

Best regards,
Jan

 

[Dan Artuso]

Hi Jan,
Ahhhh, but if you include the time in your date field then each record becomes unique
and chronological so you no longer need to have anything but CheckDate in the Where statement
as in:

SELECT T.CheckNo, T.CheckDate, T.Transaction, T.CheckAmt,
T.DepositAmt, T.TransactionType, T.Comment,
(SELECT SUM(Nz(DepositAmt, 0) - Nz(CheckAmt, 0))
FROM MyCheckRegister T1
WHERE T1.CheckDate <= T.CheckDate) AS RunningBalance
FROM MyCheckRegister AS T
ORDER BY T.CheckDate;