compare adjacent records

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

Guest

I have a table of several water quality parameters collected by a sensor
permanently mounted to a pier. Occasionally a grain of sand or critter will
temporarily disrupt the sensor. I need to be able to locate these blips by
comparing each record to the adjacent records and seeing if there is a 50% or
greater drop in the readings. I need to locate these blips so I can better
qa/qc my data. I think that the best route is to do this in a query but am
unsure how to proceed.
 
What other data are you collecting? Datetime? Sample number? You will
need something to know which to compare.
 
Tammie said:
I have a table of several water quality parameters collected by a sensor
permanently mounted to a pier. Occasionally a grain of sand or critter will
temporarily disrupt the sensor. I need to be able to locate these blips by
comparing each record to the adjacent records and seeing if there is a 50% or
greater drop in the readings. I need to locate these blips so I can better
qa/qc my data. I think that the best route is to do this in a query but am
unsure how to proceed.
Click to expand...


See if this get you close:

SELECT T.sensorID, T.sensorreading, T.ReadingDate, . . .
(SELECT Top 1 X.sensorreading
FROM table As X
WHERE X.ReadingDate < T.ReadingDate
ORDER BY X.ReadingDate DESC) As PrevReading
FROM table As T
WHERE (SELECT Top 1 X.sensorreading
FROM table As X
WHERE X.ReadingDate < T.ReadingDate
ORDER BY X.ReadingDate DESC) / T.sensorreading > .5
And T.ReadingDate Between [Start Date] And [End Date]

The date range criteria should be replaced by form text
boxes after you get the rest of it working.
 
Marshall said:
Tammie said:
I have a table of several water quality parameters collected by a sensor
permanently mounted to a pier. Occasionally a grain of sand or critter will
temporarily disrupt the sensor. I need to be able to locate these blips by
comparing each record to the adjacent records and seeing if there is a 50% or
greater drop in the readings. I need to locate these blips so I can better
qa/qc my data. I think that the best route is to do this in a query but am
unsure how to proceed.
Click to expand...


See if this get you close:

SELECT T.sensorID, T.sensorreading, T.ReadingDate, . . .
(SELECT Top 1 X.sensorreading
FROM table As X
WHERE X.ReadingDate < T.ReadingDate
ORDER BY X.ReadingDate DESC) As PrevReading
FROM table As T
WHERE (SELECT Top 1 X.sensorreading
FROM table As X
WHERE X.ReadingDate < T.ReadingDate
ORDER BY X.ReadingDate DESC) / T.sensorreading > .5
And T.ReadingDate Between [Start Date] And [End Date]

The date range criteria should be replaced by form text
boxes after you get the rest of it working.
Click to expand...

Well that is missing a critical part. The subquery (both
instances) should be:

(SELECT Top 1 X.sensorreading
FROM table As X
WHERE X.ReadingDate < T.ReadingDate
And X.sensorID = T.sensorID
ORDER BY X.ReadingDate DESC)
 
I am collecting sample id code, date and time.

KARL DEWEY said:
What other data are you collecting? Datetime? Sample number? You will
need something to know which to compare.
Click to expand...
 
Try these two queries using your table and field names ---
Tammie_Sort --
SELECT Q.SampleID, Q.Sensorreading, Q.ReadingDate, (SELECT COUNT(*) FROM
Tammie Q1
WHERE Q1.ReadingDate < Q.ReadingDate)+1 AS Rank
FROM Tammie AS Q
ORDER BY Q.SampleID, Q.Sensorreading, Q.ReadingDate;

SELECT Tammie_Sort.Rank, Tammie_Sort.SampleID, Tammie_Sort.Sensorreading,
Tammie_Sort_1.Sensorreading
FROM Tammie_Sort, Tammie_Sort AS Tammie_Sort_1
WHERE (((Tammie_Sort_1.Sensorreading)<[Tammie_Sort].[Sensorreading]*0.5 Or
(Tammie_Sort_1.Sensorreading)>[Tammie_Sort].[Sensorreading]*2) AND
((Tammie_Sort_1.Rank)=[Tammie_Sort].[Rank]+1));
 
Back
Top