Access act like spreedsheet

[Guest]

Hi, I have problem in query and I am not sure if it is possible.
My query has a column for
Branch,DateProcessed,OpeningBalance,Payments,Collections&
ClosingBalance. On the first entry all data will enter manually
except for ClosingBalance, it will be computed as OpeningBalance+
Payments+Collections.On the next and succeding entry, the
Openingbalance will be taken from what ever the amount
in Closingbalance from the last entry
and the formula will be the same,is there a way to that?

In spreedsheet it will like this:

A B C D
1 10 20 30 60
2 60 10 10 80
3 80

A B C D
1 10 20 30 A1+B1+C1
2 D1 10 10 A1+B1+C1
3 D2

Thank you for any help!!!

 

[Michel Walsh]

-- untested

SELECT a.id,
Nz(SUM(Nz(b.a, 0)+ b.b+b.c), a.a),
a.b,
a.c,
Nz(SUM(Nz(b.a, 0)+ b.b+ b.c), a.a)+ a.b+a.c

FROM myTable As a LEFT JOIN myTable As b
ON a.id > b.id

GROUP BY a.id



where I assumed the first column is called id, and the table name is
myTable.

a and b are tow aliases to the same table, but b refers to records coming
'before' those in a.

For a.id=1, b will be empty, so its fields will get null in them, the sum( )
over them will also be null, so, we end up with a.a+a.b+a.c, the sum of the
columns of the first record.

For a.id>1, b won't be empty. I assumed that b.a will be null, for all
records, except for the very first one, so I changed that null to a zero,
and the sum will operate over all the previous records, as intended.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thank you Michel, I'm a little bit confused. What is that a.id, a.c a.b, b.b
did you assumed 2 tables where file name are a and b? I'm still new in
access, If you could explain to me more, I will really appreciate it.


TY

 

[Michel Walsh]

Not two tables, just one, a and b as two alias, or, for a figure, let assume
they acts like two fingers that run through the records of your table, but
finger_b runs through all records that have their id value less than the id
value actually pointed by finger_a.

a.id is thus the id field pointed by finger_a, and b.c is the field c
pointed by finger_b.

When finger_a is on the first record, a.id=1, you cannot point anything with
finger_b, that is what the first Nz solves, telling to use the value a.a
instead of that 'nothing-ness'.

For records with id>1, I assumed the column a has nothing in it, so
Nz(b.a, 0) changes that null into zero.



Hoping it may help,
Vanderghast, Access MVP