Query to search multiple identical tables

[Jim VanGordon]

I have 7 tables that are identical in format, but all
contain different information (Yeah ,yeah...I know I
should probably consolidate but humor me for now.) How
would I go about searching through all my tables to find
one record. Right now I can only search through one at a
time, but I'd like to have a master that will search
through all of them at once.

 

[Van T. Dinh]

Create a Union Query to merge 7 Tables into 1 "virtual Table". You can the
search this "virtual Table" datasheet or you can create a SELECT Query with
the UNION Query as the datasource.

But the proper solution is ...

 

[Jim VanGordon]

I looked in help to find out how to create a union query,
but it said you have to use sql code to create it. I
have no experience with this. I tried based on the Help
and this is what I got. Is anything like this even
possible?

SELECT [New Staking Enigineering Forms].*
FROM [New Staking Engineering Forms]
WHERE [[First Name] [Last Name] [Location]
[HomePhone] [MobilePhone] [Business Phone]] = [Find]
FROM [Search Table]

UNION ALL SELECT [Delbert].*
FROM [Delbert]
WHERE [[First Name] [Last Name] [Location]
[HomePhone] [MobilePhone] [Business Phone]] = [Find]
FROM [Search Table]

UNION ALL SELECT [Darin].*
FROM [Darin]
WHERE [[First Name] [Last Name] [Location]
[HomePhone] [MobilePhone] [Business Phone]] = [Find]
FROM [Search Table]

UNION ALL SELECT [Delbert Has Looked at and Routed].*
FROM [Delbert Has Looked at and Routed]
WHERE [[First Name] [Last Name] [Location]
[HomePhone] [MobilePhone] [Business Phone]] = [Find]
FROM [Search Table]

UNION ALL SELECT [Completed Staking Engineering Forms].*
FROM [Completed Staking Engineering Forms]
WHERE [[First Name] [Last Name] [Location]
[HomePhone] [MobilePhone] [Business Phone]] = [Find]
FROM [Search Table]

UNION ALL SELECT [Call Backs on Jobs].*
FROM [Call Backs on Jobs]
WHERE [[First Name] [Last Name] [Location]
[HomePhone] [MobilePhone] [Business Phone]] = [Find]
FROM [Search Table]
ORDER BY [Last Name], [First Name], [Location];

Thanks,
Jim VanGordon

 

[Anne]

Hey Jim -

If your tables are IDENTICAL this will work


SELECT [New Staking Enigineering Forms].*
FROM [New Staking Engineering Forms]

UNION SELECT [Delbert].*
FROM [Delbert]

If the tables are not identical, you need to replace the *
with each field by name.

Save that query as something like "AllJobs" and any time
you need to search all of the jobs make a query based on
the AllJobs query.

HOWEVER, based on what I can see from the sample code
below, I think you would be MUCH MUCH MUCH better off with
one big table that has all of your records in it. To
differentiate between the various types, you would add a
new field, maybe called JobStatusID? that joins to a
lookup table with the various statuses (New Staking,
Delbert, etc.)

Combining all your tables into one wouldn't take much
work, and it will definitely save you much pain in the
future, especially if you are not good at SQL.

 

[Van T. Dinh]

I wrote that you need to do it in TWO stages: one for the (Select) UNION
Query and one for the SELECT Query with your search criteria.

 

[Jim VanGordon]

Why is it that is asks me for a parameter value in the
first field of the SELECT statement in my UNION query?
Is there any way I can get rid of this? Other than that
it works fine.

Thanks,
Jim

-----Original Message-----
I wrote that you need to do it in TWO stages: one for the (Select) UNION
Query and one for the SELECT Query with your search criteria.

--
HTH
Van T. Dinh
MVP (Access)

I looked in help to find out how to create a union query,
but it said you have to use sql code to create it. I
have no experience with this. I tried based on the Help
and this is what I got. Is anything like this even
possible?

SELECT [New Staking Enigineering Forms].*
FROM [New Staking Engineering Forms]
WHERE [[First Name] [Last Name] [Location]
[HomePhone] [MobilePhone] [Business Phone]] = [Find]
FROM [Search Table]

UNION ALL SELECT [Delbert].*
FROM [Delbert]
WHERE [[First Name] [Last Name] [Location]
[HomePhone] [MobilePhone] [Business Phone]] = [Find]
FROM [Search Table]

UNION ALL SELECT [Darin].*
FROM [Darin]
WHERE [[First Name] [Last Name] [Location]
[HomePhone] [MobilePhone] [Business Phone]] = [Find]
FROM [Search Table]

UNION ALL SELECT [Delbert Has Looked at and Routed].*
FROM [Delbert Has Looked at and Routed]
WHERE [[First Name] [Last Name] [Location]
[HomePhone] [MobilePhone] [Business Phone]] = [Find]
FROM [Search Table]

UNION ALL SELECT [Completed Staking Engineering Forms].*
FROM [Completed Staking Engineering Forms]
WHERE [[First Name] [Last Name] [Location]
[HomePhone] [MobilePhone] [Business Phone]] = [Find]
FROM [Search Table]

