Re: running balance

Tech-Archive recommends: Fix windows errors by optimizing your registry

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



"Christina" <Christina@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6CAF7FA3-3B3D-4A87-BF80-4E174CD88411@xxxxxxxxxxxxxxxx
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.

"Michel Walsh" wrote:

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




"Christina" <Christina@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:13CC8298-ED71-4504-B933-18A7140289A5@xxxxxxxxxxxxxxxx
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

"Michel Walsh" wrote:

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




"Christina" <Christina@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F9313AD8-55FE-4F34-BBC1-B0E06048C29D@xxxxxxxxxxxxxxxx
Thanks so much , I got it to work. Is there anyway, I can include
the
Check
number and name in the query.

"Michel Walsh" wrote:

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



"Christina" <Christina@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7B0EE047-43CF-4787-9C8B-5ED6B2D6C693@xxxxxxxxxxxxxxxx
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.

"Christina" wrote:

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.

"Michel Walsh" wrote:

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" <Christina@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:BD72CF9B-E751-47CB-80E2-A0464C3F8748@xxxxxxxxxxxxxxxx
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" wrote:

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" <Christina@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:E6660964-BA19-45D4-89D8-7F5D1E490AD9@xxxxxxxxxxxxxxxx
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.


















.

Relevant Pages

  • Re: Aging Function
    ... bottom of your activity statement to show the AgeBuckets for each account. ... DebitID, and Amount. ... I would like to subtract the total credit ... > transactions starting with the oldest debit transactions, ...
    (microsoft.public.access.modulesdaovba)
  • Re: Issues: A Question Of Integrity (was: Issues)
    ... >>> debit or it represents a CREDIT for Hamilton's only proactive actor. ... Therefore we can regard -c as the amount ... > mathematics knows is that $50 moved SOMEWHERE. ...
    (sci.bio.evolution)
  • Re: running balance
    ... SELECT a.[date of transaction], ... LASTAS debit, ... LAST(a.[Deposit Amount]) AS credit, ...
    (microsoft.public.access.queries)
  • Re: running balance
    ... amount in the credit, if applicable). ... FROM tableNameHere AS a, tableNameHere AS b ... GROUP BY a.[date of transaction] ...
    (microsoft.public.access.queries)
  • Re: running balance
    ... amount in the credit, if applicable). ... FROM tableNameHere AS a, tableNameHere AS b ... GROUP BY a.[date of transaction] ...
    (microsoft.public.access.queries)