running balance

[Christina]

I have a check book with deposits and checks written on the account. I would
like to run a query and show a running balance next to each entry. Please
help.

 

[Michel Walsh]

Assuming there is a single transaction, per second, per account.

Assuming there is a single field, amount, with + and - for credit and
debit: +2000 and -342.44 as example.


SELECT a.account,
a.dateTime,
LAST(a.amount) AS transactionAmount,
SUM(b.amount) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.account = b.account
AND a.dateTime >= b.dateTime
GROUP BY a.account, a.dateTime



should do the job. I assumed the fields name were account, dateTime (for the
date and time of the transaction) and amount.


Vanderghast, Access MVP

 

[Christina]

My field names are Check Amount and Deposit Amount. Where would I put the
info you gave me. I dont know SQL etc, hust how to work in the basic design
view,

Would you walk me through. Thanks

 

[Michel Walsh]

You should edit a new query, in SQL view, and type the code. Missing the
tableName you have to supply (two places). Also assuming that [Check Amount]
is a debit and [Deposit Amount] a credit (still not sure of your field
accountNumber and dateTime)


SELECT a.accountNumber,
a.dateTime,
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.accountNumber= b.accountNumber
AND a.dateTime >= b.dateTime
GROUP BY a.accountNumber, a.dateTime



Vanderghast, Access MVP

 

[Christina]

This is a very simple account. For date it is set to edium date, It is only
one account so I don't have that as a field. I have fields named check
number, check amount, deposit amount, date of transaction, Name, Status (for
cashed or uncashed).
When I create the new query in design view and bring the check book table,
please tell me exactly what I need to do after I bring down the fields.
Thanks.

You should edit a new query, in SQL view, and type the code. Missing the
tableName you have to supply (two places). Also assuming that [Check Amount]
is a debit and [Deposit Amount] a credit (still not sure of your field
accountNumber and dateTime)


SELECT a.accountNumber,
a.dateTime,
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.accountNumber= b.accountNumber
AND a.dateTime >= b.dateTime
GROUP BY a.accountNumber, a.dateTime



Vanderghast, Access MVP

My field names are Check Amount and Deposit Amount. Where would I put the
info you gave me. I dont know SQL etc, hust how to work in the basic
design
view,

Would you walk me through. Thanks

 

[Christina]

Do I need to have both deposits and checks in the same column, and show as
negative and positive? I'd rather not if If I don't have to.

This is a very simple account. For date it is set to edium date, It is only
one account so I don't have that as a field. I have fields named check
number, check amount, deposit amount, date of transaction, Name, Status (for
cashed or uncashed).
When I create the new query in design view and bring the check book table,
please tell me exactly what I need to do after I bring down the fields.
Thanks.

You should edit a new query, in SQL view, and type the code. Missing the
tableName you have to supply (two places). Also assuming that [Check Amount]
is a debit and [Deposit Amount] a credit (still not sure of your field
accountNumber and dateTime)


SELECT a.accountNumber,
a.dateTime,
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.accountNumber= b.accountNumber
AND a.dateTime >= b.dateTime
GROUP BY a.accountNumber, a.dateTime



Vanderghast, Access MVP

My field names are Check Amount and Deposit Amount. Where would I put the
info you gave me. I dont know SQL etc, hust how to work in the basic
design
view,

Would you walk me through. Thanks

:

Assuming there is a single transaction, per second, per account.

Assuming there is a single field, amount, with + and - for credit and
debit: +2000 and -342.44 as example.


SELECT a.account,
a.dateTime,
LAST(a.amount) AS transactionAmount,
SUM(b.amount) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.account = b.account
AND a.dateTime >= b.dateTime
GROUP BY a.account, a.dateTime



should do the job. I assumed the fields name were account, dateTime (for
the
date and time of the transaction) and amount.


Vanderghast, Access MVP





I have a check book with deposits and checks written on the account. I
would
like to run a query and show a running balance next to each entry.
Please
help.

 

[Michel Walsh]

You don't have to have only one column, no (although since we now use
handheld computer, or computer, there is no real need to have two columns,
since hand held computer can as easily add than subtract ).



SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running

FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]


in a new query, in SQL view (still have to change the table name at two
places, use [ ] around the name if the name has spaces in it.

You can also use a similar version, which will allow you to switch back in
design view:


SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running

FROM tableNameHere AS a , tableNameHere AS b

WHERE a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]



and indeed, switching back in design view, you could see what you could have
done, graphically, to produce that query (rather than doing it in SQL view).





Vanderghast, Access MVP

Do I need to have both deposits and checks in the same column, and show as
negative and positive? I'd rather not if If I don't have to.

This is a very simple account. For date it is set to edium date, It is
only
one account so I don't have that as a field. I have fields named check
number, check amount, deposit amount, date of transaction, Name, Status
(for
cashed or uncashed).
When I create the new query in design view and bring the check book
table,
please tell me exactly what I need to do after I bring down the fields.
Thanks.

You should edit a new query, in SQL view, and type the code. Missing
the
tableName you have to supply (two places). Also assuming that [Check
Amount]
is a debit and [Deposit Amount] a credit (still not sure of your field
accountNumber and dateTime)


SELECT a.accountNumber,
a.dateTime,
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.accountNumber= b.accountNumber
AND a.dateTime >= b.dateTime
GROUP BY a.accountNumber, a.dateTime



Vanderghast, Access MVP



