Multiple Tables into one querie

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jennifer - 20 Feb 2008 07:04 GMT

Ok here it goes bare with me.
I have a 4 tables
Tbl 1 (basic inform)
Exihibator ID Name Etc
1001 Jennifer 18
1002 Bob 17

Table 2 Steer Table
Exihibator ID Entry Number Placing in Class
1001 121 2
1001 122 5
1002 253 1

Table 3 Swine Table
Exihibator ID Entry Number Placing in Class
1001 123 0
1002 254 1
1002 255 6

Then another table for sheep with the same information
I want to create a query that combines the tables. You may have exhibitor
1001 who had a steer, pig and lamb. I want to see all of her placings in one
query or you may have another exhibitory who only showed steers and i want to
see how he did as well. How do I get these to go together? Thank you for any
direction. VERY BEGINNER I KNOW.

Signature

Thank you,

Jennifer

Reply to this Message

Allen Browne - 20 Feb 2008 07:35 GMT

The simplest way to solve this would be to put all the entries (whether
steers, swine, or sheep) in the one table. You can add a field to indicate
what kind of animal it is.

Better still, you probably have several classes where people can show
animals

You will have tables like this:
1. Client table (one record per exhibitor)

2. Class table (one record per competition class.)
This table defines what kind of animal the competition is for.

3. Entry table (one record for every entry).
Fields:
EntryID AutoNumber primary key
ExhibitorID Number relates to a person on Client table
ClassID Number relates to a competition in Class table
EntryDescrip Text description of the animal entered
Result Number placing in class (blank until judged)

So, one exhibitor can enter animals in many classes.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Ok here it goes bare with me.
> I have a 4 tables
[quoted text clipped - 24 lines]
> any
> direction. VERY BEGINNER I KNOW.

Reply to this Message

Jennifer - 20 Feb 2008 07:47 GMT

You have totally comfirmed what I had thought about how this "should" have
been set up. I am working with a file that has been used this way for a long
time. I was wanting to create a report for a show we just had that used the
tables as they are. I'm just happy I was correct in thinking there may have
been a better way to set it up. Which means I am learning. Cheers!

Signature

Thank you,

Jennifer

> The simplest way to solve this would be to put all the entries (whether
> steers, swine, or sheep) in the one table. You can add a field to indicate
[quoted text clipped - 47 lines]
> > any
> > direction. VERY BEGINNER I KNOW.

Reply to this Message

John Spencer - 20 Feb 2008 13:06 GMT

Allen's advice is very good and absolutely correct.

IF for some reason, you have to work with the current data you can use a
UNION query to combine all the data into one query. Union queries can only
be built in the SQL view. The query would look something like the following

SELECT [Exihibator ID], "Steer" as TheClass, [Entry Number], [Placing in
Class]
FROM [Table Steers]
UNION ALL
SELECT [Exihibator ID], "Swine" as TheClass, [Entry Number], [Placing in
Class]
FROM [Table Swine]
UNION ALL
SELECT [Exihibator ID], "Sheep" as TheClass, [Entry Number], [Placing in
Class]
FROM [Table Sheep]

Once you have that built, you can save it and then build a new query using
the union query and the Exhibator table.

Signature

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> You have totally comfirmed what I had thought about how this "should" have
> been set up. I am working with a file that has been used this way for a
[quoted text clipped - 4 lines]
> have
> been a better way to set it up. Which means I am learning. Cheers!

>> > Ok here it goes bare with me.
>> > I have a 4 tables
[quoted text clipped - 28 lines]
>> > any
>> > direction. VERY BEGINNER I KNOW.

Reply to this Message