Question about relationships, inner joins and query results

[Cara]

I have a database that has 5 tables with relationships linking last
names, so in my report it shows results from each table based on
person.

My question is this: when I go to query the result, it only shows
results of people listed in all 5 tables.

Is there any way to see people in the results only if they have
listings in 2 or 3 of the 5 tables? Is it something to do with
relationships?

Thanks!

Cara

[Johnny Meredith]

Use outer joins

For example, open the query window, add the tables you're interested
in. Double-click on each of the relationship lines and select the
correct option. For example:

Three tables:
Header [ID, Name]
Details [ID, fkHeaderID, Name]
Details2 [ID, fkHeaderID, Name]

1 Header has many Details
1 Header has many Details2

SQL output to show all records in Header and only records in Details
and Details2 where Header and a foreign keys in each many-side match:

SELECT Header.Name, Details.Name, Details2.Name FROM (Header LEFT JOIN
Details ON Header.ID = Details.fkHeade rID) LEFT JOIN Details2 ON
Header.ID = Details2.fkHead erID;

Access should produce this statement for you.

Thanks,
Johnny

[Cara]

I've got the left join working, but the issue now is, the information
will be entered into the tables randomly. IE, someone might enter
information into table 2 but they don't have corresponding information
in table 1 and thus it won't show up on my report page. Is there any
way to do a left and a right join? Or connect them all somehow?

Thanks

[Kevin Nechodom]

I will present two answers. The first is an abysmal hack,
but will do the job without a structure change. The second
is the right way to do it.

1. Build a view that includes ALL keys from all tables:
SELECT Key From Table1
UNION
SELECT Key from Table2
UNION
SELECT Key from Table3
....
Then use THIS view, and left join everything else to it.
The more data you have, the more inefficient this will be.

2. Redesign your database so that you have a Key table, then
use foreign keys to restrict the data that goes into the
other 5 tables so that only valid keys can be inserted.
Inserts to the Key table need to happen before data in the
other tables can be inserted.

Kevin

Cara<ro******** *@cox.net> 6/9/2005 9:48 AM >>>

I've got the left join working, but the issue now is, the
information
will be entered into the tables randomly. IE, someone
might enter
information into table 2 but they don't have corresponding
information
in table 1 and thus it won't show up on my report page.

Isthere any
way to do a left and a right join? Or connect them all
somehow?

Thanks