My field names are Check Amount and Deposit Amount. Where would I
put the
info you gave me. I dont know SQL etc, hust how to work in the basic
design
view,

Would you walk me through. Thanks

:

Assuming there is a single transaction, per second, per account.

Assuming there is a single field, amount, with + and - for credit
and
debit: +2000 and -342.44 as example.


SELECT a.account,
a.dateTime,
LAST(a.amount) AS transactionAmount,
SUM(b.amount) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.account = b.account
AND a.dateTime >= b.dateTime
GROUP BY a.account, a.dateTime



should do the job. I assumed the fields name were account, dateTime
(for
the
date and time of the transaction) and amount.


Vanderghast, Access MVP





I have a check book with deposits and checks written on the
account. I
would
like to run a query and show a running balance next to each entry.
Please
help.

 

[Christina]

Ok, this seems like it will work,as it runs, but does not give any results.
I don't have an account number field, and the date is set to med date.
Thanks for your help.

You don't have to have only one column, no (although since we now use
handheld computer, or computer, there is no real need to have two columns,
since hand held computer can as easily add than subtract ).



SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running

FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]


in a new query, in SQL view (still have to change the table name at two
places, use [ ] around the name if the name has spaces in it.

You can also use a similar version, which will allow you to switch back in
design view:


SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running

FROM tableNameHere AS a , tableNameHere AS b

WHERE a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]



and indeed, switching back in design view, you could see what you could have
done, graphically, to produce that query (rather than doing it in SQL view).





Vanderghast, Access MVP

Do I need to have both deposits and checks in the same column, and show as
negative and positive? I'd rather not if If I don't have to.

This is a very simple account. For date it is set to edium date, It is
only
one account so I don't have that as a field. I have fields named check
number, check amount, deposit amount, date of transaction, Name, Status
(for
cashed or uncashed).
When I create the new query in design view and bring the check book
table,
please tell me exactly what I need to do after I bring down the fields.
Thanks.

:

You should edit a new query, in SQL view, and type the code. Missing
the
tableName you have to supply (two places). Also assuming that [Check
Amount]
is a debit and [Deposit Amount] a credit (still not sure of your field
accountNumber and dateTime)


SELECT a.accountNumber,
a.dateTime,
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.accountNumber= b.accountNumber
AND a.dateTime >= b.dateTime
GROUP BY a.accountNumber, a.dateTime



Vanderghast, Access MVP



My field names are Check Amount and Deposit Amount. Where would I
put the
info you gave me. I dont know SQL etc, hust how to work in the basic
design
view,

Would you walk me through. Thanks

:

Assuming there is a single transaction, per second, per account.

Assuming there is a single field, amount, with + and - for credit
and
debit: +2000 and -342.44 as example.


SELECT a.account,
a.dateTime,
LAST(a.amount) AS transactionAmount,
SUM(b.amount) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.account = b.account
AND a.dateTime >= b.dateTime
GROUP BY a.account, a.dateTime



should do the job. I assumed the fields name were account, dateTime
(for
the
date and time of the transaction) and amount.


Vanderghast, Access MVP





I have a check book with deposits and checks written on the
account. I
would
like to run a query and show a running balance next to each entry.
Please
help.

 

[Christina]

Thanks so much , I got it to work. Is there anyway, I can include the Check
number and name in the query.

You don't have to have only one column, no (although since we now use
handheld computer, or computer, there is no real need to have two columns,
since hand held computer can as easily add than subtract ).



SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running

FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]


in a new query, in SQL view (still have to change the table name at two
places, use [ ] around the name if the name has spaces in it.

You can also use a similar version, which will allow you to switch back in
design view:


SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running

FROM tableNameHere AS a , tableNameHere AS b

WHERE a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]



and indeed, switching back in design view, you could see what you could have
done, graphically, to produce that query (rather than doing it in SQL view).





Vanderghast, Access MVP

Do I need to have both deposits and checks in the same column, and show as
negative and positive? I'd rather not if If I don't have to.

This is a very simple account. For date it is set to edium date, It is
only
one account so I don't have that as a field. I have fields named check
number, check amount, deposit amount, date of transaction, Name, Status
(for
cashed or uncashed).
When I create the new query in design view and bring the check book
table,
please tell me exactly what I need to do after I bring down the fields.
Thanks.

:

You should edit a new query, in SQL view, and type the code. Missing
the
tableName you have to supply (two places). Also assuming that [Check
Amount]
is a debit and [Deposit Amount] a credit (still not sure of your field
accountNumber and dateTime)


SELECT a.accountNumber,
a.dateTime,
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.accountNumber= b.accountNumber
AND a.dateTime >= b.dateTime
GROUP BY a.accountNumber, a.dateTime



Vanderghast, Access MVP



My field names are Check Amount and Deposit Amount. Where would I
put the
info you gave me. I dont know SQL etc, hust how to work in the basic
design
view,

Would you walk me through. Thanks

:

Assuming there is a single transaction, per second, per account.

Assuming there is a single field, amount, with + and - for credit
and
debit: +2000 and -342.44 as example.


SELECT a.account,
a.dateTime,
LAST(a.amount) AS transactionAmount,
SUM(b.amount) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.account = b.account
AND a.dateTime >= b.dateTime
GROUP BY a.account, a.dateTime



should do the job. I assumed the fields name were account, dateTime
(for
the
date and time of the transaction) and amount.


Vanderghast, Access MVP





