Recordset or tblTemp for aggregating data

[hkaing79]

I have a table of data that I would like to remain untouched. From this data, I need to calculate averages disaggregated by various fields. Instead of playing with the WHERE to filter the data, I would like to use SQL to delete those records from a copy of the table.

Using a temporary table, I would make a copy of the table, delete the records using several DELETE statements, then run a SELECT INTO statement to aggregate the data into a table.

I read in other threads that doing so will bloat the database. Instead of copying the table into a temporary table, can I create a copy using DAO recordset, delete the data from the recordset, aggregate the recordset, and then copy the results into a table?

If so, how would I go about working with recordsets?

I guess I'm asking how would I do this (oversimplification):
Function Test()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblTest")
CurrentDb.Execute "DELETE FROM " & rs & " WHERE Field4=1"
CurrentDb.Execute "SELECT * INTO tbltmp FROM rs "
End Function

 

[mp9]

Why not just use one SQL statement to do what you want, i.e.

Function Test()
    CurrentDb.Execute("SELECT tblTest.* INTO YourNewTableNameHere FROM tblTest WHERE tblTest.Field4)<>1")
End Function

Hope this helps.

http://www22.brinkster.com/accessory/

 

[hkaing79]

There's a lot of possible filters. Instead of creating a long WHERE expression, I thought it would be easier to just use multiple DELETE statements.

I ended up going the WHERE route, but I'm still curious how to use SQL with recordsets, i.e. SELECT INTO or DELETE.