Joining two fields from one table to one field in another table

[Alex Martinez]

Hello,

I work for an insurance company and I need some professional help. I have
two tables one call "Claims" the other "Reviewers"

In the "Claims" table I have the following fields:
ClaimNumber (Text)
AssignedTo (Text) (Login Name)
ReassignedTo (Text) (Login Name) - (Field just added)


The "Reviewers" table I have
ReviewerID (Text) (login Name)
ReviewerName (Text) (Reviewer full name)

In the past I was able to join from the "Claims" table the "AssignedTo"
field to the "Reviewers" table "ReviewerID" field to get the ReviewerName
not a problem. Now I have to add a new field "ReassignedTo" in the
"Claims" table. Now I need to get the correct ReviewerID & ReviewerName
when the "ReassignedTo" is populated otherwise I need to pick up the
"AssignedTo".


Example

Claims table
ClaimNumber AssignedTo ReassignedTo
X999 AC03 CH03


Reviewers table
ReviewerID ReviewerName
AC03 Allen Carter
CH03 Cindy Pick


Now all I need is to pick up Cindy Pick (CH03) not Allen Carter (AM03) in my
query. How do I do that? I like to keep this in one query. Any tips will
be appreciated. Thank you in advance.

 

[Allen Browne]

Presumably you have already opened the Relationships window (Tools menu) and
created a relationship from Claims.AssignedTo to Reviewers.ReviewerID (with
Referential Integrity).

Now you need a 2nd relationship between these tables.
Add another copy of the Reviewers table to the Relationships window.
Access will alias it as Reviewers_1.
Drag Reviewers_1.ReviewerID onto Claims.ReassignedTo.
This gives you a second relationship between the tables (as distinct from
one relationship joined on 2 fields.)

Once that's in place, you can create a query that contains the 3 tables
(Claims, and 2 copies of Reviewers.) Now what you want to do is select
Reviewers_1.ReviewerName if it has a value, otherwise
Reviewers.ReviewerName. Enter this into the Field row in query design:
Nz([Reviewers_1].[ReviewerName], [Reviewers].[ReviewerName])
Naturally the field will be read-only (so suitable for a report, but
probably not a form.)

It might be worth considering whether a claim could be reassigned more than
once? If there could be many reviewers for one claim, and you want to be
able to track the history of the claim, it might be worth creating a related
table with fields like this:
ClaimNumber foreign key to Claims.ClaimNumber
ReviewerID foreign key to Reviewers.ReviewerID
AssignDate Date/Time (when the claim was assigned to this
reviewer.)
The current reviewer is then the one with the most recent AssignDate.

 

[Marshall Barton]

I work for an insurance company and I need some professional help. I have
two tables one call "Claims" the other "Reviewers"

In the "Claims" table I have the following fields:
ClaimNumber (Text)
AssignedTo (Text) (Login Name)
ReassignedTo (Text) (Login Name) - (Field just added)


The "Reviewers" table I have
ReviewerID (Text) (login Name)
ReviewerName (Text) (Reviewer full name)

In the past I was able to join from the "Claims" table the "AssignedTo"
field to the "Reviewers" table "ReviewerID" field to get the ReviewerName
not a problem. Now I have to add a new field "ReassignedTo" in the
"Claims" table. Now I need to get the correct ReviewerID & ReviewerName
when the "ReassignedTo" is populated otherwise I need to pick up the
"AssignedTo".
Example

Claims table
ClaimNumber AssignedTo ReassignedTo
X999 AC03 CH03

Reviewers table
ReviewerID ReviewerName
AC03 Allen Carter
CH03 Cindy Pick

Now all I need is to pick up Cindy Pick (CH03) not Allen Carter (AM03) in my
query. How do I do that? I like to keep this in one query. Any tips will
be appreciated.

You will need to get the names for both and then choose
which one to use. To get both names, add the Reviewers
table to the query a second time. Access should assign the
name Reviewers1 to the second copy. Now, drag a connecting
line from the AssignedTo field to the Reviewers table. Then
drag another connecting line from the ReassignedTo field to
the Reviewers1 table.

You can now use a calculated field in the table to provide
the desired name:

CurrentlyAssigned: IIf(Claimes.ReassignedTo Is Null,
Reviewers.ReviewerName, Reviewers1.ReviewerName)

 

[Alex Martinez]

Allen,

As always I appreciate your time and your help. Thanks for the tip. Good
Day.

Presumably you have already opened the Relationships window (Tools menu)
and created a relationship from Claims.AssignedTo to Reviewers.ReviewerID
(with Referential Integrity).

Now you need a 2nd relationship between these tables.
Add another copy of the Reviewers table to the Relationships window.
Access will alias it as Reviewers_1.
Drag Reviewers_1.ReviewerID onto Claims.ReassignedTo.
This gives you a second relationship between the tables (as distinct from
one relationship joined on 2 fields.)

Once that's in place, you can create a query that contains the 3 tables
(Claims, and 2 copies of Reviewers.) Now what you want to do is select
Reviewers_1.ReviewerName if it has a value, otherwise
Reviewers.ReviewerName. Enter this into the Field row in query design:
Nz([Reviewers_1].[ReviewerName], [Reviewers].[ReviewerName])
Naturally the field will be read-only (so suitable for a report, but
probably not a form.)

It might be worth considering whether a claim could be reassigned more
than once? If there could be many reviewers for one claim, and you want to
be able to track the history of the claim, it might be worth creating a
related table with fields like this:
ClaimNumber foreign key to Claims.ClaimNumber
ReviewerID foreign key to Reviewers.ReviewerID
AssignDate Date/Time (when the claim was assigned to this
reviewer.)
The current reviewer is then the one with the most recent AssignDate.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

I work for an insurance company and I need some professional help. I
have two tables one call "Claims" the other "Reviewers"

In the "Claims" table I have the following fields:
ClaimNumber (Text)
AssignedTo (Text) (Login Name)
ReassignedTo (Text) (Login Name) - (Field just added)


The "Reviewers" table I have
ReviewerID (Text) (login Name)
ReviewerName (Text) (Reviewer full name)

In the past I was able to join from the "Claims" table the "AssignedTo"
field to the "Reviewers" table "ReviewerID" field to get the
ReviewerName not a problem. Now I have to add a new field
"ReassignedTo" in the "Claims" table. Now I need to get the correct
ReviewerID & ReviewerName when the "ReassignedTo" is populated otherwise
I need to pick up the "AssignedTo".


Example

Claims table
ClaimNumber AssignedTo ReassignedTo
X999 AC03 CH03


Reviewers table
ReviewerID ReviewerName
AC03 Allen Carter
CH03 Cindy Pick


Now all I need is to pick up Cindy Pick (CH03) not Allen Carter (AM03) in
my query. How do I do that? I like to keep this in one query. Any tips
will be appreciated. Thank you in advance.

 

[Alex Martinez]

Marshall,

Thanks for the helpful tip. I appreciate your help.
Regards

 

[John Spencer]

One additional bit of advice Since the ReassignedTo field can be null the
join from the Claims table to the Reviewers_1 table should be one that shows
All records in the Claims Table and only those records in the Reviewers_1
table that match. The way to get that choice is to double-click on the join
line and select the option from the choices that appear.

With that set up, you should then be able to use the NZ function to get the
value you need.
NZ(Reviewers_1.ReviewerName,Reveiwers.ReviwerName)

Apologies to Marshall and Allen if I missed this in their answers.

Marshall,

Thanks for the helpful tip. I appreciate your help.
Regards

 

[Marshall Barton]

Good points John. I am still sitting in the corner for
using IIf instead of Nz ;-)

Thanks for pointing that out.