I have a check book with deposits and checks written on the
account. I
would
like to run a query and show a running balance next to each entry.
Please
help.

 

[Michel Walsh]

Sure:

SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
LAST(a.[Check Number]) AS checkNumber,
LAST(a.[Check Name]) AS purpose,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running
FROM
...



Vanderghast, Access MVP

Thanks so much , I got it to work. Is there anyway, I can include the
Check
number and name in the query.

You don't have to have only one column, no (although since we now use
handheld computer, or computer, there is no real need to have two
columns,
since hand held computer can as easily add than subtract ).



SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running

FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]


in a new query, in SQL view (still have to change the table name at two
places, use [ ] around the name if the name has spaces in it.

You can also use a similar version, which will allow you to switch back
in
design view:


SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running

FROM tableNameHere AS a , tableNameHere AS b

WHERE a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]



and indeed, switching back in design view, you could see what you could
have
done, graphically, to produce that query (rather than doing it in SQL
view).





Vanderghast, Access MVP

Do I need to have both deposits and checks in the same column, and show
as
negative and positive? I'd rather not if If I don't have to.

:

This is a very simple account. For date it is set to edium date, It
is
only
one account so I don't have that as a field. I have fields named
check
number, check amount, deposit amount, date of transaction, Name,
Status
(for
cashed or uncashed).
When I create the new query in design view and bring the check book
table,
please tell me exactly what I need to do after I bring down the
fields.
Thanks.

:

You should edit a new query, in SQL view, and type the code. Missing
the
tableName you have to supply (two places). Also assuming that [Check
Amount]
is a debit and [Deposit Amount] a credit (still not sure of your
field
accountNumber and dateTime)


SELECT a.accountNumber,
a.dateTime,
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.accountNumber= b.accountNumber
AND a.dateTime >= b.dateTime
GROUP BY a.accountNumber, a.dateTime



Vanderghast, Access MVP



My field names are Check Amount and Deposit Amount. Where would I
put the
info you gave me. I dont know SQL etc, hust how to work in the
basic
design
view,

Would you walk me through. Thanks

:

Assuming there is a single transaction, per second, per account.

Assuming there is a single field, amount, with + and - for
credit
and
debit: +2000 and -342.44 as example.


SELECT a.account,
a.dateTime,
LAST(a.amount) AS transactionAmount,
SUM(b.amount) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.account = b.account
AND a.dateTime >= b.dateTime
GROUP BY a.account, a.dateTime



should do the job. I assumed the fields name were account,
dateTime
(for
the
date and time of the transaction) and amount.


Vanderghast, Access MVP





message
I have a check book with deposits and checks written on the
account. I
would
like to run a query and show a running balance next to each
entry.
Please
help.

 

[Christina]

I am sorry. Is there something missing, I tired to follow from the last one
but it did not work. Can you please fill in the rest. Thanks

Sure:

SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
LAST(a.[Check Number]) AS checkNumber,
LAST(a.[Check Name]) AS purpose,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running
FROM
...



Vanderghast, Access MVP

Thanks so much , I got it to work. Is there anyway, I can include the
Check
number and name in the query.

You don't have to have only one column, no (although since we now use
handheld computer, or computer, there is no real need to have two
columns,
since hand held computer can as easily add than subtract ).



SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running

FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]


in a new query, in SQL view (still have to change the table name at two
places, use [ ] around the name if the name has spaces in it.

You can also use a similar version, which will allow you to switch back
in
design view:


SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running

FROM tableNameHere AS a , tableNameHere AS b

WHERE a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]



and indeed, switching back in design view, you could see what you could
have
done, graphically, to produce that query (rather than doing it in SQL
view).





Vanderghast, Access MVP



Do I need to have both deposits and checks in the same column, and show
as
negative and positive? I'd rather not if If I don't have to.

:

This is a very simple account. For date it is set to edium date, It
is
only
one account so I don't have that as a field. I have fields named
check
number, check amount, deposit amount, date of transaction, Name,
Status
(for
cashed or uncashed).
When I create the new query in design view and bring the check book
table,
please tell me exactly what I need to do after I bring down the
fields.
Thanks.

:

You should edit a new query, in SQL view, and type the code. Missing
the
tableName you have to supply (two places). Also assuming that [Check
Amount]
is a debit and [Deposit Amount] a credit (still not sure of your
field
accountNumber and dateTime)


SELECT a.accountNumber,
a.dateTime,
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.accountNumber= b.accountNumber
AND a.dateTime >= b.dateTime
GROUP BY a.accountNumber, a.dateTime



Vanderghast, Access MVP



My field names are Check Amount and Deposit Amount. Where would I
put the
info you gave me. I dont know SQL etc, hust how to work in the
basic
design
view,

Would you walk me through. Thanks

:

Assuming there is a single transaction, per second, per account.

Assuming there is a single field, amount, with + and - for
credit
and
debit: +2000 and -342.44 as example.


SELECT a.account,
a.dateTime,
LAST(a.amount) AS transactionAmount,
SUM(b.amount) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.account = b.account
AND a.dateTime >= b.dateTime
GROUP BY a.account, a.dateTime



should do the job. I assumed the fields name were account,
dateTime
(for
the
date and time of the transaction) and amount.


Vanderghast, Access MVP





message
I have a check book with deposits and checks written on the
account. I
would
like to run a query and show a running balance next to each
entry.
Please
help.

 

[Christina]

