select record before and after, then calculate average.

B

brhall

I've got an intersting update query that I can't seem to figure out. What I
am trying to do is identify the records where [O3ppm] is zero and update that
value by averaging the hour before and the hour after. So for hour 1100, the
value should be 0.04 by averaging the [O3ppm] value for the 1000 hour(before)
and the 1200 hour(after).
Any help is greatly appreciated.

Here is a sample of the data:


[ID] [DATE] [TIME] [O3ppm]
30898754 7/1/2005 0 0.02
30898755 7/1/2005 100 0.02
30898756 7/1/2005 200 0.01
30898757 7/1/2005 300 0.02
30898758 7/1/2005 400 0.01
30898759 7/1/2005 500 0.02
30898760 7/1/2005 600 0.02
30898761 7/1/2005 700 0.03
30898762 7/1/2005 800 0.01
30898763 7/1/2005 900 0.03
30898764 7/1/2005 1000 0.02
30898765 7/1/2005 1100 0
30898766 7/1/2005 1200 0.06
30898767 7/1/2005 1300 0.04
30898768 7/1/2005 1400 0.04
30898769 7/1/2005 1500 0.05
30898770 7/1/2005 1600 0.04
30898771 7/1/2005 1700 0.03
30898772 7/1/2005 1800 0.03
30898773 7/1/2005 1900 0.02
30898774 7/1/2005 2000 0.04
30898775 7/1/2005 2100 0.03
30898776 7/1/2005 2200 0
30898777 7/1/2005 2300 0.03
 
D

Dale Fye

It looks to me like you are about to do something you shouldn't.

It looks like you are trying to coverup the fact that someone failed to take
a reading when they were supposed to. I think it would be far more
appropriate to create a query that gives you this information, including the
average value with some sort of annotation to indicate that it is in fact an
average of surrounding values.

If your records are actually sequential, you might be able to do this.

SELECT T1.ID, (SELECT Average(T2.[03ppm])
FROM yourTable T2
WHERE T2.ID BETWEEN T1.ID - 1 AND T1.ID + 1
AND T2.ID <> T1.ID) as Avg03ppm
FROM yourTable T1
WHERE T1.[03ppm] = 0

Then you could create a second (update) query that links your table to this
query and updates based on the ID value linkage.

If, the ID values are not sequential, then it will be more complicated:

SELECT ID, (SELECT Average(T2.[03ppm])
FROM yourTable T2
WHERE T2.ID
BETWEEN (SELECT Max(ID)
FROM yourTable T3
WHERE T3.ID < T1.ID)
AND (SELECT Min(PatientID)
FROM yourTable T4
WHERE T4.ID > T1.ID)
) as Avg03ppm
FROM yourTable T1
WHERE T1.[03ppm] = 0

and if there is a chance that you have more than two consecutive 0 readings,
and want to average the two non-zero values, then you will have to modify the
WHERE clauses of the T3 and T4 subqueries above to include:

WHERE T3.ID < T1.ID
AND T3.[03ppm] <> 0

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



brhall said:
I've got an intersting update query that I can't seem to figure out. What I
am trying to do is identify the records where [O3ppm] is zero and update that
value by averaging the hour before and the hour after. So for hour 1100, the
value should be 0.04 by averaging the [O3ppm] value for the 1000 hour(before)
and the 1200 hour(after).
Any help is greatly appreciated.

Here is a sample of the data:


