Subtracting fields between records in a query

[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

 

[Allen Browne]

See:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;210504

 

[Marshall Barton]

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.

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.