Getting an average of a subsample ...still

Results 1 to 2 of 2

Thread: Getting an average of a subsample ...still

  1. #1
    Join Date
    Dec 1969

    Default Getting an average of a subsample ...still

    Hi,<BR>I&#039;m back, and at the end of my rope (as I generall am before I post here).<BR><BR>A few days ago, Bill helped me figure out how to find a "subsample average" that is, the average of only the last X values for a field. I have a date field, so I&#039;m using this to compare the most recent values with the overall average.<BR><BR>Anyways, with Bill&#039;s help, I got this SQL working just fine on a small (sample), 12 record database using Access&#039;s Design View:<BR><BR>SELECT Avg(data.param1) AS recentavg<BR>FROM data<BR>WHERE (((data.Date) In (SELECT TOP 3 FROM data Order by DESC)));<BR><BR>When I ported it over to my real DB (~7000 records), it freezes up - no error message, Access just freezes. I tried chopping it up a bit and found that if I just ask it to return records (and not calculate an average), it works like a charm. Is this freezing because my database is too big? If so, any suggestions? I didn&#039;t think 7000 records was big...<BR><BR>Just to make things more confusing, I would also like to limit the query above to only look at records that contain values (count &#062; 0) My table has many blank cells and I don&#039;t want a non-value contributing to my average... Any thoughts?<BR><BR>I will be eternally indebted to you...<BR>Mike

  2. #2
    Join Date
    Dec 1969

    Default RE: Getting an average of a subsample ...still

    7000 records don&#039;t seem to much to me, you could try to &#039;compact and repair&#039; the database, this is an option in access.<BR><BR>To include only records where a certain value &#062; 0 add it to your where clause<BR><BR>WHERE (((data.Date) In (SELECT TOP 3 FROM data Order by DESC))) AND (thefield &#062; 0)<BR>

Posting Permissions

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