[ID] [DATE] [TIME] [O3ppm]
30898754 7/1/2005 0 0.02
30898755 7/1/2005 100 0.02
30898756 7/1/2005 200 0.01
30898757 7/1/2005 300 0.02
30898758 7/1/2005 400 0.01
30898759 7/1/2005 500 0.02
30898760 7/1/2005 600 0.02
30898761 7/1/2005 700 0.03
30898762 7/1/2005 800 0.01
30898763 7/1/2005 900 0.03
30898764 7/1/2005 1000 0.02
30898765 7/1/2005 1100 0
30898766 7/1/2005 1200 0.06
30898767 7/1/2005 1300 0.04
30898768 7/1/2005 1400 0.04
30898769 7/1/2005 1500 0.05
30898770 7/1/2005 1600 0.04
30898771 7/1/2005 1700 0.03
30898772 7/1/2005 1800 0.03
30898773 7/1/2005 1900 0.02
30898774 7/1/2005 2000 0.04
30898775 7/1/2005 2100 0.03
30898776 7/1/2005 2200 0
30898777 7/1/2005 2300 0.03
Click to expand...
 
B

brhall

Dale,
Thanks for the reply. Calculating values this way is a federal guideline
that I must use but I've already identified all those records that are zero
for reference. I created a boolean field to let the user know if the value
was created or not.

I tried the first query becuase my records are sequential by the ID but with
no luck. The name of my table is [tblO3data]. Will this change the SQL at
all? (other than "yourTable")


Dale Fye said:
It looks to me like you are about to do something you shouldn't.

It looks like you are trying to coverup the fact that someone failed to take
a reading when they were supposed to. I think it would be far more
appropriate to create a query that gives you this information, including the
average value with some sort of annotation to indicate that it is in fact an
average of surrounding values.

If your records are actually sequential, you might be able to do this.

SELECT T1.ID, (SELECT Average(T2.[03ppm])
FROM yourTable T2
WHERE T2.ID BETWEEN T1.ID - 1 AND T1.ID + 1
AND T2.ID <> T1.ID) as Avg03ppm
FROM yourTable T1
WHERE T1.[03ppm] = 0

Then you could create a second (update) query that links your table to this
query and updates based on the ID value linkage.

If, the ID values are not sequential, then it will be more complicated:

SELECT ID, (SELECT Average(T2.[03ppm])
FROM yourTable T2
WHERE T2.ID
BETWEEN (SELECT Max(ID)
FROM yourTable T3
WHERE T3.ID < T1.ID)
AND (SELECT Min(PatientID)
FROM yourTable T4
WHERE T4.ID > T1.ID)
) as Avg03ppm
FROM yourTable T1
WHERE T1.[03ppm] = 0

and if there is a chance that you have more than two consecutive 0 readings,
and want to average the two non-zero values, then you will have to modify the
WHERE clauses of the T3 and T4 subqueries above to include:

WHERE T3.ID < T1.ID
AND T3.[03ppm] <> 0

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



brhall said:
I've got an intersting update query that I can't seem to figure out. What I
am trying to do is identify the records where [O3ppm] is zero and update that
value by averaging the hour before and the hour after. So for hour 1100, the
value should be 0.04 by averaging the [O3ppm] value for the 1000 hour(before)
and the 1200 hour(after).
Any help is greatly appreciated.

Here is a sample of the data:


[ID] [DATE] [TIME] [O3ppm]
30898754 7/1/2005 0 0.02
30898755 7/1/2005 100 0.02
30898756 7/1/2005 200 0.01
30898757 7/1/2005 300 0.02
30898758 7/1/2005 400 0.01
30898759 7/1/2005 500 0.02
30898760 7/1/2005 600 0.02
30898761 7/1/2005 700 0.03
30898762 7/1/2005 800 0.01
30898763 7/1/2005 900 0.03
30898764 7/1/2005 1000 0.02
30898765 7/1/2005 1100 0
30898766 7/1/2005 1200 0.06
30898767 7/1/2005 1300 0.04
30898768 7/1/2005 1400 0.04
30898769 7/1/2005 1500 0.05
30898770 7/1/2005 1600 0.04
30898771 7/1/2005 1700 0.03
30898772 7/1/2005 1800 0.03
30898773 7/1/2005 1900 0.02
30898774 7/1/2005 2000 0.04
30898775 7/1/2005 2100 0.03
30898776 7/1/2005 2200 0
30898777 7/1/2005 2300 0.03
Click to expand...
Click to expand...
 
