Creating an Access query that searches multiple tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am tryin to create a query that will search through multple tables to
locate production informtion. Each Table has the same format and holds
producton information for each week.
 
if you can't combine the tables, then you could hack it together with a
UNION query

SELECT f1, f2, etc FROM Table1

UNION

SELECT f1, f2, etc FROM Table2

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting
 
The question implies that there are some problems with the table structure
used. Tables should not be used to store data for a week; that makes the
table itself part of the data. There should be a date field along with your
other data, per row of a single table. This avoids this kind of situation
which is messy and inconvenient- at best- to deal with.

Given what you have, you can try a UNION ALL query, but you need to add a
query for each table as you go. In other words, if you have tables for data
from 9-5-2005, 9-12-2005, etc. you need a query for each table as:

SELECT * FROM tbl09052005...
UNION ALL
SELECT * FROM tbl09122005...
UNION ALL
etc.

Every time a new table is added, a new query needs to be added.
 
I am tryin to create a query that will search through multple tables to
locate production informtion. Each Table has the same format and holds
producton information for each week.
Click to expand...

You would need a UNION query combining these tables to do this. See
the online help for UNION.

But what you *really* need to do is redesign your incorrect table
structure. Holding data - dates - in a table name IS SIMPLY WRONG and
will cause you endless problems. Instead, have a Production table with
a production-date field. If you want to see data for a particular week
use a Query selecting records with production dates in that range.

"Too many records" you say? Well, you're mistaken. You have the same
number of records either way; in a single table with an indexed
ProductionDate field you can search them easily; in your case you must
use a much more complex and less efficient UNION query.

John W. Vinson[MVP]
 
Thank you for your response. The programmer set up all the tables or the
year ( he did it this wy so that the tables wouldn't get too big and so the
db down), I am trying to build queries to to select information to make a
report from eg. daily production summaries or customer reports. I am trying
to get the query to search through each table to find the selected
information.
 
John Vinson's reply is correct: the database would have been better off
normalized; the belief that doing so would slow the database down isn't
necessarily so. I'm assuming, though, that by this point it may be too late
or impractical to redesign the schema. If that's so you may have to use the
table union suggestions mentioned.

Randall Arnold
 
Thank you for your response. The programmer set up all the tables or the
year ( he did it this wy so that the tables wouldn't get too big and so the
db down)
Click to expand...

Sorry... but his decision will have exactly the OPPOSITE effect.

Your database, the .mdb file, will be *BIGGER*, slower, less
efficient, and harder to use, since each weekly table will have a
substantial amount of system overhead... and your database will have
exactly the same total number of records.

I'd suggest creating the UNION query, and then basing an Append query
upon it to migrate the data into a normalized single table. To each
SELECT statement in the UNION (this will make sense when you read the
online help) add the date of the table as a calculated field, e.g.

SELECT this, that, theother, #4/21/2005# AS WeekVal
FROM tbl04212005
UNION ALL
SELECT this, that, theother, #4/28/2005#
FROM tbl04282005
UNION ALL
<etc etc>

John W. Vinson[MVP]
 
Thank you for your help. I think I will talk to the programmer and have it
changed. I had originnally had it set up that way. Hopefully its not too
late.

Thanksn again,
Clare
 
Back
Top