Calculating beginning balance based on ending balance

[Guest]

I am using the following SQL to generate the table below for a SINGLE sales
rep.

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

SalesDate 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
etc.

Next, I need to generate a similar table for MULTIPLE sales reps (e.g. have
IDs of 10, 20 and 40). For example,

ID SalesDate Begin_Balance Sales EndBalance
10 9/1/04 100 20 120
10 9/2/04 120 40 160
10 9/3/04 160 -50 110
10 9/4/04 110 80 190
20 9/3/04 50 30 80
20 9/5/04 80 90 170
40 9/1/04 0 80 80
40 9/2/04 80 -20 60
40 9/3/04 60 40 100
etc.

Is there a standard query/report method or algorithm for doing this?

 

[Marshall Barton]

I am using the following SQL to generate the table below for a SINGLE sales
rep.

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

SalesDate 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
etc.

Next, I need to generate a similar table for MULTIPLE sales reps (e.g. have
IDs of 10, 20 and 40). For example,

ID SalesDate Begin_Balance Sales EndBalance
10 9/1/04 100 20 120
10 9/2/04 120 40 160
10 9/3/04 160 -50 110
10 9/4/04 110 80 190
20 9/3/04 50 30 80
20 9/5/04 80 90 170
40 9/1/04 0 80 80
40 9/2/04 80 -20 60
40 9/3/04 60 40 100

I think you'll get the desired result by just adding another
criteria to the subquery:

SELECT date,
Nz((SELECT Sum(sales)
FROM tbl1 as B
WHERE B.SalesDate< A.SalesDate
AND B.ID = A.ID
), 0) as BeginBalance,
sales,
BeginBalance + sales as EndBalance
FROM tbl1 as A
WHERE a.SalesDate Between [Start Date] and [End Date]

but, shouldn't that first field be SalesDate?