B

brhall

I have this currently but it is prompting for a parameter value for T2.03ppm:

SELECT T1.ID, (SELECT Avg(T2.[03ppm])
FROM [tblO3data] AS T2
WHERE T2.ID BETWEEN T1.ID - 1 AND T1.ID + 1
AND T2.ID <> T1.ID) AS Avg03ppm
FROM tblO3data AS T1
WHERE ((([T1].[03ppm])=0));




brhall said:
Dale,
Thanks for the reply. Calculating values this way is a federal guideline
that I must use but I've already identified all those records that are zero
for reference. I created a boolean field to let the user know if the value
was created or not.

I tried the first query becuase my records are sequential by the ID but with
no luck. The name of my table is [tblO3data]. Will this change the SQL at
all? (other than "yourTable")


Dale Fye said:
It looks to me like you are about to do something you shouldn't.

It looks like you are trying to coverup the fact that someone failed to take
a reading when they were supposed to. I think it would be far more
appropriate to create a query that gives you this information, including the
average value with some sort of annotation to indicate that it is in fact an
average of surrounding values.

If your records are actually sequential, you might be able to do this.

SELECT T1.ID, (SELECT Average(T2.[03ppm])
FROM yourTable T2
WHERE T2.ID BETWEEN T1.ID - 1 AND T1.ID + 1
AND T2.ID <> T1.ID) as Avg03ppm
FROM yourTable T1
WHERE T1.[03ppm] = 0

Then you could create a second (update) query that links your table to this
query and updates based on the ID value linkage.

If, the ID values are not sequential, then it will be more complicated:

SELECT ID, (SELECT Average(T2.[03ppm])
FROM yourTable T2
WHERE T2.ID
BETWEEN (SELECT Max(ID)
FROM yourTable T3
WHERE T3.ID < T1.ID)
AND (SELECT Min(PatientID)
FROM yourTable T4
WHERE T4.ID > T1.ID)
) as Avg03ppm
FROM yourTable T1
WHERE T1.[03ppm] = 0

and if there is a chance that you have more than two consecutive 0 readings,
and want to average the two non-zero values, then you will have to modify the
WHERE clauses of the T3 and T4 subqueries above to include:

WHERE T3.ID < T1.ID
AND T3.[03ppm] <> 0

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



brhall said:
I've got an intersting update query that I can't seem to figure out. What I
am trying to do is identify the records where [O3ppm] is zero and update that
value by averaging the hour before and the hour after. So for hour 1100, the
value should be 0.04 by averaging the [O3ppm] value for the 1000 hour(before)
and the 1200 hour(after).
Any help is greatly appreciated.

Here is a sample of the data:


[ID] [DATE] [TIME] [O3ppm]
30898754 7/1/2005 0 0.02
30898755 7/1/2005 100 0.02
30898756 7/1/2005 200 0.01
30898757 7/1/2005 300 0.02
30898758 7/1/2005 400 0.01
30898759 7/1/2005 500 0.02
30898760 7/1/2005 600 0.02
30898761 7/1/2005 700 0.03
30898762 7/1/2005 800 0.01
30898763 7/1/2005 900 0.03
30898764 7/1/2005 1000 0.02
30898765 7/1/2005 1100 0
30898766 7/1/2005 1200 0.06
30898767 7/1/2005 1300 0.04
30898768 7/1/2005 1400 0.04
30898769 7/1/2005 1500 0.05
30898770 7/1/2005 1600 0.04
30898771 7/1/2005 1700 0.03
30898772 7/1/2005 1800 0.03
30898773 7/1/2005 1900 0.02
30898774 7/1/2005 2000 0.04
30898775 7/1/2005 2100 0.03
30898776 7/1/2005 2200 0
30898777 7/1/2005 2300 0.03
Click to expand...
Click to expand...
Click to expand...
 
D

Dale Fye

Your table name contains a capital O rather than a zero (0). Should the
field name contain an O as well?

