Formula in Query to produce Balance field

[KimTong via AccessMonster.com]

Hi,

Can anyone tell me how to put a formula to produce a Balance field in query?
Here is the sample of data:

Date Description In Out
Balance
11/1/07 aaaaaaa 50
50
11/2/07 cccccccc 10
40
11/2/07 bbbbbb 5
35
11/5/07 iiiiiiiiiiiii 20
55
11/5/07 rrrrrrrrr 20
35

The Balance should be: Previous Balance+In-Out. The problem is the previous
balance on the first record.

Thanks in advance.

KT

 

[Michel Walsh]

SELECT a.date, LAST(a.description), LAST(a.in), LAST(a.out),
SUM(Nz(b.in,0) - Nz(b.out,0))

FROM myTable As a INNER JOIN myTable As b
ON a.date >= b.date

GROUP BY a.date



Hoping it may help,
Vanderghast, Access MVP

 

[KimTong via AccessMonster.com]

Hi,

Thank you for the response. I made a SQL script like this:

SELECT a.date, LAST(a.description), LAST(a.[in]), LAST(a.out), SUM(Nz(b.[in],
0)-Nz(b.out,0))
FROM Table1 AS a INNER JOIN Table1 AS b ON a.date>=b.date;

But the error message says "you tried to execute query that doesn't include
the specified expression 'date' as a part of aggregate function"

Is anyone can tell which part is error? Thank you

KF

SELECT a.date, LAST(a.description), LAST(a.in), LAST(a.out),
SUM(Nz(b.in,0) - Nz(b.out,0))

FROM myTable As a INNER JOIN myTable As b
ON a.date >= b.date

GROUP BY a.date

Hoping it may help,
Vanderghast, Access MVP

Hi,

[quoted text clipped - 22 lines]

KT

 

[Michel Walsh]

You are missing the

GROUP BY a.date



Vanderghast, Access MVP

Hi,

Thank you for the response. I made a SQL script like this:

SELECT a.date, LAST(a.description), LAST(a.[in]), LAST(a.out),
SUM(Nz(b.[in],
0)-Nz(b.out,0))
FROM Table1 AS a INNER JOIN Table1 AS b ON a.date>=b.date;

But the error message says "you tried to execute query that doesn't
include
the specified expression 'date' as a part of aggregate function"

Is anyone can tell which part is error? Thank you

KF

SELECT a.date, LAST(a.description), LAST(a.in), LAST(a.out),
SUM(Nz(b.in,0) - Nz(b.out,0))

FROM myTable As a INNER JOIN myTable As b
ON a.date >= b.date

GROUP BY a.date

Hoping it may help,
Vanderghast, Access MVP

Hi,

[quoted text clipped - 22 lines]

KT