query needed to subtract values

[vnl]

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.

[Tom van Stiphout]

Re: query needed to subtract values

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.BalanceAmoun t, (Select
BalanceAmount from tblCustomerBala nce T2 where
Customer_ID=T1. Customer_ID and T2.BalanceDate= (select Min(BalanceDate )
from tblCustomerBala nce T3 where T3.BalanceDate> T1.BalanceDate) ) AS
NextAmount, [BalanceAmount]-[NextAmount] AS Difference
FROM tblCustomerBala nce 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]

[Michel Walsh]

Re: query needed to subtract values

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:Xns9539C3A 2B2FDDvnl999@21 6.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]