This should be simple...

[Alfred S.]

This should be simple but I cant figure out how to do it
simply!

Basicly, what I want to do is subtract the result of a
query from another result from the same query in the same
field.

When I run the query I have to input 2 seperate dates,
then the query shows the data for each item for each date.
Here is some example output:

Bob 32 110 1/1/2003
Bob 32 120 1/10/2003
Ted 49 320 1/1/2003
Ted 49 300 1/10/2003

I want to subtract the values 110-120 from 'Bob' and 320-
300 from 'Ted'. How do I do this, using only 1 query?

Currently I am using one query for each date and then a
seperate query to do the subtraction.

This seems like it should be simple but I just keep
scratching my head over it.

 

[Michel Walsh]

Hi,


SELECT a.ClientID, b.TimeDate, a.TimeDate, LAST(b.Amount), LAST(a.Amount),
LAST(b.Amount)-LAST(a.Amount) As Difference

FROM (MyTable As a INNER JOIN MyTable As b
ON a.ClientID=b.ClientID )
INNER JOIN MyTable As c
ON b.ClientID=c.ClientID

WHERE b.TimeDate < a.TimeDate
AND c.TimeDate<=b.TimeDate

GROUP BY a.ClientID, a.TimeDate, b.TimeDate

HAVING b.TimeDate = Max(c.TimeDate)


or, if you prefer a more classical solution implying sub-select (but then, you have to type in SQL
view, while the previous solution allows you to use the query designer graphical part to "write"
the whole query) :


SELECT a.ClientID, b.TimeDate, a.TimeDate, b.Amount, a.Amount, b.Amount-a.Amount As Difference
FROM MyTable As a INNER JOIN MyTable As b
ON ( a.ClientID=b.ClientID AND b.TimeDate < a.TimeDate)

WHERE b.TimeDate=( SELECT MAX( c.TimeDate)
FROM MyTable As c
WHERE c.ClientID=b.ClientID
AND c.TimeDate <= b.TimeDate)



Hoping it may help,
Vanderghast, Access MVP