Master Query

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

Guest

Hello All,

I was wondering if it is possible to create a query that will check all of
my tables, using a "like" in the criteria. I have about 30 tables but they
are all fairly small.

Thanks in advance!
SAB
 
Hello All,

I was wondering if it is possible to create a query that will check all of
my tables, using a "like" in the criteria. I have about 30 tables but they
are all fairly small.

Thanks in advance!
SAB
Click to expand...

You could create a UNION query, if all thirty tables have the same
structure. See UNION in the online help.

However, this suggests that you need to rethink your table design; in
fact the very need to *do* this suggests that your tables are not
properly normalized! What are these thirty tables, and why is it
necessary to search all tables (all fields in all tables!?) for a
value? Normally each Table contains all of the information about a
particular type of Entity, and each field contains information about
one Attribute of that entity - and hence if you need to find a
particular entity based on one of its attributes, you need to search
only one field of one table.

John W. Vinson[MVP]
 
Thanks for the reply John.

The tables are all different types of Contact numbers. I work in a Command
Center and we have tons of phone numbers to access and maintain. I am fairly
new to Access so I imported all of these from Excel that people had already
done.

I created forms so people can update them easily, Is there a better way to
do this?

Thanks again.
 
Thanks for the reply John.

The tables are all different types of Contact numbers. I work in a Command
Center and we have tons of phone numbers to access and maintain. I am fairly
new to Access so I imported all of these from Excel that people had already
done.

I created forms so people can update them easily, Is there a better way to
do this?
Click to expand...

Yes, there certainly is. Use Access as a relational database, rather
than as a substitute spreadsheet!

All these different types of Contacts are... contacts. They should all
be in ONE table, with a Type field to distinguish which type of
contact each person is.

You'll actually probably need two or three tables - but not for
contacts of different types. Assuming that there might be duplicates
across these tables (i.e. that the same person might serve as two or
three different types of contact), and that each person might have
one, two, or several phone numbers (home, work, cell, pager), you
might want tables such as:

Contacts
ContactID <Primary Key>
LastName
FirstName
<other information specific to this person>

Types
Type <Primary Key>

ContactTypes
ContactID <link to Contacts>
Type <Link to Types>

Phones
ContactID <link to Contacts>
PhoneType <e.g. Home, Cell, ...>
PhoneNumber

This gives you ONE form - with a couple of subforms - and ONE query to
find all the information about any person, without needing to search
multiple tables.

John W. Vinson[MVP]
 
Thanks again John,

My only concern is that I am setting this up for anyone who is on watch that
day to be able to View, Change and Print that data if necessary. So, for each
table I have, I made a Print Preview button, a form and a query. If I use the
structure that you kindly recommended, would I still be able to customize
those items to meet me needs?

Hope I am not being difficult, and I appreciate your help.

/r
SAB
 
Thanks again John,

My only concern is that I am setting this up for anyone who is on watch that
day to be able to View, Change and Print that data if necessary. So, for each
table I have, I made a Print Preview button, a form and a query. If I use the
structure that you kindly recommended, would I still be able to customize
those items to meet me needs?

Hope I am not being difficult, and I appreciate your help.

/r
SAB
Click to expand...

Certainly. You would use a Report based on a query; the query could
reference a textbox or other control on your Form to select which
subset of the data should be printed. You certainly do NOT need a
separate form and a separate query for each - you need *one* form, and
*one* query, and (probably) only one report.

Remember that I don't know the structure of your database or the
nature of the reports you want to print - so I can't be too specific.
But I can make some suggestions:

- Never open Table Datasheets except for design and debugging. They're
not designed for data editing or reporting.
- Use Forms (based on queries, usually) for all interaction with the
data.
- Use Reports (based on queries, almost universally) for printing.

Take a look at some of the references on Jeff's Resources page, and
perhaps open up the Northwind sample database and see how it works.
You'll see that it has hundreds of suppliers - but it does NOT have a
table for each supplier, which is what you appear to be trying to do!

John W. Vinson[MVP]
 
Back
Top