trying to extract a name linked to two tables

[Andre C]

This may seem complex to explain but I will try.

This is a medical database.

In table 1 (Contacts) are all the clients and relevent details,
included in this is a key which identifies the allocated nurse. A list
of nurses along with the key is in a seperate table.

In table 2 (visits) are a list of visits to these clients. each record
conatins such things as date, duration, location of visit, a key
linkiing to the client and (here is a problem) a key linking back to
teh nurse table which identifies who visited. (The issue is that any
nurse can visit another nurse's patient.)

I am generating a report which will analyse nurses caseload and the
number of visits, duration etc. So I create a querry drawing in data
from tables 1 and 2. Trouble is when I attempt to add a field to this
querry with the nurses name I loose records. The name I want is the
the nurse the client belongs to, not the visiting nurse but as the
nurse table is linked to both tablesw 1 & 2 the querry seems to ignore
visits where the nurse was different.

My way around this is to only refer to the nurse key in the querry and
this works fine. However, in the report I want this number translated
to the actual name which the key in the querry links to. How can I
make a text label based on the value of the nurse key which will
lookup the nurse name.

By the way the report will group on different nurses.

I hope this makes sense.

ACC

 

[Jeff Boyce]

Andre

Consider posting the SQL statement of the query you are using. Perhaps you
are using the nurse assigned (?your table 1) field rather than the nurse
visiting (?your table 2) field.

You can create a query with the two tables joined on (only) the client IDs,
then add the nurses lookup table, and join to (only) the visits table (?2?)
nurse visiting field to get the name of the visiting nurse.

Or perhaps I don't (yet) understand your data model...

 

[Andre C]

You can create a query with the two tables joined on (only) the client IDs,
then add the nurses lookup table, and join to (only) the visits table (?2?)
nurse visiting field to get the name of the visiting nurse.

I solved the problem by adjusting the joins. Something I did not fully
understand until today!!!!!

Still don't fully understnad inner and outer joins though. Any good
links?

 

[Jeff Boyce]

Andre

Start off checking Access HELP on the subject.

If you are using the query design window, you may not need to know inner
from outer...