I have a data table with records of temperature and humidity observations over a period of time (eventually, it will have ~430,000 records, but at the moment it contains about 130,000). Part of my task is to transfer this data to a pivot table, with the time of the observation characterized as either a “Heavy Load Hour” (between 6:00h and 22:00h) or a “Light Load Hour” (any other time). I have written a query that does what I want, but it seems to act strangely. When the query has been saved it can be opened and run – but after being run a couple of times, it hangs and Access reports a syntax error if I try to save it again, or sometimes when I run it or try to go into SQL-View mode (zipped jpg of the query grid and syntax error attached). Closing the query without changing it and re-opening the saved version allows it to run another couple of times. It seems random whether the query runs once or twice before this occurs, although there may be a pattern to it that I just don’t see. The error message truncates the expression that Access is objecting to – I don’t know whether this is a limitation in the way Access reports errors, or Access is truncating the expression before it tries to evaluate it, thus leading to the error.
The Hour field is saved as a “time” – a value from 0 to 1 representing fractional parts of a day – if need be I could convert them all to numbers from 0 to 23 (the data is recorded hourly on the hour) if that is part of the problem. The SQL for the query is below:
qryAggregate2
returns count of observations in each temp/humidity bin, by Month & HLH/LLH
SELECT Format$([date],"mmm") AS [Month], IIf([hour]=#12/30/1899 6:0:0#,"HLH","LLH") AS Load, Partition([temp]+100,51,150,5) AS TBin, Partition([RelHum],1,100,10) AS HBin, Sum(1) AS Dummy FROM AllWeather WHERE (((AllWeather.Temp) Is Not Null) AND ((AllWeather.RelHum) Is Not Null)) GROUP BY Format$([date],"mmm"), IIf([hour]=#12/30/1899 6:0:0#,"HLH","LLH"), Partition([temp]+100,51,150,5), Partition([RelHum],1,100,10);
Assuming I can get by this error, I have another question – as set out, this query presents the total number of observations for each combination of month, High or Low load hour, and temperature and humidity ranges, and returns about 1200 records. My initial plan was to use this as a data source for an Excel pivot table. The alternative is to change this query from a total query to a select query, and have it return the 130K records (eventually 430K), each tagged by month and HLH/LLH – this could also be the source for an Excel pivot table. Does anyone know if there will be speed differences in the two approaches, or whether it would be faster to change the existing aggregation query into a make-table query to create a table with the ~1200 records, but now existing in a “crystallized” state as a seperate table. That table would have to be updated if new data were added, but that is a bit of a one-off, anyway.
