Looped result

[Guest]

I have a query that I would like to loop. The fields are Transaction date, accountID, Beginning balance, withdrawals, deposits and then a calculated field - Ending Balance. Ending balance is calculated by taking the beginning balance - withdrawals + deposits.

I would like the Ending balance to replace the beginning balance for the next transaction. This query also selects the start and end dates to query. Sorry - I'm a little rusty and have used this in the past, but the 'old' mind is blank. Any help will be welcome.

 

[Tom Ellison]

Dear John(?):

There is no "looping" available. The rows returned in a query cannot
be considered to have been calculated in some specific order. Each
must be calculated on its own.

That is not to say what you want cannot be done.

The "Beginning Balance" of any row can be added up from all the
preceding rows, perhaps limited to a single account. That is, the
Beginning Balance would start over at zero for a new account.

A correlated subquery is ideal for this. However, as I said, each row
must be calculated on its own. Therefore, to have a nice looking
"running balance" column in the query, there must be a unique order to
the rows, based on some set of columns.

Because there can typically be multiple transactions on the same date,
accountID and Transaction date are probably not sufficient together to
make a good running total for "Beginning Balence". What can end up
happening is that all the rows with the same account and date will be
considered simultaneously. That would cause the "Beginning Balance"
to reflect all the transactions prior to that date, but not to advance
during the day. Perhaps there is a transaction number available that
would allow us to order the transactions within a given date, and give
us a unique ordering.

On the other hand, it is common for a bank to actually process the
transactions for a date together and in no particular order.

For example, say I have a balance of $100.00. At 10:00 AM the bank
processes a check I wrote for $200.00. At 3:00 PM they process a
depoist for $300.00. Was I overdrawn? No, because everything is
posted at 6:00 PM, taking into account all transactions made up to
3:30 PM. They don't really track my balance one transaction at a
time, all day long. Rather, all they really care about is my balance
at the end of the day. So, as long as I can find out my balance
during the day, I can avoid being overdrawn if some unexpected debit
occurs.

So, there might be several ways of doing this, depending on how the
bank really runs its accounts.

Write a query that produces all the columns you want to see other than
the Beginning Balance. Order the results as you expect to see them.
I'll add the Beginning Balance column in my reply. Whether it makes a
nice running total or not will depend on whether you can provide a
unique ordering for it. If you can provide a unique ordering, then it
will be important that the running sum follows that same ordering, or
it will look bad indeed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Tom Ellison]

Dear Corrine:

Nice name! Thanks for sharing it with us.

As I tried to tell you, you may need a unique way of sorting to get
just what you want. I suspect that AccountID / TransactionDate won't
be unique. Here's the query changed, so you may be able to see what I
meant:

