Multiple Tables in a Query

G

GLS

I work in a hospital which has numerous sites where all
the data is collected in exactly the same format but in a
different table for each site. When I want to run the
same query over a number of different sites I have to add
the new table to the query then change each individual
column to the new table then delete the original table
from the query. There must be a quicker way of doing this
so I can change all the columns (sometimes as many as 20 -
30)in one go.

Can anyone advise please?

Thanks
GLS
 
P

Paul

Assuming the columns names are the same for each table, and that you have a
table with a tablename record for each of those tables, you could write code
in VBA to automatically change the SQL for the query. How you intend to use
the query results makes a difference, so, more info would be helpful.

--
Michael Badnarik for President '04
Libertarian...the freedom party
www.lp.org
www.badnarik.org

"If you are in prison and your chances are 50% for execution by electric
chair, 45% for execution by lethal injection, and 5% for escape, are you
just going to vote for the chair because it is the likeliest outcome?" Vote
Libertarian and live.
 
G

Guest

Thanks for replying Paul.

The table records are identical its just the table name
that differs, so, in each column I have to change it from,
say, TableA 'Surname' to TableB 'Surname', and so on
across the whole query.

The nature of the query would be to extract the same
information across all sites/tables.

Hope this helps
GLS
 
P

Paul

What I was trying to find out is, what are you really doing with the data
extracted, i.e., merely reviewing it, appending it to another table,
deleting it, updating it?

--
Michael Badnarik for President '04
Libertarian...the freedom party
www.lp.org
www.badnarik.org

"If you are in prison and your chances are 50% for execution by electric
chair, 45% for execution by lethal injection, and 5% for escape, are you
just going to vote for the chair because it is the likeliest outcome?" Vote
Libertarian and live.
 
G

Guest

I would be merely reviewing it as in finding out, for
instance, how long patients had waited for treatment (a
major political issue in the UK)or how long they had
stayed in hospital etc. Then I would put it all in an
Excel spreadsheet and use a pivot table to summarise.

GLS
 
P

Paul

If you are selecting ALL the columns, the easiest way to do this would be to
change to SQL view and revise the SQL. For example,

SELECT * FROM tableA

becomes

SELECT * FROM tableB

If you are only selecting certain columns, it may be easier to copy the SQL
to notepad, use the replace function, and then paste the SQL back and run
the query.

--
Michael Badnarik for President '04
Libertarian...the freedom party
www.lp.org
www.badnarik.org

"If you are in prison and your chances are 50% for execution by electric
chair, 45% for execution by lethal injection, and 5% for escape, are you
just going to vote for the chair because it is the likeliest outcome?" Vote
Libertarian and live.
 
J

Jamie Collins

I would be merely reviewing it as in finding out, for
instance, how long patients had waited for treatment (a
major political issue in the UK)or how long they had
stayed in hospital etc. Then I would put it all in an
Excel spreadsheet and use a pivot table to summarise.
Click to expand...

If the column definitions are the same across the tables, you should
be able able to UNION them e.g.

SELECT Surname FROM Guys
UNION ALL
SELECT Surname FROM GreatOrmondStreet
UNION ALL
SELECT Surname FROM HolbyCity
;

Jamie.

--
 
Top