Query for count of temperature ranges

[sonofroy]

I am not sure if I can do this but I have this data in my table

Date1 Shift1 Temperature
Date1 Shift2 Temperature
Date2 Shift1 Temperature
Date2 Shift2 Temperature
etc...

What I will like to do is to come up with a count when the temperature for
one shift is above 32 degrees and the next shift falls below 32 degrees.

Is this possible?

Thanks in advance

 

[ghetto_banjo]

There may be a smarter way of doing this in a single query, but this
will work.

Assuming you always have exactly 2 shifts (shift1 and shift2) in a
given day:

Use a Make-Table query with Criteria of shift = 'Shift1' and
Temperature >32 (aka all shift ones that were above 32 degrees)

Then build a query that links your main table to this new table via
the Date. Choose Shift and Temperature from the MAIN table, and set
criteria to 'Shift2' and <32 respectively. the number of results this
query returns is your answer.

 

[John Spencer]

What type of field is the shift field and what values are stored in the field?
Assuming that Shift is a number value of 1 or 2 then you could possibly use
the following untested SQL statement.

SELECT Count(*)
FROM

as A INNER JOIN

as B
ON (A.[Date] = B.[Date] AND A.Shift = B.Shift-1)

OR (A.[Date] = B.[Date]-1 and A.[Shift]-1 = B.Shift)
WHERE A.Temperature > 32 and B.Temperature < 32


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County