Need HELP with Relationship Problem (1 Viewer)

[danbl]

I have been trying for days to figure out why I can not get the relationship(s) to work in my database!!:banghead:

I need to add a table that tracks physician time off (t_DrUTZ); I needs to be related to the table (t_Physician) for the DrId. In addition I want to gather the data from the other tables which are linked by the visit Id field from the visits table. The utilization will be calculated by gathering data based on time used which is pulled by a date range from visit dates and a monthly value for time off.

The current relationships work but when I try to add the table (t_DrUTZ) I get no data at all.

Attached is the test database I am using to figure this out.

Can someone please help me with this????

 

[DrallocD]

I don't fully understand how [t_DrUTX] relates to [t_visit] but it looks like it should be a left outer join. i.e. set the join type to "Include ALL records from 't_Visit' and only those records from 't_DrUTZ' where the joined fields are equal."

Is it possible for Physician in [t_DrUTZ] to be different to the one in [t_Visit] when the two tables are joined?

I would also suggest naming your fields consistently so that [DrID] is always the ID of the physician and [Physician] is always the name. [vid] would be [vID] (as would [vil]).

 

[danbl]

Thank you for replying ...... the only reason it has to relate to the visits table is so I can obtain the data from the other tables. The purpose of the relation to visits is I need to compare ddtiem available to actual time used.
The table name should be DrUTZ
I will try what you suggest and see what happens.. post back in a while..

 

[danbl]

Okay .. I now have the colums visiable but no data? More explanaitions for you to consider... vId does not populate; I need to have a date field for the Utilization table and that needs to relate to the date in the visits table inorder to get the info from the other tables for times of procedures. I the utilization tabel I need to store a monthly value for vaction time and Adm Reaquested time off. These values will very from month to month. Does this help explain the issue(s)??

 

[DrallocD]

I think that you are at the limits of what can be done using relationships and you need to do this with SQL and/or programming.

Having said that, I almost never use relationships since most of my tables are linked to SQL Server so others may be able to help take relationships further than I can.

 

[danbl]

okay thanks for the honest feedback. I will try to see who else can help.

Dan