Hi, I am just noticing that the ending balance is correct but there is an
error on the first line of the query.
dateTime debit credit running
01-Jan-08 $0.00 $40,000.00 $79,800.00
03-Jan-08 $200.00 0.00 $39,700.00
04-Jan-08 $100.00 $0.00 $39,600.00
05-Jan-08 $100.00 $0.00 $39,500.00
17-Jan-08 $60.00 $0.00 $39,440.00
29-Jan-08 $500.00 $0.00 $38,940.00
02-Feb-08 $30.00 $0.00 $38,910.00

This is my code:
SELECT a.[date of transaction],

LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running

FROM tableNameHere AS a , tableNameHere AS b

WHERE a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]

Thanks

Sure:

SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
LAST(a.[Check Number]) AS checkNumber,
LAST(a.[Check Name]) AS purpose,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running
FROM
...



Vanderghast, Access MVP

Thanks so much , I got it to work. Is there anyway, I can include the
Check
number and name in the query.

You don't have to have only one column, no (although since we now use
handheld computer, or computer, there is no real need to have two
columns,
since hand held computer can as easily add than subtract ).



SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running

FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]


in a new query, in SQL view (still have to change the table name at two
places, use [ ] around the name if the name has spaces in it.

You can also use a similar version, which will allow you to switch back
in
design view:


SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running

FROM tableNameHere AS a , tableNameHere AS b

WHERE a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]



and indeed, switching back in design view, you could see what you could
have
done, graphically, to produce that query (rather than doing it in SQL
view).





Vanderghast, Access MVP



Do I need to have both deposits and checks in the same column, and show
as
negative and positive? I'd rather not if If I don't have to.

:

This is a very simple account. For date it is set to edium date, It
is
only
one account so I don't have that as a field. I have fields named
check
number, check amount, deposit amount, date of transaction, Name,
Status
(for
cashed or uncashed).
When I create the new query in design view and bring the check book
table,
please tell me exactly what I need to do after I bring down the
fields.
Thanks.

:

You should edit a new query, in SQL view, and type the code. Missing
the
tableName you have to supply (two places). Also assuming that [Check
Amount]
is a debit and [Deposit Amount] a credit (still not sure of your
field
accountNumber and dateTime)


SELECT a.accountNumber,
a.dateTime,
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.accountNumber= b.accountNumber
AND a.dateTime >= b.dateTime
GROUP BY a.accountNumber, a.dateTime



Vanderghast, Access MVP



My field names are Check Amount and Deposit Amount. Where would I
put the
info you gave me. I dont know SQL etc, hust how to work in the
basic
design
view,

Would you walk me through. Thanks

:

Assuming there is a single transaction, per second, per account.

Assuming there is a single field, amount, with + and - for
credit
and
debit: +2000 and -342.44 as example.


SELECT a.account,
a.dateTime,
LAST(a.amount) AS transactionAmount,
SUM(b.amount) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.account = b.account
AND a.dateTime >= b.dateTime
GROUP BY a.account, a.dateTime



should do the job. I assumed the fields name were account,
dateTime
(for
the
date and time of the transaction) and amount.


Vanderghast, Access MVP





message
I have a check book with deposits and checks written on the
account. I
would
like to run a query and show a running balance next to each
entry.
Please
help.

 

[Michel Walsh]

SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
LAST(a.[Check Number]) AS checkNumber,
LAST(a.[Check Name]) AS purpose,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running

FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]


should be the whole statement.

I am sorry. Is there something missing, I tired to follow from the last
one
but it did not work. Can you please fill in the rest. Thanks

Sure:

SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
LAST(a.[Check Number]) AS checkNumber,
LAST(a.[Check Name]) AS purpose,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running
FROM
...



Vanderghast, Access MVP

Thanks so much , I got it to work. Is there anyway, I can include the
Check
number and name in the query.

:

You don't have to have only one column, no (although since we now use
handheld computer, or computer, there is no real need to have two
columns,
since hand held computer can as easily add than subtract ).



SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running

FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]


in a new query, in SQL view (still have to change the table name at
two
places, use [ ] around the name if the name has spaces in it.

You can also use a similar version, which will allow you to switch
back
in
design view:


SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running

FROM tableNameHere AS a , tableNameHere AS b

WHERE a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]



and indeed, switching back in design view, you could see what you
could
have
done, graphically, to produce that query (rather than doing it in SQL
view).





Vanderghast, Access MVP



Do I need to have both deposits and checks in the same column, and
show
as
negative and positive? I'd rather not if If I don't have to.

:

This is a very simple account. For date it is set to edium date,
It
is
only
one account so I don't have that as a field. I have fields named
check
number, check amount, deposit amount, date of transaction, Name,
Status
(for
cashed or uncashed).
When I create the new query in design view and bring the check book
table,
please tell me exactly what I need to do after I bring down the
fields.
Thanks.

:

You should edit a new query, in SQL view, and type the code.
Missing
the
tableName you have to supply (two places). Also assuming that
[Check
Amount]
is a debit and [Deposit Amount] a credit (still not sure of your
field
accountNumber and dateTime)


SELECT a.accountNumber,
a.dateTime,
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.accountNumber= b.accountNumber
AND a.dateTime >= b.dateTime
GROUP BY a.accountNumber, a.dateTime



Vanderghast, Access MVP



message
My field names are Check Amount and Deposit Amount. Where
would I
put the
info you gave me. I dont know SQL etc, hust how to work in the
basic
design
view,

