Multiple Tables

Hello all,

I have a Country table in my db that is referenced by many tables. I am now trying to create a query that will list info from 2 tables, and I would like for the country to be listed as well.

The problem is of course that Table1 and Table2 both have "CountryINDEX".

Can someone show me how I could output all records in tables 1 and 2 as well as the CountryName (referenced in the Country Table)?

Thanks,

-Sean

Drag the * to the query grid for table 1 then add country name to the grid from table 2.

I forgot to mention you will notice from your relationships that when you add the tables to the grid they will already be joined by index. If not you will need to join them in your query.

Hay

Thanks for replying Hayley,

unfortunately this will not work as it is not Table 2 which contains the country name - but the country table does.

What I have is a table for each group of people - they all have varying types of info. One common field is CountryINDEX, which was assigned a number according to the Country_TABLE.

So far I have:
SELECT tblPERSONAL_INFO.PersonID, tblPERSONAL_INFO.LNAME, tblGroup1.CountryID, tblGroup2.CountryID
FROM (tblPERSONAL_INFO LEFT JOIN tblGroup1 ON tblPERSONAL_INFO.PersonID = tblGroup1.PersonID) LEFT JOIN tblGroup2 ON tblPERSONAL_INFO.PersonID = tblGroup2.PersonID;

This will output the information I need except, instead of the CountryID, I would like for it to go to the Country_Table and lookup which country it should be (as opposed to ie 2 or 35).

Thanks,
-Sean

Here it is a little further along:

This works well if someone belongs to both groups, however I can't seem to allow the record if they belong to one OR the other (plus, I would like them listed as many times as groups they are in) - the people who only belong to one OR the other get left out.

SELECT tblPERSONAL_INFO.PersonID, tblPERSONAL_INFO.FNAME, tblPERSONAL_INFO.LNAME, tblEmployed.Title, tblCountry.Country_Name
FROM tblPERSONAL_INFO INNER JOIN ((tblCountry INNER JOIN tblEmployed ON tblCountry.CountryID = tblEmployed.CountryID) INNER JOIN tblStudent ON tblCountry.CountryID = tblStudent.CountryID) ON (tblPERSONAL_INFO.PersonID = tblStudent.PersonID) AND (tblPERSONAL_INFO.PersonID = tblEmployed.PersonID);

Any help would be greatly appreciated!!!

-Sean

You probably don't need separate group tables. You should have one table with a field that indicates which group a person belongs to.

Then you can add the CountryIndex table to the query and draw a join line connecting the CountryID in the Group table with the CountryID in the Index table. Select the CountryName field rather than the CountryID field.

Thanks for answering Pat,

I think the confusing thing in this subject is that I've been using an example because my real db is big.

In reality, each group has about 2-12 fields, and most are very different (ie some have expiration dates). Therefore, there is not much I can do to these tables.

I used CountryID in the last post because this extracted the correct number of records - once I use CountryName, the number of records is reduced only to those who belong to both groups (if I try any other kind of join - that I know of - I get the ambigious join error).

Thanks again everyone,

-Sean

Since the query (the one with the left joins) outputs the info you need except the country names, modify the two CountryIDs in it as follows (remove one of them if they are actually the same) and save it as qryOutput:-

SELECT tblPERSONAL_INFO.PersonID, tblPERSONAL_INFO.LNAME,
tblGroup1.CountryID AS Group1CountryID,
tblGroup2.CountryID AS Group2CountryID
FROM (tblPERSONAL_INFO LEFT JOIN tblGroup1 ON tblPERSONAL_INFO.PersonID = tblGroup1.PersonID) LEFT JOIN tblGroup2 ON tblPERSONAL_INFO.PersonID = tblGroup2.PersonID;


Create a new query based on qryOutput (type/paste in the new query's SQL View):-

SELECT PersonID, LNAME, Group1CountryID,
(Select Country_Name from tblCountry where CountryID = a.Group1CountryID) as Group1_CountryName, Group2CountryID,
(Select Country_Name from tblCountry where CountryID = a.Group2CountryID) as Group2_CountryName
FROM qryOutput as a


Run this new query. The two select statements in brackets will retrieve the country names from table tblCountry.

Thank you Jon!!!

It seemed like such an easy thing to do (replace the numbers with the country name) that combining queries in this way hadn't even occured to me.

Thanks again!

-Sean