Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Intermittent Query Error - Time Functions? (Access 2K)

    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

    <pre>SELECT Format$([date],"mmm") AS [Month], IIf([hour]<#12/30/1899 22:0:0# And
    [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 22:0:0# And
    [hour]>=#12/30/1899 6:0:0#,"HLH","LLH"),
    Partition([temp]+100,51,150,5), Partition([RelHum],1,100,10);
    </pre>



    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Intermittent Query Error - Time Functions? (Access 2K)

    I can reproduce the error. You can avoid it by using 6/24 instead of #6:00 AM# and 22/24 instead of #10:00 PM#:

    SELECT Format$([date],"mmm") AS [Month], IIf([hour]<22/24 And [hour]>=6/24,"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]<22/24 And [hour]>=6/24,"HLH","LLH"), Partition([temp]+100,51,150,5), Partition([RelHum],1,100,10);

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Intermittent Query Error - Time Functions? (Access 2K)

    Second question: I think it's worthwhile trying both options and see which works best.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Intermittent Query Error - Time Functions? (Access 2K)

    Hans - thanks for your help. I am glad that you can reproduce the error - it is at least an Access peculiarity, not a personal peculiarity! I will take your advice on testing one XL Pivot Table method against the other; I had thought that I might do that anyway, and I expect that I will run a loop to refresh the pivot table -say- one hundred times and then compare the results. In fact, it doesn't really matter since I will only need to get the data into Excel once - but I will lose my geek status if I don't worry about such things!

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Intermittent Query Error - Time Functions? (Access 2K)

    For those interested, the update times between the two methods were different enough that they were evident on a single iteration - no need to cycle through a hundred updates or anything. A Pivot Table that referenced a query that presented the summary data (ie - ~1200 records, each of which includes a number indicating the number of records with the same characteristics that are found in the base table in Access) updates in about 4 seconds, while a PT based on a query that returns a single record for each of the base-table records indicating the characteristics of interest takes about 9 seconds to update or refresh. I tested by refreshing each table a couple of times, and then by changing some of the fields in the Access queries, saving them, and then refreshing the Pivot Table again.

    I haven't compared to basing the PT on a table created from either of these queries - that's next, I guess.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •