table grid

[subs]

in a query in design grid, i want to change the source of the query
ie. the table. how would i do that without eliminating the fields of
the table.

When i remove the table, the fields in the grid also get removed. so
is there to keep those field while bring up a new table. since the
fields in both the table are same.

 

[tina]

well, there's no quick and easy way from Design view, or from SQL view, that
i know of - not without using an outside find/replace utility. you might
open the query in Design view, change to SQL view (menu bar: View | SQL
View), copy the *complete* SQL statement and paste it into an MS Word
document, then use Find/Replace on the tablename. then copy the *complete*
SQL statement from Word, and paste back into the SQL view pane, completely
erasing the original SQL statement. yeah, it's cludgy - but my only other
suggestion would be to do much the same find/replace thing in VBA code,
which is perhaps a bit above your skill level right now.

having said all of the above, i must add that your statement "the fields in
both the table are same" raises a big red flag. if you have more than one
table with all, or most, of the fields identical, it's probable that your
tables/relationships structure is incorrect. recommend you read up on
relational design principles and then re-examine your structure, before you
go any further in developing your database. for more information, see
http://home.att.net/~california.db/tips.html#aTip1.

hth

 

[KARL DEWEY]

Add new table before removing old. Click on table name pull down below field
name and select new table name. Copy new table name and paste for rest of
fields.

Why not have one table with an additional field to identify the data set and
just use criteria on that field to pull the correct records?

 

[John Spencer]

If you build the query in the sql view and alias the table this can be
fairly simple.

SELECT S.FieldA, S.FieldB, S.FieldC
FROM [Your First Table] as S

Then all you need to do to use a different table with the same exact
field names and structure is replace [Your First Table] with [Your other
table].

The easy way to do this if you already have designed the query using the
design grid.

-- Click on the table and show the properties
-- Enter a new name for the table in the Alias box
-- Save the query
-- Switch to SQL view (Menu: View: SQL)
-- Replace the existing tablename with the new tablename

The real question here is why do you have to identical tables. That is
almost always a bad design for a relational database. It usually means
you should have one table with an additional field to capture whatever
makes the data go into one table and not another.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================