Forum Home
Press F1
 
Thread ID: 8915 2001-04-25 22:27:00 Microsoft Access Question Guest (0) Press F1
Post ID Timestamp Content User
11800 2001-04-25 22:27:00 Hi,
I have created a table with three columns. The first is the autonumber with the key in, the second is a date in the format of dd/mm/yy and the third is a number that increases a different amount with every new entry. Below is an example.

1 07-Feb-01 2345
2 14-Feb-01 2366
3 21-Feb-01 2369

What I want to do is create a query that subtracts the number for one date from the number of the date after.
i.e. the difference in the numbers between 14 Feb to 21 Feb. (answer is 2369-2366=3)


Regards
N Finn. 		Guest (0)
11801 2001-04-28 02:43:00 Howdy,

Let's say your autonumbered primary key is called lngKey, the date field is called dtmDate, and the number that increases a different amount with every new entry is called lngAmount. To calculate the difference between lngAmount (lngDiff) for the records of two arbitrary dates dtmDate1 and dtmDate2, you could use this query:

PARAMETERS dtmDate1 DateTime, dtmDate2 DateTime;
SELECT SUM((dtmDate = dtmDate1)*lngAmount - (dtmDate = dtmDate2)*lngAmount) AS lngDiff
FROM tbdTest
WHERE ((dtmDate = dtmDate1) OR (dtmDate = dtmDate2));

To calculate all of the differences lngDiff between records is a bit more difficult, and I can't think of a better way than using an inner join to do the trick. This query works, providing that the primary key values in lngKey are consecutive (i.e. 1, 2, 3, 4, 5 etc) and the values in dtmDate are strictly ascending with respect to lngKey:

SELECT (qryLeftSide.lngAmount - qryRightSide.lngAmount) AS lngDiff
FROM qryLeftSide INNER JOIN qryRightSide ON qryLeftSide.lngKey = qryRightSide.lngKeyP1;

where qryLeftSide is defined as:

SELECT lngKey, lngAmount FROM tbdTEST WHERE lngKey NOT IN
(SELECT TOP 1 lngKey FROM tbdTest ORDER BY dtmDate ASC);

and qryRightSide is defined as:

SELECT (lngKey+1) AS lngKeyP1, lngAmount FROM tbdTEST WHERE lngKey NOT IN (SELECT TOP 1 lngKey FROM tbdTest ORDER BY dtmDate DESC);

If the values in lngKey are not all consecutive when sorted in order, as is often the case, or the values in dtmDate are not strictly ordered with respect to lngKey, then you will probably be better off writing some code to wade through the table record-by-record to calculate each difference. 		Guest (0)
1