Pulling data from multiple tables of same structure

[jburke]

I have three tables that are similarly structured. I need to pull data
from all three tables, and create a report that details those record
that meet selected criteria.

I know that I could create one make table query and two append queries,
with a resulting table containing the appropriate data. However, I
have multiple users in this database, and don't need to have three
people trying to run the same make table query at the same time. Also,
I'm sure that there is an easier way.

How do I create one query that pulls records from three data sources
that all have the same criteria? Can it be done?

Thanks in advance for your help!

JBurke

 

[Jeff Boyce]

Are you saying that you have three roughly identical tables, each with
different data? If so, why? If you want to make the best use of Access'
features and functions, your data needs to be well-normalized... and three
"similarly structured" tables may not be.

If you absolutely must work with the three tables as is, take a look at
Access HELP on the topic of UNION queries. The basic idea is that you
create a separate query against each table, then "stitch" them together to
show the union of the results.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Marshall Barton]

I have three tables that are similarly structured. I need to pull data
from all three tables, and create a report that details those record
that meet selected criteria.

I know that I could create one make table query and two append queries,
with a resulting table containing the appropriate data. However, I
have multiple users in this database, and don't need to have three
people trying to run the same make table query at the same time. Also,
I'm sure that there is an easier way.

How do I create one query that pulls records from three data sources
that all have the same criteria? Can it be done?

It sounds like you want to se a UNION or UNION ALL query:

SELECT A.* FROM A WHERE . . .
UNION ALL
SELECT B.* FROM B WHERE . . .
UNION ALL
SELECT C.* FROM C WHERE . . .

 

[csiqa]

Well, the reason that I am working with three tables that are identical
is because they are tables that I am linked to. I am working in an
office that has three databases because we have three contracts. The
data for each contract must be maintained in a separate database to
prevent cross contamination of data; however, the structure of the
three databases is the same.

So, if I want to pull an open inventory report, I have to pull one for
contract A, contract B, and contract C. However, our supervisors don't
think in terms of three contracts but in terms of the team that they
manage. Their team members could have work from any or all of the
three contracts. So, if I want a report of workload for Susie's team,
I have to three queries. However, Susie would like to have all of this
on one report instead of three.

I will try the union queries. Wish me luck!

 

[Jeff Boyce]

Good luck!

I'll point out that there are any number of databases (Access and others)
that keep information separated, but don't have to use separate databases to
do that.

At my day job, I've written a contract management system that holds over
1,000 contracts, keeping each one's information separate, but still using
only a single database.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[csiqa]

Well, technically, I'm sure that the db mgr has everything in one db on
the SQL server. However, because the data is protected by that dreaded
HIPAA, and not all employees have a "need to access" all of the data,
he created different views to the SQL tables separated by contract so
that he could control who has access to which views. For example,
there are actually 5 contracts contained in the SQL db, but I only have
access to views for data for 3 of the contracts, because the other 2
are not within my jurisdiction.

A whole lot of useless explaining, but thought I would at least tell
you that I understand why he set it up that way, even though it is a
pain!