Would you walk me through. Thanks

:

Assuming there is a single transaction, per second, per
account.

Assuming there is a single field, amount, with + and - for
credit
and
debit: +2000 and -342.44 as example.


SELECT a.account,
a.dateTime,
LAST(a.amount) AS transactionAmount,
SUM(b.amount) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.account = b.account
AND a.dateTime >= b.dateTime
GROUP BY a.account, a.dateTime



should do the job. I assumed the fields name were account,
dateTime
(for
the
date and time of the transaction) and amount.


Vanderghast, Access MVP





message
I have a check book with deposits and checks written on the
account. I
would
like to run a query and show a running balance next to each
entry.
Please
help.

 

[Michel Walsh]

You probably have MORE THAN ONE RECORD with the date of January First 2008.
If that reflects 2007 year 'closure', try to replace all those records with
just ONE record dated January the first (with an amount in the debit and an
amount in the credit, if applicable). You can also use a date AND time to
make record unique (say, one record at 00:00:00, one record at 00:00:01, and
so on) if making just ONE record is somehow impossible to achieve. Indeed,
if there is many record with the same date (and time), then the ORDER of the
running sum is not well defined (and the implicit join is likely to wrongly
(for our goal) amplify the data).


If you have just one record for January the First 2008, and no record before
that date, then, I am lost. Can you post part of the initial data you have
in your initial table, here?




Vanderghast, Access MVP

Hi, I am just noticing that the ending balance is correct but there is an
error on the first line of the query.
dateTime debit credit running
01-Jan-08 $0.00 $40,000.00 $79,800.00
03-Jan-08 $200.00 0.00 $39,700.00
04-Jan-08 $100.00 $0.00 $39,600.00
05-Jan-08 $100.00 $0.00 $39,500.00
17-Jan-08 $60.00 $0.00 $39,440.00
29-Jan-08 $500.00 $0.00 $38,940.00
02-Feb-08 $30.00 $0.00 $38,910.00

This is my code:
SELECT a.[date of transaction],

LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running

FROM tableNameHere AS a , tableNameHere AS b

WHERE a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]

Thanks

Sure:

SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
LAST(a.[Check Number]) AS checkNumber,
LAST(a.[Check Name]) AS purpose,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running
FROM
...



Vanderghast, Access MVP

Thanks so much , I got it to work. Is there anyway, I can include the
Check
number and name in the query.

:

You don't have to have only one column, no (although since we now use
handheld computer, or computer, there is no real need to have two
columns,
since hand held computer can as easily add than subtract ).



SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running

FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]


in a new query, in SQL view (still have to change the table name at
two
places, use [ ] around the name if the name has spaces in it.

You can also use a similar version, which will allow you to switch
back
in
design view:


SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running

FROM tableNameHere AS a , tableNameHere AS b

WHERE a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]



and indeed, switching back in design view, you could see what you
could
have
done, graphically, to produce that query (rather than doing it in SQL
view).





Vanderghast, Access MVP



Do I need to have both deposits and checks in the same column, and
show
as
negative and positive? I'd rather not if If I don't have to.

:

This is a very simple account. For date it is set to edium date,
It
is
only
one account so I don't have that as a field. I have fields named
check
number, check amount, deposit amount, date of transaction, Name,
Status
(for
cashed or uncashed).
When I create the new query in design view and bring the check book
table,
please tell me exactly what I need to do after I bring down the
fields.
Thanks.

:

You should edit a new query, in SQL view, and type the code.
Missing
the
tableName you have to supply (two places). Also assuming that
[Check
Amount]
is a debit and [Deposit Amount] a credit (still not sure of your
field
accountNumber and dateTime)


SELECT a.accountNumber,
a.dateTime,
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.accountNumber= b.accountNumber
AND a.dateTime >= b.dateTime
GROUP BY a.accountNumber, a.dateTime



Vanderghast, Access MVP



message
My field names are Check Amount and Deposit Amount. Where
would I
put the
info you gave me. I dont know SQL etc, hust how to work in the
basic
design
view,

Would you walk me through. Thanks

:

Assuming there is a single transaction, per second, per
account.

Assuming there is a single field, amount, with + and - for
credit
and
debit: +2000 and -342.44 as example.


SELECT a.account,
a.dateTime,
LAST(a.amount) AS transactionAmount,
SUM(b.amount) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.account = b.account
AND a.dateTime >= b.dateTime
GROUP BY a.account, a.dateTime



should do the job. I assumed the fields name were account,
dateTime
(for
the
date and time of the transaction) and amount.


Vanderghast, Access MVP





message
I have a check book with deposits and checks written on the
account. I
would
like to run a query and show a running balance next to each
entry.
Please
help.

 

[Michel Walsh]

or, if you want to keep the graphical editor:



SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
LAST(a.[Check Number]) AS checkNumber,
LAST(a.[Check Name]) AS purpose,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running

FROM tableNameHere AS a , tableNameHere AS b

WHERE a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]




Vanderghast, Access MVP

 

[Christina]

Ok, when I change the data so that all have a unique date, it works fine. I
then tried it with more than one records with the same date ,using the
date/time format to "general date". The query however then dropped one of
the records. How should I format the date, or do I actually have to type in
the time, and please give me am example how I would type it in. Also the
code to show up the name and check numbers, would you please give me the
whole code, as I think you left out the last part, and I tried to follow the
previous example, but it did ot work.

