Incorrect calculation of beginning balance

[Guest]

With a sample input of:

Date Begin_Balance Sales EndBalance
9/1/04 100 20 computed
9/2/04 computed 40 computed
9/3/04 computed -50 computed
9/4/04 computed 80 computed

Using the following SQL:

SELECT date, Nz((select sum(sales) from tbl1 as B where B.date < A.date), 0)
as BeginBalance, sales, BeginBalance + sales as EndBalance from tbl1 as A
where a.date Between [Start Date] and [End Date]

For date ranges of From 9/2 To 9/4 or From 9/3 to 9/4 the SQL works fine.
However, for the date range starting with 9/1 (e.g., From 9/1 to 9/4 it
gives:

Date Begin_Balance Sales EndBalance
9/1/04 0 20 20
9/2/04 20 40 60
9/3/04 60 -50 10
9/4/04 10 80 90

What I want, for From 9/1 To 9/4 is

Date Begin_Balance Sales EndBalance
9/1/04 100 20 120
9/2/04 120 40 160
9/3/04 160 -50 110
9/4/04 110 80 190

Any ideas?

 

[Jeff Boyce]

You're saying the initial balance isn't being calculated correctly -- since
your SQL uses a "previous" sum (B.date < A.date), what's in your table
before your start date?

By the way, MS Access treats the word "date" (among many others) as a
reserved word. If you have a field named "Date" (or another named "Name",
for that matter), both you and Access may be confused about what's being
referred to...