Differences between values in queries 1

[Ulsterman2]

Hi

I'm looking to find the diffence between sequential records in a query, and show that difference as a field in the query.

e.g.

Shift Total to date Total for shift
1 1000 1000
2 1500 500
3 2100 600
6 5000 3900

The field I require is "Total for Shift" which takes the "Total to Date" value in the record and subtracts the same value in the previous record from it.

Thanks

 

[PHV]

How do you know which is the previous record ?
In other words what is the ORDER BY clause of your query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ulsterman2]

The "Shift" field will be unique and will be in acsending order.

 

[PHV]

Something like this ?
SELECT A.Shift, A.[Total to date], A.[Total to date]-Nz(B.[Total to date]) AS [Total for shift]
FROM qryShift AS A LEFT JOIN qryShift AS B ON A.Shift > B.Shift
WHERE Nz(B.Shift,0)=Nz((SELECT Max(Shift) FROM qryShift WHERE Shift < A.Shift),0);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ulsterman2]

Thanks

Worked a treat!

 

[Ulsterman2]

I tried applying the above code to the query below to find the difference between the "Sample" field in each record, but this time it only displayed one record (the first)where there should be in the region of 1000.

SELECT A.ID, A.Date, A.Supplier, a.Reference, a.Gross, a.Sample, A.Sample-Nz(B.Sample) AS Diff
FROM [qry Totals to Date] AS A LEFT JOIN [qry Totals to date] AS B ON A.ID>B.ID
WHERE Nz(B.ID,0)=Nz((SELECT Max(ID) FROM [qry Totals to date] WHERE ID < A.ID),0);

The data originates from a table with a few thousand records. Running "qry Totals to Date" asks for a period number which filters the data and performs calculations on it, including the calculation of the "Sample" field. The "ID" field is an autonumber field from the original table and is sorted in ascending order.

I'm not an expert SQL programmer so I may have mis-types something in transfering the code.

Any help would be greatly appreciated.