2 joins question

[Guest]

I have a table Physicians with Last Name, First Name, ID (key field), Position

Then the Review table has physician_reviewedID field which could be anyone from the Physician table (1 to many) but it also has physician_reviewerID which could be anyone(1 to many). This means 2 joins from the Physician Key Field to fields in the Review table.

When I created the joins, it made another duplicate physicians table (Physicians_1) and this table is not available for queries.

Any thoughts on creating this relationship better?

Thanks.

 

[Allen Browne]

The 2nd copy of the Physicians table in the relationships window shows that
you have the relationship correct, i.e. you have 2 relationships (as
distinct from a 2-field relationship).

You can achieve the same thing in a query by adding the Physicians table a
2nd time. Again Access will alias the 2nd copy as Physicians_1. However, you
can use the Properties box (View menu) to give it a different alias if you
wish, e.g. you could call it Reviewer.

When you have muliple relationships like that in query design, Access cannot
know which relationship you want to use on this occasion. You will therefore
have to delete some of the lines between tables, and create others (by
dragging the field from one table onto the matching field in the other).

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

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

I have a table Physicians with Last Name, First Name, ID (key field), Position

Then the Review table has physician_reviewedID field which could be anyone

from the Physician table (1 to many) but it also has physician_reviewerID
which could be anyone(1 to many). This means 2 joins from the Physician Key
Field to fields in the Review table.

When I created the joins, it made another duplicate physicians table

(Physicians_1) and this table is not available for queries.

 

[Allen Browne]

A query with multiple copies of the table will be dead easy.

The only thing you may have to watch is the join types. If you have some
records where the ID is blank, be sure to double-click the join lines and
choose option 2 or 3, i.e. "All records from Table 1, and any matches from
Table 2".

BTW, what you are doing is quite common place, so there is no issue at all
with the approach.

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

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

Thanks. I will see how that works by deleting lines.

Since my Review Info table does not have "names", just "IDs", I am trying

to figure out the easiest way to get the Names in the Reports...ie base the
report on a query or on the Review Info table and get the "names" via SQL
statements?

 

[Guest]

Thanks. I recently added the ID fields and the blanks were probably affecting my query.

 

[ÍÉÛÁ]

I have a table Physicians with Last Name, First Name, ID (key field), Position

Then the Review table has physician_reviewedID field which could be anyone

from the Physician table (1 to many) but it also has physician_reviewerID
which could be anyone(1 to many). This means 2 joins from the Physician Key
Field to fields in the Review table.

When I created the joins, it made another duplicate physicians table

(Physicians_1) and this table is not available for queries.