Thanks so much for your help.

You probably have MORE THAN ONE RECORD with the date of January First 2008.
If that reflects 2007 year 'closure', try to replace all those records with
just ONE record dated January the first (with an amount in the debit and an
amount in the credit, if applicable). You can also use a date AND time to
make record unique (say, one record at 00:00:00, one record at 00:00:01, and
so on) if making just ONE record is somehow impossible to achieve. Indeed,
if there is many record with the same date (and time), then the ORDER of the
running sum is not well defined (and the implicit join is likely to wrongly
(for our goal) amplify the data).


If you have just one record for January the First 2008, and no record before
that date, then, I am lost. Can you post part of the initial data you have
in your initial table, here?




Vanderghast, Access MVP

Hi, I am just noticing that the ending balance is correct but there is an
error on the first line of the query.
dateTime debit credit running
01-Jan-08 $0.00 $40,000.00 $79,800.00
03-Jan-08 $200.00 0.00 $39,700.00
04-Jan-08 $100.00 $0.00 $39,600.00
05-Jan-08 $100.00 $0.00 $39,500.00
17-Jan-08 $60.00 $0.00 $39,440.00
29-Jan-08 $500.00 $0.00 $38,940.00
02-Feb-08 $30.00 $0.00 $38,910.00

This is my code:
SELECT a.[date of transaction],

LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running

FROM tableNameHere AS a , tableNameHere AS b

WHERE a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]

Thanks

Sure:

SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
LAST(a.[Check Number]) AS checkNumber,
LAST(a.[Check Name]) AS purpose,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running
FROM
...



Vanderghast, Access MVP




Thanks so much , I got it to work. Is there anyway, I can include the
Check
number and name in the query.

:

You don't have to have only one column, no (although since we now use
handheld computer, or computer, there is no real need to have two
columns,
since hand held computer can as easily add than subtract ).



SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running

FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]


in a new query, in SQL view (still have to change the table name at
two
places, use [ ] around the name if the name has spaces in it.

You can also use a similar version, which will allow you to switch
back
in
design view:


SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running

FROM tableNameHere AS a , tableNameHere AS b

WHERE a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]



and indeed, switching back in design view, you could see what you
could
have
done, graphically, to produce that query (rather than doing it in SQL
view).





Vanderghast, Access MVP



Do I need to have both deposits and checks in the same column, and
show
as
negative and positive? I'd rather not if If I don't have to.

:

This is a very simple account. For date it is set to edium date,
It
is
only
one account so I don't have that as a field. I have fields named
check
number, check amount, deposit amount, date of transaction, Name,
Status
(for
cashed or uncashed).
When I create the new query in design view and bring the check book
table,
please tell me exactly what I need to do after I bring down the
fields.
Thanks.

:

You should edit a new query, in SQL view, and type the code.
Missing
the
tableName you have to supply (two places). Also assuming that
[Check
Amount]
is a debit and [Deposit Amount] a credit (still not sure of your
field
accountNumber and dateTime)


SELECT a.accountNumber,
a.dateTime,
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.accountNumber= b.accountNumber
AND a.dateTime >= b.dateTime
GROUP BY a.accountNumber, a.dateTime



Vanderghast, Access MVP



message
My field names are Check Amount and Deposit Amount. Where
would I
put the
info you gave me. I dont know SQL etc, hust how to work in the
basic
design
view,

Would you walk me through. Thanks

:

Assuming there is a single transaction, per second, per
account.

Assuming there is a single field, amount, with + and - for
credit
and
debit: +2000 and -342.44 as example.


SELECT a.account,
a.dateTime,
LAST(a.amount) AS transactionAmount,
SUM(b.amount) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.account = b.account
AND a.dateTime >= b.dateTime
GROUP BY a.account, a.dateTime



should do the job. I assumed the fields name were account,
dateTime
(for
the
date and time of the transaction) and amount.


Vanderghast, Access MVP





message
I have a check book with deposits and checks written on the
account. I
would
like to run a query and show a running balance next to each
entry.
Please
help.

 

[Michel Walsh]

The whole statement can be:

SELECT a.[date of transaction],
LAST( a.[Check Amount] ) AS debit,
LAST( a.[Deposit Amount] ) AS credit,
LAST( a.[Check Number] ) AS checkNumber,
LAST( a.[Check Name] ) AS purpose,
SUM( b.[Deposit Amount] - b.[Check Amount]) As running

FROM [table Name Here] AS a , [table Name Here] AS b

WHERE a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]


Change the table name (2 places) for your real table name.

As far as the [date of transaction], no date (including time) must be
duplicated among the records. So, you can simply add time, to differentiate
those records: instead of having 1/1/2008, have 1/1/2008 00:00:01, one
second after midnight, for the first record, and, for the second record date
1/1/2008, change it to 1/1/2008 00:00:02, two second after midnight, and so
on, for each record actually dated the 1/1/2008, until each record has a
different date_time value. After that modification, the order (of the
running sum) will be properly defined, and the first sum will become
correct.


Vanderghast, Access MVP

Ok, when I change the data so that all have a unique date, it works fine.
I
then tried it with more than one records with the same date ,using the
date/time format to "general date". The query however then dropped one of
the records. How should I format the date, or do I actually have to type
in
the time, and please give me am example how I would type it in. Also the
code to show up the name and check numbers, would you please give me the
whole code, as I think you left out the last part, and I tried to follow
the
previous example, but it did ot work.

