Print an Auditor name instead of their ID number in the report

[Alex Martinez]

Hello,

I am using Access 2002 and I need some help. I am using 3 tables in my
query, which my report pick up from. 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. Now in my report I have the
following fields: "FP", "CO","Rev","Ck","Dis", what I really want is not to
show the auditorID number, but the auditor's name. Now I posted this in
the queries form, but maybe someone can maybe advise me a solution on a
report level. The problem with using the AuditorID is that the user may add
new auditors, so hard coding is not a solution. Any tips will be
appreciated. Thank you.

 

[Duane Hookom]

I would normalize the Inventory table so that FP CO Rev Ck etc are not
fields in the table. I would make these values in a related table. Assuming
Inventory contains a primary key field of InventoryID, I would create a
table "tblInventoryAuditors" with field InventoryID, AuditType (for values
like FP, CO, Rev,...), AuditorID, and possibly others such as AuditDate etc.

The new table is called a Junction table and allows for much greater
flexibility.

 

[Marshall Barton]

I am using Access 2002 and I need some help. I am using 3 tables in my
query, which my report pick up from. 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"

[snip]

I tried to answer this question in another thread.

For the sake of everyone's sanity in tracking a
conversation, please don't post the same question multiple
times.