UNION ALL SELECT [Call Backs on Jobs].*
FROM [Call Backs on Jobs]
WHERE [[First Name] [Last Name] [Location]
[HomePhone] [MobilePhone] [Business Phone]] = [Find]
FROM [Search Table]
ORDER BY [Last Name], [First Name], [Location];

Thanks,
Jim VanGordon

.

 

[Jim VanGordon]

Ok, I fixed that problem, but I have a new question.
What if I just made a form that used the union quey as
it's source? The only problem is I can't edit the
records. Is there any way to be able to edit those
records via the form using the query and leave the
records in their own tables?

 

[Van T. Dinh]

1. You seem to think Query and Form have their own set of data???

They don't. SELECT Queries simply select data *from Tables* according to
the criteria you specify. Forms *only display* data from its RecordSource,
generally tranced back to Tables. So, when you edit data, you edit data
*from Tables*.

2. UNION Queries are NOT updateable since in a union, JET engine, in
general, cannot identify which Table the Record comes from.

3. I am not sure what you are trying to do but from the description, you
probably need to use the UNION Query as the source for a Make-Table Query to
create a (*temp*) Table and then you can edit data in this temporary Table.
This, of course, violates the Relational Database Design Theory since you
store duplicate data. Hence, I wrote "temp". This leads me to the next
point.

4. Generally, if a database has Tables of similar structure storing the
similar data, the database structure may be incorrect since if you need to
manipulate the data in these Tables at the same time, it is likely that the
data should be in ONE Table, not several Tables.

Have you checked your database against the Relational Database Design
Priciples / Database Normalisation.

 

[Jim VanGordon]

Well, Van, perhaps you didn't take into account that the
users that will be using this don't have a lot of
computer experience. After all, in making any program,
the designer has to take into account the limitations of
the users and, if needed, make it as simple as possible.
This would not include combining everything into one
table and relying on a group of computer illiterate users
to get everything right. Maybe you need to take a step
back and re-evaluate what you think is good design here,
if you think making it more complex for a group of
limited users is better. Just thought that your last
post was a little harsh in tone for someone that does
helps people for a living. I normally don't have any
expectations on message boards but you're supposed to be
and MVP here.

 

[Van T. Dinh]

Let me correct one of the points in your post first: I work for a living
separate from the newsgroups and the advices given in these newsgroups are
*free* and *of my own free will*. MVPs and other respondents don't earn any
money from these newsgroups!

In your original post, you wrote "...but humor me for now" regarding the
Table Structure so I did. However, 2 out of 3 replies (the 3rd replies only
for a specific point) referred to the properly-designed Table Structure.
Note that I didn't use "consolidate" or "combine" like you did since there
are rules that need to be followed to have an efficient and flexible Table
Structure. Access can handle a properly-designed Table with 100K+ Records
with no problems but it will not work efficiently with 7 of Tables with 1000
Records each or other incorrectly-structured Tables.

*In humoring you* with your "7 Tables of similar structure" + "to edit those
records via the form using the query and leave the records in their own
tables", I suggested using a Make-Table Query to make a *temp* Table but I
do hope you take into account of what I wrote after that. I am not sure
whether you objected to this temporary Table or your impression that I
implied "consolidating" or "combining" 7 Tables into one Table (which I did
not!). If you meant the temporary Table, then please re-read what I wrote
in the same point. Of course, I don't have these problems in databases I
design and earn my living from.

In making any program as simple as possible for the users, I am not sure
whether you referred to making your database, with you as the developer, as
simple as possible for your computer novice users or the Access software
making as simple as possible for you as the user so I'll address both.

If the former, it is then even more important to get the Table Structure
correct and then to design easy and intuitive GUI for your users. However,
the second aim(easy and intuitive GUI) cannot be achieved easily without the
first (correctly-designed Table Structure). Sure there are people who can
simply use their logical mind and their common sense to do this but most of
us will need to understand the Relational Database Design Principles (and
know how to apply the Database Normalisation technique). IMHO, database
developers that don't know RDDP and Normalization do a *disservice* to their
clients.

If the later, sure Access does a hell of a good job for everyone to create a
database by throwing a bunch of Tables / Queries / etc... together. Users
can do a lot with basic knowledge of how Access works and how to put a few
things together. In fact, this is why I.T. profesionals / developers with a
cursory glance at Access ("Yeah. It's part of the Microsoft Office suite
for 'users'.", I hear.) tend to consider Access not as "developer's tools".
However, like most other software, we need to invest time to get the best
out of the software. In Access, we need the additional effort to know about
RDDP + Normalisation. Just think of reading a 100-page Access book and a
1500-page Access book: they could not simply have the same content even
though they both show the readers how to create "databases".

Another example: I once tried to write a Mathematics paper in Word using a
template for numbering / indexing / footnotes / etc and mathematical
symbols, e.g. the integration sign of the stretched capital S ("Other
people could do it; sure I could do it easily since it is only Word,
anyway." mentality). How wrong I was! I finished the paper after spending
inextrodinary amount of time on Word that I thought was designed for average
users and simple to use.

I wrote all of these as matter-of-factly and meant no harshness. My advices
in these newsgroups are free (I stress) which you are free to heed or not.
Since I already mention RDDP + Normalization (which is the cure) a number of
times so I think I stop here.

--
HTH
Van T. Dinh
MVP (Access)