Thanks so much for your help.

You probably have MORE THAN ONE RECORD with the date of January First
2008.
If that reflects 2007 year 'closure', try to replace all those records
with
just ONE record dated January the first (with an amount in the debit and
an
amount in the credit, if applicable). You can also use a date AND time to
make record unique (say, one record at 00:00:00, one record at 00:00:01,
and
so on) if making just ONE record is somehow impossible to achieve.
Indeed,
if there is many record with the same date (and time), then the ORDER of
the
running sum is not well defined (and the implicit join is likely to
wrongly
(for our goal) amplify the data).


If you have just one record for January the First 2008, and no record
before
that date, then, I am lost. Can you post part of the initial data you
have
in your initial table, here?




Vanderghast, Access MVP

Hi, I am just noticing that the ending balance is correct but there is
an
error on the first line of the query.
dateTime debit credit running
01-Jan-08 $0.00 $40,000.00 $79,800.00
03-Jan-08 $200.00 0.00 $39,700.00
04-Jan-08 $100.00 $0.00 $39,600.00
05-Jan-08 $100.00 $0.00 $39,500.00
17-Jan-08 $60.00 $0.00 $39,440.00
29-Jan-08 $500.00 $0.00 $38,940.00
02-Feb-08 $30.00 $0.00 $38,910.00

This is my code:
SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running

FROM tableNameHere AS a , tableNameHere AS b

WHERE a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]


Thanks

:

Sure:

SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
LAST(a.[Check Number]) AS checkNumber,
LAST(a.[Check Name]) AS purpose,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running
FROM
...



Vanderghast, Access MVP




Thanks so much , I got it to work. Is there anyway, I can include
the
Check
number and name in the query.

:

You don't have to have only one column, no (although since we now
use
handheld computer, or computer, there is no real need to have two
columns,
since hand held computer can as easily add than subtract ).



SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running

FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]


