Saving a query into a Table

Question

I have searched for a question that I am having, though I find parts of the answer I am not able to understand it fully. I am using access2010.

In simple Terms, I want to filter a table [newsearch] to show the results based on my WHERE condition. I can use SELECT and a WHERE condition and I get a result through a query, but I want this result to be saved into [newsearch], that means I want this [newsearch] to contain only the results of this query.

I tried using SELECT INTO but since my source and destination are [newsearch], it does not work.

the query I run now is:

strSQL = "SELECT * FROM [newsearch] WHERE [newsearch].[" & Me.Combo17 & "] = '" & Me.Text18 & "'"
Set qdef = db.CreateQueryDef("User query results", strSQL)
qdef.close
Set qdef = Nothing
Set db = Nothing
DoCmd.OpenQuery "User query results", acViewNormal

This gives me the result in a query table but I want it saved to [newsearch].

How can I do it?

SELECT SOURCE.Field1, SOURCE.Field2 INTO TABLEA FROM SOURCE WHERE SOURCE.field3=xyz — Nathan_Sav
sorry, there was an error in my question. I already tried this but it does not work, as both source and Destination are [newsearch] . So your code would be: SELECT * INTO [newsearch] FROM [newsearch] WHERE [newsearch].field3=xyz. It will not work — saranmc
So you want to add it back into the same table? Or are they in different databases? — Nathan_Sav
i want to add it back to the same table in the same DB. but that table should contain only the results, all previous records have to be erased (so no UPDATE) and this new query results have to be put in. — saranmc

Shadow Shadow · Accepted Answer · 2016-03-10T10:23:52

select into query creates a table, but obviously you cannot create a table that already exists. You have 2 options:

Instead of selecting what you need, delete those records from your table that you do not need: delete from [newsearch] where field3<>xyz
You can use select into to create a new table, then drop [newsearch] and then rename the new table to [newsearch].

Saving a query into a Table

1 Answers