Counting hours in day

[Guest]

I have a database with hourly temperatures (over many months) and would like
to do a query that shows how many hours during the day this temperature was

13 degrees, >18 degrees and >20 degrees. I also have a query where it gives

the max, min and ave but when I try to do a count it just gives me 24 for
each. How do I get it to count the hourly temps instead of the date?

 

[OfficeDev18 via AccessMonster.com]

Do you GROUP BY the hour? That will produce a Count() of 24. If you lose the
GROUP BY of the hour, and just Count(HourEntry), what happens now?

Sam

 

[Guest]

What I did was have each hourly temp have a month/day column, so I could get
the average, max and min for each day. Now I also want the number of hours
per day that the temp is less than a certain number. When I try to do this,
I get some numbers that I don' t know what they refer to. II did group by
the day as the hour of the day is not there, just 24 different temps. When I
just do count it tells me there are 1600 temps.

 

[Guest]

What is the structure of your table?

I have a database with hourly temperatures (over many months) and would like
to do a query that shows how many hours during the day this temperature was
the max, min and ave but when I try to do a count it just gives me 24 for
each. How do I get it to count the hourly temps instead of the date?

 

[Guest]

It is stream temperatures I am working on so the stream name is the first
column (repeated for every hour) followed by the date (mm/dd/yy), date
(mm/dd), time, celsius temp, fahrenheit temp. The query will be set up with
the stream name, date (mm/dd), min celsius, max celsius, ave celsius, Degrees
Below 13 degrees, Degrees below 18 degrees, Degrees above 20 Degrees. I can
get the min, max, and ave per day temps to work.

 

[John Spencer]

Use something like the following.

Field: CountHoursBetween13And18: Abs(Sum(Celsius >=13 and Celsius<18))
Field: CountHoursBetween18And20: Abs(Sum(Celsius >18 and Celsius<=20))

Field: CountHoursOver20: Abs(Sum(Celsius>20))

 

[Guest]

I am just learning Access so I don't really know what you are meaning. I
tried doing a query using the following fields: 1. Stream Name 2. Month/Day
3. Temperature. When I put this in the field column, an error comes up.

 

[Guest]

I can get a daily count for one of my variables using count and where, but it
only answers one at a time (<13, >20 or<18) Also if say there were no temps
greater than 20 degrees on a given day it will not say 0. So I would like it
to answer my question in one nice and easy step. Thanks for your help!

 

[John Spencer]

Dear Otyokwa,
Please copy and post the SQL of your query that is working. I will try to
modify it for you.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

 

[Guest]

SELECT Warnicke03.[Stream Name], Warnicke03.[Month/Day],
Min(Warnicke03.Celsius) AS Minimum, Max(Warnicke03.Celsius) AS Maximum,
Avg(Warnicke03.Celsius) AS Average, Count(Warnicke03.Celsius) AS [Hours <13],
Count(Warnicke03.Celsius) AS [Hours Between 13 and 18],
Count(Warnicke03.Celsius) AS [Hours Greater Than 18]
FROM Warnicke03
WHERE (((Warnicke03.Celsius)<=13) AND ((Warnicke03.Celsius)>13 And
(Warnicke03.Celsius)<18) AND ((Warnicke03.Celsius)>18))
GROUP BY Warnicke03.[Stream Name], Warnicke03.[Month/Day];

I can get the min, max and ave to run and can get each hour count to run
separately, but they won't show a 0 where there is no data.

 

[John Spencer]

SELECT Warnicke03.[Stream Name], Warnicke03.[Month/Day],
Min(Warnicke03.Celsius) AS Minimum, Max(Warnicke03.Celsius) AS Maximum,
Avg(Warnicke03.Celsius) AS Average
, Abs(Sum(Warnicke03.Celsius<=13)) AS [Hours <13]
, Abs(Sum((Warnicke03.Celsius >13 and Celsius <=18)) AS [Hours Between 13
and 18]
, Abs(Sum(Warnicke03.Celsius >18)) AS [Hours Greater Than 18]
FROM Warnicke03
GROUP BY Warnicke03.[Stream Name], Warnicke03.[Month/Day];

Celsius <= 13 returns either 0 or -1 (False or True)
Sum totals the values returned
Abs removes the minus sign

Another way of doing this is to use
Count(IIF(Celsius<=13,1,Null))

Since the count function counts all non-null values.
SELECT Warnicke03.[Stream Name], Warnicke03.[Month/Day],
Min(Warnicke03.Celsius) AS Minimum, Max(Warnicke03.Celsius) AS Maximum,
Avg(Warnicke03.Celsius) AS Average
, Count(IIF(Celsius<=13,1,Null)) AS [Hours <13]
, Count(IIF(Celsius >13 and Celsius <=18,1,Null)) AS [Hours Between 13 and
18]
, Count(IIF(Celsius >18,1,Null)) AS [Hours Greater Than 18]
FROM Warnicke03
GROUP BY Warnicke03.[Stream Name], Warnicke03.[Month/Day];

SELECT Warnicke03.[Stream Name], Warnicke03.[Month/Day],
Min(Warnicke03.Celsius) AS Minimum, Max(Warnicke03.Celsius) AS Maximum,
Avg(Warnicke03.Celsius) AS Average, Count(Warnicke03.Celsius) AS [Hours
<13],
Count(Warnicke03.Celsius) AS [Hours Between 13 and 18],
Count(Warnicke03.Celsius) AS [Hours Greater Than 18]
FROM Warnicke03
WHERE (((Warnicke03.Celsius)<=13) AND ((Warnicke03.Celsius)>13 And
(Warnicke03.Celsius)<18) AND ((Warnicke03.Celsius)>18))
GROUP BY Warnicke03.[Stream Name], Warnicke03.[Month/Day];

I can get the min, max and ave to run and can get each hour count to run
separately, but they won't show a 0 where there is no data.

Dear Otyokwa,
Please copy and post the SQL of your query that is working. I will try
to
modify it for you.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message