That is the only thing that I can see that might be causing that particular
message.

Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



brhall said:
I have this currently but it is prompting for a parameter value for T2.03ppm:

SELECT T1.ID, (SELECT Avg(T2.[03ppm])
FROM [tblO3data] AS T2
WHERE T2.ID BETWEEN T1.ID - 1 AND T1.ID + 1
AND T2.ID <> T1.ID) AS Avg03ppm
FROM tblO3data AS T1
WHERE ((([T1].[03ppm])=0));




brhall said:
Dale,
Thanks for the reply. Calculating values this way is a federal guideline
that I must use but I've already identified all those records that are zero
for reference. I created a boolean field to let the user know if the value
was created or not.

I tried the first query becuase my records are sequential by the ID but with
no luck. The name of my table is [tblO3data]. Will this change the SQL at
all? (other than "yourTable")


Dale Fye said:
It looks to me like you are about to do something you shouldn't.

It looks like you are trying to coverup the fact that someone failed to take
a reading when they were supposed to. I think it would be far more
appropriate to create a query that gives you this information, including the
average value with some sort of annotation to indicate that it is in fact an
average of surrounding values.

If your records are actually sequential, you might be able to do this.

SELECT T1.ID, (SELECT Average(T2.[03ppm])
FROM yourTable T2
WHERE T2.ID BETWEEN T1.ID - 1 AND T1.ID + 1
AND T2.ID <> T1.ID) as Avg03ppm
FROM yourTable T1
WHERE T1.[03ppm] = 0

Then you could create a second (update) query that links your table to this
query and updates based on the ID value linkage.

If, the ID values are not sequential, then it will be more complicated:

SELECT ID, (SELECT Average(T2.[03ppm])
FROM yourTable T2
WHERE T2.ID
BETWEEN (SELECT Max(ID)
FROM yourTable T3
WHERE T3.ID < T1.ID)
AND (SELECT Min(PatientID)
FROM yourTable T4
WHERE T4.ID > T1.ID)
) as Avg03ppm
FROM yourTable T1
WHERE T1.[03ppm] = 0

and if there is a chance that you have more than two consecutive 0 readings,
and want to average the two non-zero values, then you will have to modify the
WHERE clauses of the T3 and T4 subqueries above to include:

WHERE T3.ID < T1.ID
AND T3.[03ppm] <> 0

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I've got an intersting update query that I can't seem to figure out. What I
am trying to do is identify the records where [O3ppm] is zero and update that
value by averaging the hour before and the hour after. So for hour 1100, the
value should be 0.04 by averaging the [O3ppm] value for the 1000 hour(before)
and the 1200 hour(after).
Any help is greatly appreciated.

Here is a sample of the data:


[ID] [DATE] [TIME] [O3ppm]
30898754 7/1/2005 0 0.02
30898755 7/1/2005 100 0.02
30898756 7/1/2005 200 0.01
30898757 7/1/2005 300 0.02
30898758 7/1/2005 400 0.01
30898759 7/1/2005 500 0.02
30898760 7/1/2005 600 0.02
30898761 7/1/2005 700 0.03
30898762 7/1/2005 800 0.01
30898763 7/1/2005 900 0.03
30898764 7/1/2005 1000 0.02
30898765 7/1/2005 1100 0
30898766 7/1/2005 1200 0.06
30898767 7/1/2005 1300 0.04
30898768 7/1/2005 1400 0.04
30898769 7/1/2005 1500 0.05
30898770 7/1/2005 1600 0.04
30898771 7/1/2005 1700 0.03
30898772 7/1/2005 1800 0.03
30898773 7/1/2005 1900 0.02
30898774 7/1/2005 2000 0.04
30898775 7/1/2005 2100 0.03
30898776 7/1/2005 2200 0
30898777 7/1/2005 2300 0.03
Click to expand...
Click to expand...
Click to expand...
 
B

brhall

Ah, of course. I didn't catch that.

Thanks Dale, worked beautifully.

