query needed to subtract values

  • Thread starter Thread starter vnl
  • Start date Start date
V

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.
 
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
 
Back
Top