SELECT AccountID, TransactionDate, [Check #], TransactionDescription,
WithdrawalAmount, DepositAmount
(SELECT Nz(SUM(Nz(DepositAmount, 0), 0) -
Nz(SUM(Nz(WithdrawalAmount, 0), 0)
FROM Transactions T1
WHERE T1.AccountID = T.AccountID
AND T1.TransactionDate < T.TransactionDate)
AS BeginningBalance
FROM Transactions T
Where TransactionDate BETWEEN (start date] and [end date]
ORDER BY AccountID, TransactionDate

Looking this over, I have some concerns:

After a few years, it is going to have to add up a large number of
transactions, and it may get slow. A more complex setup is used,
where a balance is posted after a period has "closed". When a period
has closed no transaction before the end of that period may be added
or modified. You then can calculate the balance up to that date with
assurance it cannot change. Any corrections after that date must be
made with a correcting transaction with a date in the current time
period, that being a date after the end of any closed periods.

If there are several transactions on the same day, they are not
uniquely sorted. The Beginning Balance for all these same-day
transactions will be the same, that being the balance at the end of
the previous day. Your sorting, and my code which is based on that
sorting, is indeterminate within a set of transactions on the same
day. This could be overcome by adding another column to the sort
order (and to my equivalent code) that makes the rows within a given
date so they sort uniquely. Note that, if your TransactionDate
includes a time of day, they may already be unique, and this comment
would probably not apply.

I expect you will need to get back to me in order to work out these
details.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Here is the query so far. I have omitted any reference to ending or beginning balances in hope that you can help me out.

SELECT Transactions.AccountID, Transactions.TransactionDate, Transactions.[Check #], Transactions.TransactionDescription, Transactions.WithdrawalAmount, Transactions.DepositAmount
FROM Transactions
WHERE (((Transactions.TransactionDate) Between [start date] and [end date]))
ORDER BY Transactions.AccountID, Transactions.TransactionDate;

Thanks for all your help, Corrine

Dear John(?):

There is no "looping" available. The rows returned in a query cannot
be considered to have been calculated in some specific order. Each
must be calculated on its own.

That is not to say what you want cannot be done.

The "Beginning Balance" of any row can be added up from all the
preceding rows, perhaps limited to a single account. That is, the
Beginning Balance would start over at zero for a new account.

A correlated subquery is ideal for this. However, as I said, each row
must be calculated on its own. Therefore, to have a nice looking
"running balance" column in the query, there must be a unique order to
the rows, based on some set of columns.

Because there can typically be multiple transactions on the same date,
accountID and Transaction date are probably not sufficient together to
make a good running total for "Beginning Balence". What can end up
happening is that all the rows with the same account and date will be
considered simultaneously. That would cause the "Beginning Balance"
to reflect all the transactions prior to that date, but not to advance
during the day. Perhaps there is a transaction number available that
would allow us to order the transactions within a given date, and give
us a unique ordering.

On the other hand, it is common for a bank to actually process the
transactions for a date together and in no particular order.

For example, say I have a balance of $100.00. At 10:00 AM the bank
processes a check I wrote for $200.00. At 3:00 PM they process a
depoist for $300.00. Was I overdrawn? No, because everything is
posted at 6:00 PM, taking into account all transactions made up to
3:30 PM. They don't really track my balance one transaction at a
time, all day long. Rather, all they really care about is my balance
at the end of the day. So, as long as I can find out my balance
during the day, I can avoid being overdrawn if some unexpected debit
occurs.

So, there might be several ways of doing this, depending on how the
bank really runs its accounts.

Write a query that produces all the columns you want to see other than
the Beginning Balance. Order the results as you expect to see them.
I'll add the Beginning Balance column in my reply. Whether it makes a
nice running total or not will depend on whether you can provide a
unique ordering for it. If you can provide a unique ordering, then it
will be important that the running sum follows that same ordering, or
it will look bad indeed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Guest]

Tom - thank you so much for your help. I used the revised query and got the following results...

Withdrawal Deposit Balance
3.00 0.00 0.00
185.00 0.00 -3.00
20.00 0.00 -188.00

and so on...

Is there anyway that the balance can be shown on the same line as the withdrawal or deposit? The ending balance is really not a true picture of the numbers the way it runs right now.

Again, thank you for all your help, Corrine

Dear Corrine:

Nice name! Thanks for sharing it with us.

As I tried to tell you, you may need a unique way of sorting to get
just what you want. I suspect that AccountID / TransactionDate won't
be unique. Here's the query changed, so you may be able to see what I
meant:

SELECT AccountID, TransactionDate, [Check #], TransactionDescription,
WithdrawalAmount, DepositAmount
(SELECT Nz(SUM(Nz(DepositAmount, 0), 0) -
Nz(SUM(Nz(WithdrawalAmount, 0), 0)
FROM Transactions T1
WHERE T1.AccountID = T.AccountID
AND T1.TransactionDate < T.TransactionDate)
AS BeginningBalance
FROM Transactions T
Where TransactionDate BETWEEN (start date] and [end date]
ORDER BY AccountID, TransactionDate

Looking this over, I have some concerns:

After a few years, it is going to have to add up a large number of
transactions, and it may get slow. A more complex setup is used,
where a balance is posted after a period has "closed". When a period
has closed no transaction before the end of that period may be added
or modified. You then can calculate the balance up to that date with
assurance it cannot change. Any corrections after that date must be
made with a correcting transaction with a date in the current time
period, that being a date after the end of any closed periods.

If there are several transactions on the same day, they are not
uniquely sorted. The Beginning Balance for all these same-day
transactions will be the same, that being the balance at the end of
the previous day. Your sorting, and my code which is based on that
sorting, is indeterminate within a set of transactions on the same
day. This could be overcome by adding another column to the sort
order (and to my equivalent code) that makes the rows within a given
date so they sort uniquely. Note that, if your TransactionDate
includes a time of day, they may already be unique, and this comment
would probably not apply.

I expect you will need to get back to me in order to work out these
details.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Here is the query so far. I have omitted any reference to ending or beginning balances in hope that you can help me out.

SELECT Transactions.AccountID, Transactions.TransactionDate, Transactions.[Check #], Transactions.TransactionDescription, Transactions.WithdrawalAmount, Transactions.DepositAmount
FROM Transactions
WHERE (((Transactions.TransactionDate) Between [start date] and [end date]))
ORDER BY Transactions.AccountID, Transactions.TransactionDate;

Thanks for all your help, Corrine

Dear John(?):

There is no "looping" available. The rows returned in a query cannot
be considered to have been calculated in some specific order. Each
must be calculated on its own.

That is not to say what you want cannot be done.

The "Beginning Balance" of any row can be added up from all the
preceding rows, perhaps limited to a single account. That is, the
Beginning Balance would start over at zero for a new account.

A correlated subquery is ideal for this. However, as I said, each row
must be calculated on its own. Therefore, to have a nice looking
"running balance" column in the query, there must be a unique order to
the rows, based on some set of columns.

Because there can typically be multiple transactions on the same date,
accountID and Transaction date are probably not sufficient together to
make a good running total for "Beginning Balence". What can end up
happening is that all the rows with the same account and date will be
considered simultaneously. That would cause the "Beginning Balance"
to reflect all the transactions prior to that date, but not to advance
during the day. Perhaps there is a transaction number available that
would allow us to order the transactions within a given date, and give
us a unique ordering.

On the other hand, it is common for a bank to actually process the
transactions for a date together and in no particular order.

For example, say I have a balance of $100.00. At 10:00 AM the bank
processes a check I wrote for $200.00. At 3:00 PM they process a
depoist for $300.00. Was I overdrawn? No, because everything is
posted at 6:00 PM, taking into account all transactions made up to
3:30 PM. They don't really track my balance one transaction at a
time, all day long. Rather, all they really care about is my balance
at the end of the day. So, as long as I can find out my balance
during the day, I can avoid being overdrawn if some unexpected debit
occurs.

So, there might be several ways of doing this, depending on how the
bank really runs its accounts.

Write a query that produces all the columns you want to see other than
the Beginning Balance. Order the results as you expect to see them.
I'll add the Beginning Balance column in my reply. Whether it makes a
nice running total or not will depend on whether you can provide a
unique ordering for it. If you can provide a unique ordering, then it
will be important that the running sum follows that same ordering, or
it will look bad indeed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Wed, 21 Jul 2004 14:25:02 -0700, "cojohnso"

I have a query that I would like to loop. The fields are Transaction date, accountID, Beginning balance, withdrawals, deposits and then a calculated field - Ending Balance. Ending balance is calculated by taking the beginning balance - withdrawals + deposits.

I would like the Ending balance to replace the beginning balance for the next transaction. This query also selects the start and end dates to query. Sorry - I'm a little rusty and have used this in the past, but the 'old' mind is blank. Any help will be welcome.

 

[Tom Ellison]

Dear Corrine:

In the line that reads:

AND T1.TransactionDate < T.TransactionDate)

change it to:

AND T1.TransactionDate <= T.TransactionDate)

This will show the balance up to and including transactions for
today's date, not just up to today's date. Make sense? This is the
difference between "Beginning Balance" which I understood was our
starting point, and "Ending Balance". There's no reason you could not
have both.

Still, remember that when you have two transactions on the same date,
it will cause problems. Create this in some test data and see what
happens so you'll be prepared for it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom - thank you so much for your help. I used the revised query and got the following results...

Withdrawal Deposit Balance
3.00 0.00 0.00
185.00 0.00 -3.00
20.00 0.00 -188.00

and so on...

Is there anyway that the balance can be shown on the same line as the withdrawal or deposit? The ending balance is really not a true picture of the numbers the way it runs right now.

Again, thank you for all your help, Corrine

Dear Corrine:

Nice name! Thanks for sharing it with us.

As I tried to tell you, you may need a unique way of sorting to get
just what you want. I suspect that AccountID / TransactionDate won't
be unique. Here's the query changed, so you may be able to see what I
meant:

SELECT AccountID, TransactionDate, [Check #], TransactionDescription,
WithdrawalAmount, DepositAmount
(SELECT Nz(SUM(Nz(DepositAmount, 0), 0) -
Nz(SUM(Nz(WithdrawalAmount, 0), 0)
FROM Transactions T1
WHERE T1.AccountID = T.AccountID
AND T1.TransactionDate < T.TransactionDate)
AS BeginningBalance
FROM Transactions T
Where TransactionDate BETWEEN (start date] and [end date]
ORDER BY AccountID, TransactionDate

Looking this over, I have some concerns:

After a few years, it is going to have to add up a large number of
transactions, and it may get slow. A more complex setup is used,
where a balance is posted after a period has "closed". When a period
has closed no transaction before the end of that period may be added
or modified. You then can calculate the balance up to that date with
assurance it cannot change. Any corrections after that date must be
made with a correcting transaction with a date in the current time
period, that being a date after the end of any closed periods.

If there are several transactions on the same day, they are not
uniquely sorted. The Beginning Balance for all these same-day
transactions will be the same, that being the balance at the end of
the previous day. Your sorting, and my code which is based on that
sorting, is indeterminate within a set of transactions on the same
day. This could be overcome by adding another column to the sort
order (and to my equivalent code) that makes the rows within a given
date so they sort uniquely. Note that, if your TransactionDate
includes a time of day, they may already be unique, and this comment
would probably not apply.

I expect you will need to get back to me in order to work out these
details.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Here is the query so far. I have omitted any reference to ending or beginning balances in hope that you can help me out.

SELECT Transactions.AccountID, Transactions.TransactionDate, Transactions.[Check #], Transactions.TransactionDescription, Transactions.WithdrawalAmount, Transactions.DepositAmount
FROM Transactions
WHERE (((Transactions.TransactionDate) Between [start date] and [end date]))
ORDER BY Transactions.AccountID, Transactions.TransactionDate;

Thanks for all your help, Corrine

:

Dear John(?):

There is no "looping" available. The rows returned in a query cannot
be considered to have been calculated in some specific order. Each
must be calculated on its own.

That is not to say what you want cannot be done.

The "Beginning Balance" of any row can be added up from all the
preceding rows, perhaps limited to a single account. That is, the
Beginning Balance would start over at zero for a new account.

A correlated subquery is ideal for this. However, as I said, each row
must be calculated on its own. Therefore, to have a nice looking
"running balance" column in the query, there must be a unique order to
the rows, based on some set of columns.

Because there can typically be multiple transactions on the same date,
accountID and Transaction date are probably not sufficient together to
make a good running total for "Beginning Balence". What can end up
happening is that all the rows with the same account and date will be
considered simultaneously. That would cause the "Beginning Balance"
to reflect all the transactions prior to that date, but not to advance
during the day. Perhaps there is a transaction number available that
would allow us to order the transactions within a given date, and give
us a unique ordering.

On the other hand, it is common for a bank to actually process the
transactions for a date together and in no particular order.

For example, say I have a balance of $100.00. At 10:00 AM the bank
processes a check I wrote for $200.00. At 3:00 PM they process a
depoist for $300.00. Was I overdrawn? No, because everything is
posted at 6:00 PM, taking into account all transactions made up to
3:30 PM. They don't really track my balance one transaction at a
time, all day long. Rather, all they really care about is my balance
at the end of the day. So, as long as I can find out my balance
during the day, I can avoid being overdrawn if some unexpected debit
occurs.

So, there might be several ways of doing this, depending on how the
bank really runs its accounts.

Write a query that produces all the columns you want to see other than
the Beginning Balance. Order the results as you expect to see them.
I'll add the Beginning Balance column in my reply. Whether it makes a
nice running total or not will depend on whether you can provide a
unique ordering for it. If you can provide a unique ordering, then it
will be important that the running sum follows that same ordering, or
it will look bad indeed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Wed, 21 Jul 2004 14:25:02 -0700, "cojohnso"

I have a query that I would like to loop. The fields are Transaction date, accountID, Beginning balance, withdrawals, deposits and then a calculated field - Ending Balance. Ending balance is calculated by taking the beginning balance - withdrawals + deposits.

I would like the Ending balance to replace the beginning balance for the next transaction. This query also selects the start and end dates to query. Sorry - I'm a little rusty and have used this in the past, but the 'old' mind is blank. Any help will be welcome.

 

[Guest]

WHOOPPPEEE!!! It worked! And to get around the confusion of duplicate day transactions, I set the field to not show duplications (for example, ending balance will not show up twice if the same number is equal).

Thank you, thank you.

Corrine

Dear Corrine:

In the line that reads:

AND T1.TransactionDate < T.TransactionDate)

change it to:

AND T1.TransactionDate <= T.TransactionDate)

This will show the balance up to and including transactions for
today's date, not just up to today's date. Make sense? This is the
difference between "Beginning Balance" which I understood was our
starting point, and "Ending Balance". There's no reason you could not
have both.

Still, remember that when you have two transactions on the same date,
it will cause problems. Create this in some test data and see what
happens so you'll be prepared for it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom - thank you so much for your help. I used the revised query and got the following results...

Withdrawal Deposit Balance
3.00 0.00 0.00
185.00 0.00 -3.00
20.00 0.00 -188.00

and so on...

Is there anyway that the balance can be shown on the same line as the withdrawal or deposit? The ending balance is really not a true picture of the numbers the way it runs right now.

Again, thank you for all your help, Corrine

Dear Corrine:

Nice name! Thanks for sharing it with us.

As I tried to tell you, you may need a unique way of sorting to get
just what you want. I suspect that AccountID / TransactionDate won't
be unique. Here's the query changed, so you may be able to see what I
meant:

SELECT AccountID, TransactionDate, [Check #], TransactionDescription,
WithdrawalAmount, DepositAmount
(SELECT Nz(SUM(Nz(DepositAmount, 0), 0) -
Nz(SUM(Nz(WithdrawalAmount, 0), 0)
FROM Transactions T1
WHERE T1.AccountID = T.AccountID
AND T1.TransactionDate < T.TransactionDate)
AS BeginningBalance
FROM Transactions T
Where TransactionDate BETWEEN (start date] and [end date]
ORDER BY AccountID, TransactionDate

Looking this over, I have some concerns:

After a few years, it is going to have to add up a large number of
transactions, and it may get slow. A more complex setup is used,
where a balance is posted after a period has "closed". When a period
has closed no transaction before the end of that period may be added
or modified. You then can calculate the balance up to that date with
assurance it cannot change. Any corrections after that date must be
made with a correcting transaction with a date in the current time
period, that being a date after the end of any closed periods.

If there are several transactions on the same day, they are not
uniquely sorted. The Beginning Balance for all these same-day
transactions will be the same, that being the balance at the end of
the previous day. Your sorting, and my code which is based on that
sorting, is indeterminate within a set of transactions on the same
day. This could be overcome by adding another column to the sort
order (and to my equivalent code) that makes the rows within a given
date so they sort uniquely. Note that, if your TransactionDate
includes a time of day, they may already be unique, and this comment
would probably not apply.

I expect you will need to get back to me in order to work out these
details.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Thu, 22 Jul 2004 06:51:08 -0700, "cojohnso"

Here is the query so far. I have omitted any reference to ending or beginning balances in hope that you can help me out.

SELECT Transactions.AccountID, Transactions.TransactionDate, Transactions.[Check #], Transactions.TransactionDescription, Transactions.WithdrawalAmount, Transactions.DepositAmount
FROM Transactions
WHERE (((Transactions.TransactionDate) Between [start date] and [end date]))
ORDER BY Transactions.AccountID, Transactions.TransactionDate;

Thanks for all your help, Corrine

:

Dear John(?):

There is no "looping" available. The rows returned in a query cannot
be considered to have been calculated in some specific order. Each
must be calculated on its own.

That is not to say what you want cannot be done.

The "Beginning Balance" of any row can be added up from all the
preceding rows, perhaps limited to a single account. That is, the
Beginning Balance would start over at zero for a new account.

A correlated subquery is ideal for this. However, as I said, each row
must be calculated on its own. Therefore, to have a nice looking
"running balance" column in the query, there must be a unique order to
the rows, based on some set of columns.

Because there can typically be multiple transactions on the same date,
accountID and Transaction date are probably not sufficient together to
make a good running total for "Beginning Balence". What can end up
happening is that all the rows with the same account and date will be
considered simultaneously. That would cause the "Beginning Balance"
to reflect all the transactions prior to that date, but not to advance
during the day. Perhaps there is a transaction number available that
would allow us to order the transactions within a given date, and give
us a unique ordering.

On the other hand, it is common for a bank to actually process the
transactions for a date together and in no particular order.

For example, say I have a balance of $100.00. At 10:00 AM the bank
processes a check I wrote for $200.00. At 3:00 PM they process a
depoist for $300.00. Was I overdrawn? No, because everything is
posted at 6:00 PM, taking into account all transactions made up to
3:30 PM. They don't really track my balance one transaction at a
time, all day long. Rather, all they really care about is my balance
at the end of the day. So, as long as I can find out my balance
during the day, I can avoid being overdrawn if some unexpected debit
occurs.

So, there might be several ways of doing this, depending on how the
bank really runs its accounts.

Write a query that produces all the columns you want to see other than
the Beginning Balance. Order the results as you expect to see them.
I'll add the Beginning Balance column in my reply. Whether it makes a
nice running total or not will depend on whether you can provide a
unique ordering for it. If you can provide a unique ordering, then it
will be important that the running sum follows that same ordering, or
it will look bad indeed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Wed, 21 Jul 2004 14:25:02 -0700, "cojohnso"

I have a query that I would like to loop. The fields are Transaction date, accountID, Beginning balance, withdrawals, deposits and then a calculated field - Ending Balance. Ending balance is calculated by taking the beginning balance - withdrawals + deposits.

I would like the Ending balance to replace the beginning balance for the next transaction. This query also selects the start and end dates to query. Sorry - I'm a little rusty and have used this in the past, but the 'old' mind is blank. Any help will be welcome.

 

[Tom Ellison]

Dear Corrine:

Since the balance is actually the ending balance of the previous day
or of the current day (depending on the change I showed you in a
previous post) showing the Beginning Balance only once per day would
sure make sense. It would seem a bit more difficult to show the
Ending Balance only at the end of the day, however.

If you have some value, such as TransactionNumber on which the rows
could be uniquely ordered within a date, you could overcome this.

Another problem would be if you had two transacitons in a day, a check
for $100 and a deposit for $100. Then it wuold have the same balance
as the day before. You may want to show the balance whenever the date
changes, rather than when the balance changes.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

WHOOPPPEEE!!! It worked! And to get around the confusion of duplicate day transactions, I set the field to not show duplications (for example, ending balance will not show up twice if the same number is equal).

Thank you, thank you.

Corrine

Dear Corrine:

In the line that reads:

AND T1.TransactionDate < T.TransactionDate)

change it to:

AND T1.TransactionDate <= T.TransactionDate)

This will show the balance up to and including transactions for
today's date, not just up to today's date. Make sense? This is the
difference between "Beginning Balance" which I understood was our
starting point, and "Ending Balance". There's no reason you could not
have both.

Still, remember that when you have two transactions on the same date,
it will cause problems. Create this in some test data and see what
happens so you'll be prepared for it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom - thank you so much for your help. I used the revised query and got the following results...

Withdrawal Deposit Balance
3.00 0.00 0.00
185.00 0.00 -3.00
20.00 0.00 -188.00

and so on...

Is there anyway that the balance can be shown on the same line as the withdrawal or deposit? The ending balance is really not a true picture of the numbers the way it runs right now.

Again, thank you for all your help, Corrine


:

Dear Corrine:

Nice name! Thanks for sharing it with us.

As I tried to tell you, you may need a unique way of sorting to get
just what you want. I suspect that AccountID / TransactionDate won't
be unique. Here's the query changed, so you may be able to see what I
meant:

SELECT AccountID, TransactionDate, [Check #], TransactionDescription,
WithdrawalAmount, DepositAmount
(SELECT Nz(SUM(Nz(DepositAmount, 0), 0) -
Nz(SUM(Nz(WithdrawalAmount, 0), 0)
FROM Transactions T1
WHERE T1.AccountID = T.AccountID
AND T1.TransactionDate < T.TransactionDate)
AS BeginningBalance
FROM Transactions T
Where TransactionDate BETWEEN (start date] and [end date]
ORDER BY AccountID, TransactionDate

Looking this over, I have some concerns:

After a few years, it is going to have to add up a large number of
transactions, and it may get slow. A more complex setup is used,
where a balance is posted after a period has "closed". When a period
has closed no transaction before the end of that period may be added
or modified. You then can calculate the balance up to that date with
assurance it cannot change. Any corrections after that date must be
made with a correcting transaction with a date in the current time
period, that being a date after the end of any closed periods.

If there are several transactions on the same day, they are not
uniquely sorted. The Beginning Balance for all these same-day
transactions will be the same, that being the balance at the end of
the previous day. Your sorting, and my code which is based on that
sorting, is indeterminate within a set of transactions on the same
day. This could be overcome by adding another column to the sort
order (and to my equivalent code) that makes the rows within a given
date so they sort uniquely. Note that, if your TransactionDate
includes a time of day, they may already be unique, and this comment
would probably not apply.

I expect you will need to get back to me in order to work out these
details.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Thu, 22 Jul 2004 06:51:08 -0700, "cojohnso"

Here is the query so far. I have omitted any reference to ending or beginning balances in hope that you can help me out.

SELECT Transactions.AccountID, Transactions.TransactionDate, Transactions.[Check #], Transactions.TransactionDescription, Transactions.WithdrawalAmount, Transactions.DepositAmount
FROM Transactions
WHERE (((Transactions.TransactionDate) Between [start date] and [end date]))
ORDER BY Transactions.AccountID, Transactions.TransactionDate;

Thanks for all your help, Corrine

:

Dear John(?):

There is no "looping" available. The rows returned in a query cannot
be considered to have been calculated in some specific order. Each
must be calculated on its own.

That is not to say what you want cannot be done.

The "Beginning Balance" of any row can be added up from all the
preceding rows, perhaps limited to a single account. That is, the
Beginning Balance would start over at zero for a new account.

A correlated subquery is ideal for this. However, as I said, each row
must be calculated on its own. Therefore, to have a nice looking
"running balance" column in the query, there must be a unique order to
the rows, based on some set of columns.

Because there can typically be multiple transactions on the same date,
accountID and Transaction date are probably not sufficient together to
make a good running total for "Beginning Balence". What can end up
happening is that all the rows with the same account and date will be
considered simultaneously. That would cause the "Beginning Balance"
to reflect all the transactions prior to that date, but not to advance
during the day. Perhaps there is a transaction number available that
would allow us to order the transactions within a given date, and give
us a unique ordering.

On the other hand, it is common for a bank to actually process the
transactions for a date together and in no particular order.

For example, say I have a balance of $100.00. At 10:00 AM the bank
processes a check I wrote for $200.00. At 3:00 PM they process a
depoist for $300.00. Was I overdrawn? No, because everything is
posted at 6:00 PM, taking into account all transactions made up to
3:30 PM. They don't really track my balance one transaction at a
time, all day long. Rather, all they really care about is my balance
at the end of the day. So, as long as I can find out my balance
during the day, I can avoid being overdrawn if some unexpected debit
occurs.

So, there might be several ways of doing this, depending on how the
bank really runs its accounts.

Write a query that produces all the columns you want to see other than
the Beginning Balance. Order the results as you expect to see them.
I'll add the Beginning Balance column in my reply. Whether it makes a
nice running total or not will depend on whether you can provide a
unique ordering for it. If you can provide a unique ordering, then it
will be important that the running sum follows that same ordering, or
it will look bad indeed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Wed, 21 Jul 2004 14:25:02 -0700, "cojohnso"

I have a query that I would like to loop. The fields are Transaction date, accountID, Beginning balance, withdrawals, deposits and then a calculated field - Ending Balance. Ending balance is calculated by taking the beginning balance - withdrawals + deposits.

I would like the Ending balance to replace the beginning balance for the next transaction. This query also selects the start and end dates to query. Sorry - I'm a little rusty and have used this in the past, but the 'old' mind is blank. Any help will be welcome.