Dale Fye said:
Your table name contains a capital O rather than a zero (0). Should the
field name contain an O as well?

That is the only thing that I can see that might be causing that particular
message.

Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



brhall said:
I have this currently but it is prompting for a parameter value for T2.03ppm:

SELECT T1.ID, (SELECT Avg(T2.[03ppm])
FROM [tblO3data] AS T2
WHERE T2.ID BETWEEN T1.ID - 1 AND T1.ID + 1
AND T2.ID <> T1.ID) AS Avg03ppm
FROM tblO3data AS T1
WHERE ((([T1].[03ppm])=0));




brhall said:
Dale,
Thanks for the reply. Calculating values this way is a federal guideline
that I must use but I've already identified all those records that are zero
for reference. I created a boolean field to let the user know if the value
was created or not.

I tried the first query becuase my records are sequential by the ID but with
no luck. The name of my table is [tblO3data]. Will this change the SQL at
all? (other than "yourTable")


:

It looks to me like you are about to do something you shouldn't.

It looks like you are trying to coverup the fact that someone failed to take
a reading when they were supposed to. I think it would be far more
appropriate to create a query that gives you this information, including the
average value with some sort of annotation to indicate that it is in fact an
average of surrounding values.

If your records are actually sequential, you might be able to do this.

SELECT T1.ID, (SELECT Average(T2.[03ppm])
FROM yourTable T2
WHERE T2.ID BETWEEN T1.ID - 1 AND T1.ID + 1
AND T2.ID <> T1.ID) as Avg03ppm
FROM yourTable T1
WHERE T1.[03ppm] = 0

Then you could create a second (update) query that links your table to this
query and updates based on the ID value linkage.

If, the ID values are not sequential, then it will be more complicated:

SELECT ID, (SELECT Average(T2.[03ppm])
FROM yourTable T2
WHERE T2.ID
BETWEEN (SELECT Max(ID)
FROM yourTable T3
WHERE T3.ID < T1.ID)
AND (SELECT Min(PatientID)
FROM yourTable T4
WHERE T4.ID > T1.ID)
) as Avg03ppm
FROM yourTable T1
WHERE T1.[03ppm] = 0

and if there is a chance that you have more than two consecutive 0 readings,
and want to average the two non-zero values, then you will have to modify the
WHERE clauses of the T3 and T4 subqueries above to include:

WHERE T3.ID < T1.ID
AND T3.[03ppm] <> 0

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I've got an intersting update query that I can't seem to figure out. What I
am trying to do is identify the records where [O3ppm] is zero and update that
value by averaging the hour before and the hour after. So for hour 1100, the
value should be 0.04 by averaging the [O3ppm] value for the 1000 hour(before)
and the 1200 hour(after).
Any help is greatly appreciated.

Here is a sample of the data:


[ID] [DATE] [TIME] [O3ppm]
30898754 7/1/2005 0 0.02
30898755 7/1/2005 100 0.02
30898756 7/1/2005 200 0.01
30898757 7/1/2005 300 0.02
30898758 7/1/2005 400 0.01
30898759 7/1/2005 500 0.02
30898760 7/1/2005 600 0.02
30898761 7/1/2005 700 0.03
30898762 7/1/2005 800 0.01
30898763 7/1/2005 900 0.03
30898764 7/1/2005 1000 0.02
30898765 7/1/2005 1100 0
30898766 7/1/2005 1200 0.06
30898767 7/1/2005 1300 0.04
30898768 7/1/2005 1400 0.04
30898769 7/1/2005 1500 0.05
30898770 7/1/2005 1600 0.04
30898771 7/1/2005 1700 0.03
30898772 7/1/2005 1800 0.03
30898773 7/1/2005 1900 0.02
30898774 7/1/2005 2000 0.04
30898775 7/1/2005 2100 0.03
30898776 7/1/2005 2200 0
30898777 7/1/2005 2300 0.03
Click to expand...
Click to expand...
Click to expand...
 
Top