in a new query, in SQL view (still have to change the table name at
two
places, use [ ] around the name if the name has spaces in it.

You can also use a similar version, which will allow you to switch
back
in
design view:


SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running

FROM tableNameHere AS a , tableNameHere AS b

WHERE a.[date of transaction] >= b.[date of transaction]

GROUP BY a.[date of transaction]



and indeed, switching back in design view, you could see what you
could
have
done, graphically, to produce that query (rather than doing it in
SQL
view).





Vanderghast, Access MVP



Do I need to have both deposits and checks in the same column,
and
show
as
negative and positive? I'd rather not if If I don't have to.

:

This is a very simple account. For date it is set to edium
date,
It
is
only
one account so I don't have that as a field. I have fields
named
check
number, check amount, deposit amount, date of transaction, Name,
Status
(for
cashed or uncashed).
When I create the new query in design view and bring the check
book
table,
please tell me exactly what I need to do after I bring down the
fields.
Thanks.

:

You should edit a new query, in SQL view, and type the code.
Missing
the
tableName you have to supply (two places). Also assuming that
[Check
Amount]
is a debit and [Deposit Amount] a credit (still not sure of
your
field
accountNumber and dateTime)


SELECT a.accountNumber,
a.dateTime,
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.accountNumber= b.accountNumber
AND a.dateTime >= b.dateTime
GROUP BY a.accountNumber, a.dateTime



Vanderghast, Access MVP



message
My field names are Check Amount and Deposit Amount. Where
would I
put the
info you gave me. I dont know SQL etc, hust how to work in
the
basic
design
view,

Would you walk me through. Thanks

:

Assuming there is a single transaction, per second, per
account.

Assuming there is a single field, amount, with + and -
for
credit
and
debit: +2000 and -342.44 as example.


SELECT a.account,
a.dateTime,
LAST(a.amount) AS transactionAmount,
SUM(b.amount) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.account = b.account
AND a.dateTime >= b.dateTime
GROUP BY a.account, a.dateTime



should do the job. I assumed the fields name were account,
dateTime
(for
the
date and time of the transaction) and amount.


Vanderghast, Access MVP





message
I have a check book with deposits and checks written on
the
account. I
would
like to run a query and show a running balance next to
each
entry.
Please
help.

 

[ken]

Here's an alternative approach, which uses subqueries to compute the
balance. This allows for more than one transaction with the same date
as it uses the distinct TransactionID values to differentiate between
them. You simply need to add an autonumber TransactionID column to
your table if you don't already have one. The drawback of this
approach is that the TransactionID values are arbitrary so the result
table will not necessarily follow the normal accounting convention of
listing credits before debits within each business day. That of
course is also true if unique date/time values are used to order the
transactions rather than differentiating between transactions per date
by the distinct TransactionID values:

SELECT TransactionDate, Credit, Debit,
(SELECT SUM(Credit)
FROM Transactions AS T2
WHERE T2.TransactionDate <= T1.TransactionDate
AND ( T2.TransactionID <= T1.TransactionID
OR T2.TransactionDate <> T1.TransactionDate)) -
(SELECT SUM(Debit)
FROM Transactions AS T2
WHERE T2.TransactionDate <= T1.TransactionDate
AND ( T2.TransactionID <= T1.TransactionID
OR T2.TransactionDate <> T1.TransactionDate)) AS Balance
FROM Transactions AS T1
ORDER BY TransactionDate DESC, TransactionID DESC;

The above can only really be created in SQL view, but all you have to
do is paste in the above into the SQL view window, change the table
and field names to your own, remembering to wrap them in square
brackets [like this] if they contain spaces or other special
characters (if in doubt wrap them all in square brackets). Don't
change the aliases T1 or T2, though; just leave them as they are as
these differentiate the different instances of the table.

Any other fields from the table which you wish to include can be
simply be added to the first line of the query, separating then with
commas, either before, after or in between the fields I've included
above; the choice is entirely yours.

The result table follows the usual convention of listing the latest
transactions first, but can be easily reversed simply by removing the
two DESCs so that it sorts in the default ascending order.

Ken Sheridan
Stafford, England

Ok, when I change the data so that all have a unique date, it works fine. I
then tried it with more than one records with the same date ,using the
date/time format to "general date". The query however then dropped one of
the records. How should I format the date, or do I actually have to type in
the time, and please give me am example how I would type it in. Also the
code to show up the name and check numbers, would you please give me the
whole code, as I think you left out the last part, and I tried to follow the
previous example, but it did ot work.

Thanks so much for your help.

You probably have MORE THAN ONE RECORD with the date of January First 2008.
If that reflects 2007 year 'closure', try to replace all those records with
just ONE record dated January the first (with an amount in the debit and an
amount in the credit, if applicable). You can also use a date AND time to
make record unique (say, one record at 00:00:00, one record at 00:00:01, and
so on) if making just ONE record is somehow impossible to achieve. Indeed,
if there is many record with the same date (and time), then the ORDER of the
running sum is not well defined (and the implicit join is likely to wrongly
(for our goal) amplify the data).

If you have just one record for January the First 2008, and no record before
that date, then, I am lost. Can you post part of the initial data you have
in your initial table, here?

Vanderghast, Access MVP

Hi, I am just noticing that the ending balance is correct but there is an
error on the first line of the query.
dateTime debit credit running
01-Jan-08 $0.00 $40,000.00 $79,800.00
03-Jan-08 $200.00 0.00 $39,700.00
04-Jan-08 $100.00 $0.00 $39,600.00
05-Jan-08 $100.00 $0.00 $39,500.00
17-Jan-08 $60.00 $0.00 $39,440.00
29-Jan-08 $500.00 $0.00 $38,940.00
02-Feb-08 $30.00 $0.00 $38,910.00
This is my code:
SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running
FROM tableNameHere AS a , tableNameHere AS b
WHERE a.[date of transaction] >= b.[date of transaction]
GROUP BY a.[date of transaction]
Thanks
:
Sure:
SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
LAST(a.[Check Number]) AS checkNumber,
LAST(a.[Check Name]) AS purpose,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running
FROM
...
Vanderghast, Access MVP
Thanks so much , I got it to work. Is there anyway, I can include the
Check
number and name in the query.
:
You don't have to have only one column, no (although since we now use
handheld computer, or computer, there is no real need to have two
columns,
since hand held computer can as easily add than subtract ).
SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.[date of transaction] >= b.[date of transaction]
GROUP BY a.[date of transaction]
in a new query, in SQL view (still have to change the table name at
two
places, use [ ] around the name if the name has spaces in it.
You can also use a similar version, which will allow you to switch
back
in
design view:
SELECT a.[date of transaction],
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running
FROM tableNameHere AS a , tableNameHere AS b
WHERE a.[date of transaction] >= b.[date of transaction]
GROUP BY a.[date of transaction]
and indeed, switching back in design view, you could see what you
could
have
done, graphically, to produce that query (rather than doing it in SQL
view).
Vanderghast, Access MVP
Do I need to have both deposits and checks in the same column, and
show
as
negative and positive? I'd rather not if If I don't have to.
:
This is a very simple account. For date it is set to edium date,
It
is
only
one account so I don't have that as a field. I have fields named
check
number, check amount, deposit amount, date of transaction, Name,
Status
(for
cashed or uncashed).
When I create the new query in design view and bring the check book
table,
please tell me exactly what I need to do after I bring down the
fields.
Thanks.
:
You should edit a new query, in SQL view, and type the code.
Missing
the
tableName you have to supply (two places). Also assuming that
[Check
Amount]
is a debit and [Deposit Amount] a credit (still not sure of your
field
accountNumber and dateTime)
SELECT a.accountNumber,
a.dateTime,
LAST(a.[Check Amount]) AS debit,
LAST(a.[Deposit Amount]) AS credit,
SUM(b.[Deposit Amount] - b.[Check Amount]) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.accountNumber= b.accountNumber
AND a.dateTime >= b.dateTime
GROUP BY a.accountNumber, a.dateTime
Vanderghast, Access MVP
message
My field names are Check Amount and Deposit Amount. Where
would I
put the
info you gave me. I dont know SQL etc, hust how to work in the
basic
design
view,
Would you walk me through. Thanks
:
Assuming there is a single transaction, per second, per
account.
Assuming there is a single field, amount, with + and - for
credit
and
debit: +2000 and -342.44 as example.
SELECT a.account,
a.dateTime,
LAST(a.amount) AS transactionAmount,
SUM(b.amount) As running
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.account = b.account
AND a.dateTime >= b.dateTime
GROUP BY a.account, a.dateTime
should do the job. I assumed the fields name were account,
dateTime
(for
the
date and time of the transaction) and amount.
Vanderghast, Access MVP
message
I have a check book with deposits and checks written on the
account. I
would
like to run a query and show a running balance next to each
entry.
Please
help.