Hello All,
I have a database which has > 100,000 records. I would like to write a query to average the fields at a 10 to 1 ratio so that when I'm done I have an output table that has about 10,000 records. I have no clue how to do this. Any suggestions?

Marty

KKilfoil Asked:

Do you mean that you wish to create a new 10,000 record table where the first record contains the average of the values from records 1-10 of your existing table, the second record in the new table contains the averages of records 11-20 from old table, etc?
I am curious what this new table might be used for?


YES! That's exactly what I'm looking to do. So far the only thing I could come up with is a looping append query but I do not know how to do this. I'm using it to reduce GIS (Geographic Information System) point data. This is data is used for the mapping industry; specifically traffic data.



[This message has been edited by msprenz (edited 12-11-2001).]

Do you mean that you wish to create a new 10,000 record table where the first record contains the average of the values from records 1-10 of your existing table, the second record in the new table contains the averages of records 11-20 from old table, etc?

I am curious what this new table might be used for?

msprenz: it would be better to post a REPLY rather than editing your original post. That would have put your reponse last in the thread, where I look for replys. I hadn't noticed your response until just now.

Without getting too specific with the statistical mathematics, choosing a random 10% of the records from 100,000 records might serve your purpose just about as well as an (arbitrary?) average. If so, have you looked at the 'select TOP 10 PERCENT' predicate available in a query?

You will have to make a query to generate random numbers for each record, and then create a query with 'TOP 10 PERCENT' added after the SELECT keyword in SQL view, using the first query as the datasource.

Finally, if you want the particular 10 percent of the records to be persistent, you can create a make-table query using the last query I descibed as the data source. Once you run the query, the new table will contain 'static' data until you rerun the query.

As you might notice, I prefer multiple simple queries to big complex ones, because troubleshooting for me is much easier.