change query's table

[Guest]

I have a query that I want to use on another table that has identical fields.
How can I change the underlying table and save as....

 

[Guest]

Copy and Paste the query as a new query. Then, edit your query in SQL view.

Say your query looks something like this:

SELECT [table 1].[column 1], [table 1].[column2]
FROM [table 1]
GROUP BY [table 1].[column 1], [table 1].[column2];

Simply change all references to [table 1] to [table 2], like so:

SELECT [table 2].[column 1], [table 2].[column2]
FROM [table 2]
GROUP BY [table 2].[column 1], [table 2].[column2];

 

[Guest]

Actually, I found that if I add the desired table in design view then delete
the old table I accomblish what I want, I think.

Copy and Paste the query as a new query. Then, edit your query in SQL view.

Say your query looks something like this:

SELECT [table 1].[column 1], [table 1].[column2]
FROM [table 1]
GROUP BY [table 1].[column 1], [table 1].[column2];

Simply change all references to [table 1] to [table 2], like so:

SELECT [table 2].[column 1], [table 2].[column2]
FROM [table 2]
GROUP BY [table 2].[column 1], [table 2].[column2];

I have a query that I want to use on another table that has identical fields.
How can I change the underlying table and save as....

 

[John Spencer]

Save the query as it exists to your new name
In DESIGN VIEW
-- Right Click on the TableName in the top section and select Properties
-- Change the table name ALIAS to your new table name
-- Select VIEW: SQL from the menu
-- Edit the FROM clause to remove the original table name and the word AS
Sample: SELECT ... FROM MyOriginalTable as MyNewTable becomes
SELECT ... FROM MyNewTable
-- That should do it

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..