Query Multiple Tables

  • Thread starter Thread starter John C.
  • Start date Start date
J

John C.

I have 3 tables that are identical. Keeping the
information seperate is needed due to location of the
offices and amount of data.

I would like to query all 3 tables, searching for [Author]
criteria.

I have no clue where to begin.

Thank you in advance.
 
John,

A couple of ideas:

1. Make a Union Query to combine the results from all 3 tables. The
SQL view would be a bit like this...
SELECT * FROM Table1 WHERE Author = "Fred"
UNION SELECT * FROM Table2 WHERE Author = "Fred"
UNION SELECT * FROM Table2 WHERE Author = "Fred"

2. Make a master table with the same structure as the three existing
tables, run an Append Query to add the data from all 3 tables to the
master table, and then use a simple Select query on this combined
data.

- Steve Schapel, Microsoft Access MVP
 
John

Along with Steve's ideas, I would ask if you have three separate physical
databases, or a single database in which you work with information about
three separate physical locations. You also mention that "amount of data"
is a concern -- is this based on actual performance issues, or on the
possibility of performance issues.

It may be that your data structure could be simplified, making your query
task(s) much simpler in the process.

If you'd like additional ideas, provide a bit more information about your
data, table structure, and database design (i.e., split into
front-end/back-end, separate physical databases in each location, etc.).
There is also a "replication" newsgroup with help on setting up a
main/parent database replicating copies out, and merging changes back in.

Good luck

Jeff Boyce
<Access MVP>
 
The tables are contained within one database. Technicians
at each of the three locations make DB entries for their
respective location. Technicians move among all 3
locations.

The amount of data is a concern due to the number of
entries made during the year.

Tables:
Location1
Location2
Location3

Fields:
Date
Time
Author
Comment
System
Location
 
John
The tables are contained within one database. Technicians
at each of the three locations make DB entries for their
respective location. Technicians move among all 3
locations.
Click to expand...

I still don't have a clear picture -- do the technicians do the data entry
FROM the locations, or at a central site, into the "one database"?
The amount of data is a concern due to the number of
entries made during the year.
Click to expand...

My previous question stands -- are you concerned because YOU think there are
a lot of entries, or have you noticed a performance issue?

I ask because a more normalized design could save you a lot of work. If
there are performance issues, what kind? There may be indexing and other
approaches that can keep performance acceptable without the separate table
structure...

Jeff Boyce
<Access MVP>
 
Anyway of making a query that would give the same results
as the SQL you gave?
Click to expand...

Umm... what he gave you IS a Query. A Query is actually stored as a
SQL string. The query grid is simply a tool that lets you build SQL
strings; it's limited (in particular, you cannot create a UNION query
in the grid but must use SQL view).
 
John,

I had interpreted your initial question that you had 3 separate
databases in the 3 locations. Now that I know it is 3 tables within
the one database file, I support the line of approach being taken by
Jeff. It is very unlikely that there will be any advantages to the 3
separate tables design.

- Steve Schapel, Microsoft Access MVP
 
John,

Just to add to John Vinson's comments, to make a Union Query follow
these steps...
1. From the database window, select the Queries tab, click New, and
then select Design View.
2. Close the 'Show Table' window
3. From the Query menu, select SQL Specific, and then Union
4. Type in the SQL of the query.

- Steve Schapel, Microsoft Access MVP
 
The techs perform entries from several workstations from
each location.

It is what I perceive will be a performance issue. Did
have the entries going to one table, noticed a sluggish
response when performing reports or displaying the data
for respective locations.
 
The techs perform entries from several workstations from
each location.
Click to expand...

I'm not there, so I can't see how you have it set up. Are there separate
databases, or separate tables? If the techs do data entry "from several
workstations from each location", are they using separate copies of the
database, are they linked "front-end" to a single "back-end" database (and
if so, over phone lines, via a network, ...?)?

Sluggish performance could be due to insufficient indexing.

Jeff Boyce
<Access MVP>
 
One database, separate tables for each location.

Common copy of the program, residing on a LAN server.

Use is over the internal LAN system.
 
John

Thanks for your patience while I build a better picture. My comments are
based on my best understanding:

< You have a single database (.mdb file), located on a LAN server.
< That database has three "location" tables.
be using the application simultaneously, you don't need to worry about the
following -- conversely, if more than one tech simultaneously "hits" your db
over the network, you WILL end up with a corrupted database, sooner or
later.use

{A search through the .tablesdbdesign newsgroup, or at Google.com on
"corruption" will reveal that unsplit dbs, and multiple users "sharing" a
single front-end are common causes, along with "noisy" NICs and LAN
cabling.}

< You haven't mentioned the volume of data entry yet ... but if you only
have three techs doing it, and they work sometime in addition to data entry,
my guess would be your db is having less than 10,000 records a year added.
Unless each of your rows is very large (you haven't provided information
about what data is stored in the tables), this wouldn't add more than 10
Mbytes per year. For Access, this is not unreasonable. If your numbers are
appreciably larger than this (5-10 times larger), you may need to consider a
more robust back-end -- of which SQL Server is one example.

< I don't recall if you mentioned how many fields in any single table could
hold your searched criterion, but your use of the square brackets around
[Author] implies that there's only one field in each table. Again, I don't
have a complete picture, so the following alternate design is for
illustration purposes only:

tlkpLocation {holds your locations, currently three, who knows how many
later?)
LocationID (primary key, needs to be unique, now and in the future -
Autonumber?)
LocationName
... (any other facts about a location)

tblWhateverYouAreStoringThatYourEmailWasAbout
tblWhateverID (primary key, ?Autonumber)
LocationID (foreign key, from tlkpLocation) - which Location is this
record for?
Author (?AuthorID, foreign key, from tblAuthor?)
... (any other facts about WhateverYouAre...)
(!LocationID in tblWhateverYou... comes to mind)
are joining, then go to the Relationships window and create those
relationships, enforcing referential integrity (don't bother setting
cascading updates or deletes at this point). This will create (internal)
indices on those fields as well. If you do this between tlkpLocation and
tblWhatever..., you DON'T need to set an index in tblWhatever... on the
LocationID field -- Access already has, and a duplicate index only wastes
space.

Since you have already, earlier in this thread, come up with a UNION query
than can "collect" all three tables' data together, it would be trivial to
build a make-table query, based on the UNION query, and use that new query
to build a test table. Go into design mode on the test table and set your
indices, re-define field types, etc.

Re-create your original (on a single table) query on this test table and see
if there's a performance issue.

Again, thanks for your patience, and good luck!

Jeff Boyce
<Access MVP>
 
Back
Top