Subtracting fields between records in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have set up the following query:

SELECT [qry Totals to Date].ID, [qry Totals to Date].Date, [qry Totals to
Date].Supplier, [qry Totals to Date].Reference, [qry Totals to Date].Gross,
[qry Totals to Date].Sample
FROM [qry Totals to Date];

I want to show for each record an additional field that takes the value in
the "Sample" field for that records and subtracts from it the value in the
"Sample" field from the previous record.

The query is sorted in ascending order by "ID" which is an autonumber field
in the original table, but as the query also excludes creatin records from
the table the ID field will not always increase by 1 each time.

Hope that all made sense!

Any help would be greatly appreciated

AL
 
AL said:
I have set up the following query:

SELECT [qry Totals to Date].ID, [qry Totals to Date].Date, [qry Totals to
Date].Supplier, [qry Totals to Date].Reference, [qry Totals to Date].Gross,
[qry Totals to Date].Sample
FROM [qry Totals to Date];

I want to show for each record an additional field that takes the value in
the "Sample" field for that records and subtracts from it the value in the
"Sample" field from the previous record.

The query is sorted in ascending order by "ID" which is an autonumber field
in the original table, but as the query also excludes creatin records from
the table the ID field will not always increase by 1 each time.
Click to expand...


Probably not going to be especially quick, but you can use a
subquery. Assuming that the "previous record" is determined
by the Date field (BTW, terrible name for a field).

SELECT T.ID, T.Date, T.Supplier, T.Reference,
T.Gross, T.Sample,
(SELECT TOP 1 X.Sample
FROM [qry Totals to Date] As X
WHERE X.Date < T.Date
And X.ID = T.ID
ORDER BY X.Date DESC
) As PrevSample,
Sample - PrevSample As Delta
FROM [qry Totals to Date] As T

If the Date field is not how you determine the "previous
record", you can use that as the general idea to rearrange
it to use the ID field instead instead of Date.
 
Back
Top