Inner Join with three sample tables

[Alex Martinez]

Hello,

I am using Access 2002 and I need some help. I am using 3 tables in my
query. Table one call Inventory contains the following fields: "Insured
Name", "FP", "CO","Rev","Ck","Dis","ReviewerID". The fields "FP",
"CO","Rev","Ck","Dis" are auditorsID numbers. The second table call Claims
Reviewer. Contains the following fields: "ReviewerID, "Reviewer Name" The
third table is call Auditor, which contains the following fields:
"AuditorID, "Auditor Name"

What I want to do is inter join the Inventory table to the Claims Reviewer
table and the Auditor table so I can pick up the Reviewer's Name and
Auditor's Name. For example:

I did a INNER JOIN using ReviewersID from table Inventory and Claims
Reviewer table to pickup the Reviewer's Name (Inventory.[ReviewerID] inner
join with Claims Reviewer.[ReviewerID]) . I am OK so far, I can get the
Reviewer Name without a problem, but can't get the Audito's name when I try
to do the same with Inventory table using all of the following fields "FP",
"CO","Rev","Ck","Dis" trying to inner join with Auditor table with the
AuditorID all at once I don't get the Auditors name. I can get the auditors
name if I use one of the following fields only ("FP",
"CO","Rev","Ck","Dis"), but not all at once. Any tips will be appreciated.
Thank you.

 

[Marshall Barton]

I am using Access 2002 and I need some help. I am using 3 tables in my
query. Table one call Inventory contains the following fields: "Insured
Name", "FP", "CO","Rev","Ck","Dis","ReviewerID". The fields "FP",
"CO","Rev","Ck","Dis" are auditorsID numbers. The second table call Claims
Reviewer. Contains the following fields: "ReviewerID, "Reviewer Name" The
third table is call Auditor, which contains the following fields:
"AuditorID, "Auditor Name"

What I want to do is inter join the Inventory table to the Claims Reviewer
table and the Auditor table so I can pick up the Reviewer's Name and
Auditor's Name. For example:

I did a INNER JOIN using ReviewersID from table Inventory and Claims
Reviewer table to pickup the Reviewer's Name (Inventory.[ReviewerID] inner
join with Claims Reviewer.[ReviewerID]) . I am OK so far, I can get the
Reviewer Name without a problem, but can't get the Audito's name when I try
to do the same with Inventory table using all of the following fields "FP",
"CO","Rev","Ck","Dis" trying to inner join with Auditor table with the
AuditorID all at once I don't get the Auditors name. I can get the auditors
name if I use one of the following fields only ("FP",
"CO","Rev","Ck","Dis"), but not all at once. Any tips will be appreciated.

Using "all of them at once" doesn't make sense to me. Could
you explain what this is all about.

Maybe you can have any one (or more) of those fields linked
to the auditor record. If that's the case, then switch the
query to SQL view and modify the ON clause to use OR instead
of AND.

 

[tfossum]

Alex,

I suspect the FP, CO, Rev columns in the inventory table are specific roles
your auditors perform on a claim. Consequently you are wanting to query for a
given claim who were all the people that filled all roles. Using the table
names and columns names you provided this query will provide you the names
(not IDs which I understand is the issue) for all people in all roles.

SELECT Inventory.[Insured Name], [FP.Auditor Name] as FP, [Co.Auditor Name]
as CO, [Rev.Auditor Name] as Rev, [Ck.Auditor Name] as Ck, [Dis.Auditor
Name] as Dis, [Claims Reviewer.Reviewer Name]
FROM Inventory, [Claims Reviewer],
(Select * from Auditor) as FP,
(Select * from Auditor) as CO,
(Select * from Auditor) as Rev,
(Select * from Auditor) as Ck,
(Select * from Auditor) as Dis
WHERE Inventory.FP = [FP.AuditorID]
AND Inventory.CO = [CO.AuditorID]
AND Inventory.Rev = [Rev.AuditorID]
AND Inventory.Ck = [Ck.AuditorID]
AND Inventory.Dis = [Dis.AuditorID]
AND Inventory.ReviewerID = [Claims Reviewer.ReviewerID]

I guess I kinda went the brute force way, but I am not really sure if there
is an easier way. If someone has a suggestion I would be interested in seeing
it.

I hope this helps. If so please make sure to mark the response as such so
others can benefit from the thread.

Cheers,
- Ted

 

[John Vinson]

Hello,

I am using Access 2002 and I need some help. I am using 3 tables in my
query. Table one call Inventory contains the following fields: "Insured
Name", "FP", "CO","Rev","Ck","Dis","ReviewerID". The fields "FP",
"CO","Rev","Ck","Dis" are auditorsID numbers.

That's a VERY questionable table design IMO. If you have a Many
(Insureds) to Many (Auditors) relationship, a better design would be
to model it as a many to many relationship; have a InventoryAuditors
table with links to the primary key of Inventory, to the Auditors
table AuditorID, and a Role field to identify that this auditor is the
CO auditor, this next one is the Rev auditor, etc. You would then have
one *record* per auditor instead of one *field* per auditor.

The second table call Claims
Reviewer. Contains the following fields: "ReviewerID, "Reviewer Name" The
third table is call Auditor, which contains the following fields:
"AuditorID, "Auditor Name"

What I want to do is inter join the Inventory table to the Claims Reviewer
table and the Auditor table so I can pick up the Reviewer's Name and
Auditor's Name. For example:

What you'll need to do is add the Auditors table to the query grid
*five times* - each instance joined to a different one of these
fields. If any AuditorID field might be NULL you'll need to use a Left
Outer Join instead of an Inner Join - otherwise you'll see no data at
all.

John W. Vinson[MVP]

 

[tfossum]

Agreed. It is sketchy to say the least.

That's a VERY questionable table design IMO. If you have a Many
(Insureds) to Many (Auditors) relationship, a better design would be
to model it as a many to many relationship; have a InventoryAuditors
table with links to the primary key of Inventory, to the Auditors
table AuditorID, and a Role field to identify that this auditor is the
CO auditor, this next one is the Rev auditor, etc. You would then have
one *record* per auditor instead of one *field* per auditor.


What you'll need to do is add the Auditors table to the query grid
*five times* - each instance joined to a different one of these
fields. If any AuditorID field might be NULL you'll need to use a Left
Outer Join instead of an Inner Join - otherwise you'll see no data at
all.

John W. Vinson[MVP]