query needed to subtract values

I need to create a report or field that will show the difference between a
customer's balance on two different dates for every customer in our
database (for example, see below: I need the difference between customer
001's balance on 1/1/03 and their balance on 2/1/03). I have the following
types of information in a single table:

Customer_ID Date Balance
1 1/1/03 $50
1 2/1/03 $55
1 3/1/03 $75
1 4/1/03 $100
2 1/1/03 $10
2 2/1/03 $19
2 3/1/03 $52
2 4/1/03 $25

I need to automate the calculation because there are thousands of entries.
Any idea how to do this?

Thanks.

On Mon, 02 Aug 2004 18:14:11 -0500, vnl <vnl999@vnl999.invalid> wrote:

Here is one solution. Note that I took the liberty to rename your
fields. Date is a reserved word, so I don't like to use it as a field
name.

SELECT T1.Customer_ID, T1.BalanceDate, T1.BalanceAmount, (Select
BalanceAmount from tblCustomerBalance T2 where
Customer_ID=T1.Customer_ID and T2.BalanceDate=(select Min(BalanceDate)
from tblCustomerBalance T3 where T3.BalanceDate>T1.BalanceDate)) AS
NextAmount, [BalanceAmount]-[NextAmount] AS Difference
FROM tblCustomerBalance AS T1;

-Tom.



[color=blue]
>I need to create a report or field that will show the difference between a
>customer's balance on two different dates for every customer in our
>database (for example, see below: I need the difference between customer
>001's balance on 1/1/03 and their balance on 2/1/03). I have the following
>types of information in a single table:
>
>Customer_ID Date Balance
>1 1/1/03 $50
>1 2/1/03 $55
>1 3/1/03 $75
>1 4/1/03 $100
>2 1/1/03 $10
>2 2/1/03 $19
>2 3/1/03 $52
>2 4/1/03 $25
>
>I need to automate the calculation because there are thousands of entries.
>Any idea how to do this?
>
>Thanks.[/color]

Hi,


If you have records for each date that is super cool...



SELECT a.CustomerID, a.Date, a.Balance-b.Balance
FROM myTable As a LEFT JOIN myTable As b
ON b.date= a.date-1 AND a.CustomerID=b.CustomerID


The idea is to get two references to the same data, but for the same client,
"b" is one day in the past, in relation with the data in "a".



Hoping it may help,
Vanderghast, Access MVP



"vnl" <vnl999@vnl999.invalid> wrote in message
news:Xns9539C3A2B2FDDvnl999@216.196.97.131...[color=blue]
> I need to create a report or field that will show the difference between a
> customer's balance on two different dates for every customer in our
> database (for example, see below: I need the difference between customer
> 001's balance on 1/1/03 and their balance on 2/1/03). I have the following
> types of information in a single table:
>
> Customer_ID Date Balance
> 1 1/1/03 $50
> 1 2/1/03 $55
> 1 3/1/03 $75
> 1 4/1/03 $100
> 2 1/1/03 $10
> 2 2/1/03 $19
> 2 3/1/03 $52
> 2 4/1/03 $25
>
> I need to automate the calculation because there are thousands of entries.
> Any idea how to do this?
>
> Thanks.
>[/color]