Relationship Questions (1 Viewer)

[danbl]

Attached is the relationship snapshot for my DB. I need to add a table that tracks "vac time" and "Adm Req time" by physician. I am having difficulty with the relationships.

I have a query that pulls info from the other tables but when I add the new table in any relationship in pulls no data. Remove the relationship and the other data returns.
The new table is DRUtz; the query to add to is Utlz";

Can some one please help with this??:banghead:

 

[jdraw]

The purpose of your database is not clear; your field names to not add/convey any real info. It also seems your information is all linked backed/based on Visit Id.

The best way I can think of to set up relationships is to work from "Business facts" and incrementally create a model; then reconcile every "issue" that evolves and adjust the model (or the test data) and tighten up your business facts. Play stump the model until all issues have been reconciled.

See the entity relationship diagramming info at:
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

and for a general approach to design
http://www.rogersaccesslibrary.com/forum/uploads/5/12-Steps_to_Better_Databases.zip

 

[danbl]

Thanks for the links ... I will read these and see if I can get to where I want to be.

 

[danbl]

The purpose is to track the various data elements based on a "visit date". This then creates a series of reports for a monthly time frame. This is an ever changing envioprment based on required reporting.

In addition we are not trying to track utilization with a few variables; vaction time, and admin requested time off. The idea was to use the same visit date and be able to pull time values from on of the other tables based on the visit date relationship.

I have tried a number of relationships but no success.

 

[danbl]

lots of looks .... any help please???

 

[Pat Hartman]

"Date" is a poor choice for a column name since it is the name of a function. You will run into conflicts with VBA. A beter choice would be AbsenceDate or some other compound word.

Physician seems to be a name in the t_Physician table. Do NOT repeat this in any other table. That would be duplication of data.

Tracking vacation and absence data is completely separate from what I see as the original purpose. There is no reason that you can't use the database for alternative reasons but the only link is from the physician table to the utilization table.

The t_DrUTZ table should not have fields named VacTime and AdRecOff. There should be a reason field that contained the reason for the time off such as Vacation, Funeral, Sick, etc.

 

[danbl]

Points well taken ... the vaction time, Admin time need to be in hours and tracked seperately. The reson to link the tables is to gather procedure times which are ion the Temp Table. Data for the vacation sick and admin time are only entered monthly and need to be calculated against total time which is gathered from the temp table by physician.

 

[Pat Hartman]

the vaction time, Admin time need to be in hours and tracked seperately

Then add a separate field for hours. You would then have a reason field and an hours field. If both happen on the same day, you would create two records. This method is the correct, normalized structure and it allows for infinite expansion. Your method is not normalized and is limited to two choices. If you come up with a third, you will need to change EVERYTHING! The form, the reports, the query, the calculations. With the correct method, you simply add an option to the reason field and nothing else needs to change.

 

[danbl]

Pat,

I think I need to redo this whole table and query .... are you will to look at this when I get it redone??

Dan

 

[danbl]

Very Frustrated !!!

I have tried several approaches to come up with a working relationship and have had no success.

Can someone assist me???????

I need to be able to track utilization by physician with the following variables; time off, admin requested time off and sick. It would seem that I could put this in one table but I need to pull some time values from the "temperature monitoring" table. Relationship window previously attached works for the current report by use of the "Vid". When I try to get a relationship the temp table with Vid in any new Utz table I get no data from that table.

I am stuck, frustrated, and in need of some help from which I can learn

Thanks .... Dan

 

[jdraw]

I will try to help. However, my comments in post #2 are still my advice to you.
I think that Pat Hartman is saying much the same thing.
If the purpose of your database is changing, then you must be specific in WHAT exactly you are trying to do.

I once had a need to keep track of TimeWorked, TimeVacation, TimeTraining, TimeAbscent, etc.
The approach was to create a tblEmployeeTimeOccupier with EmployeeId, TimeType, TimeDateStart, TimeDateEnd, TimeNote ..... This approach was sufficient to account for how People's time was spent.

Part of the frustration you are experiencing may be because of lack of Business Facts.

This

I need to pull some time values from the "temperature monitoring" table. Relationship window previously attached works for the current report by use of the "Vid". When I try to get a relationship the temp table with Vid in any new Utz table I get no data from that table.

is not helpful to me.

I have no idea what a Vid is, nor a Utz. Meaningful table names and a clear statement of what exactly you are trying to do would improve communications tremendously.

And from the readers point of view, until we understand your situation, we are hard pressed to provide any meaningful advice.

 

[danbl]

I appreciate your help and suggestion to better to communicate my issues.

Here we go;
there is a table that records Visits and the primary key is "vid" and the foreign key in the other tables is "vil". Queries and reports are generate from this with no issues.

I need to add a new table to track physician tine utilization. This table should have the following fields: a primary key, a date field, total time available field, a time off field, and time off reason field. Time available and time off should be in hours.

A data form would be created to enter the data and would be entered on a weekly basis as we see it presently.

From the current relationships between the Visit table and a table called t_Temperature_Monitoring, I have been able to create a query which gets the visit date, physician from the Visit table, and anesthesia start and anesthesia finish from the t_Temperature_Monitoring. Within the query there is a calculated field to get the total time for an individual event. I need to be able to have the two date fields relate or some other relationship that enables me to get at the two fields in the t_Temperature_Monitoring table.

I need to use the added utilization data so I can total the time and get the % of utilization by each physician in a report.

Does this help??

Thanks Dan

 

[jdraw]

Sorry but you're in jargon mode and you are telling the reader How you want to do something.

Here's my quick take on it and I am not familiar with your environment.
(If I had to describe your requirements/opportunity to the person behind me at mcDonalds)

WHAT I'm trying to design is a database system about Physicians, Patient Visits, and Patient temperature Monitoring. A special concern is the involvement of anesthesia.
The key use of this system will be our ability to track physician time utilization.

Not too much to go on and I can already see the questions.

Do you work in a hospital?
How many Physicians?
What does anesthesia have to do with Patient Visits or temperature monitoring?
Who is going to use this system?

 

[danbl]

Sorry but you're in jargon mode and you are telling the reader How you want to do something.

Here's my quick take on it and I am not familiar with your environment.
(If I had to describe your requirements/opportunity to the person behind me at mcDonalds)



Not too much to go on and I can already see the questions.

Do you work in a hospital?
How many Physicians?
What does anesthesia have to do with Patient Visits or temperature monitoring?
Who is going to use this system?

Do you work in a hospital? NO Surgery Center
How many Physicians? 8
What does anesthesia have to do with Patient Visits or temperature monitoring? The Visit table holdes the basic information; Date of service, Date of birtth, Physician. The "Vid" is the primary key in the visit table and the foreign key in all the others ie. "t_Temperature_Monitoring" table, this table holds the anesthesia start and finish time which is used to calculate procedure time.
Who is going to use this system? one data entry person and two Administrative staff

The goal is to have a table with the following fields; Date, Physician, Type of Time Off, and hours. This needs to relate to the Date in the Visits table and thus get the anesthesia times from the Temperature Monitoing table.

When I add the table to the query which I am using and try to relate them by adding the Vid foreign key, no data appears in the query. Remove the table and the data is in the query.

 

[jdraw]

Sorry, you're still telling HOW, not what.

My earlier post was just to point out that you have not provided enough information for us to help you with design. My questions were mock ups of the obvious questions that anyone hearing your general request would have.

Where and why is anesthesia time involved in your issue? Just tell us in plain English WHAT you are trying to do. Forget Access for the moment -What business are you in? What things do you deal with? Are these things related?

 

[danbl]

I guess I thought I was telling you what. Sorry. I will try to do this from a different view.
This database tracks quality indicators for a surgery center. This particular task is to show how much time each physician is using compared to the allocated time. The anesthesia start and finish times are stored in the temperature monitoring table. By subtracting onw from the other I can get procedure time by physician. I will total this in a report and then compare that value to the total time available to each physician. As part of the equation any physician may have vacation time, sick time or even a request to not work a certain day by the administrator.

As I see it there is a relationship between the table to be created and the temperature monitoing table in order to gather the anesthesia time values. Does that not mean there needs to be a relationship back to the Visit table so date perameter will be in line??

 

[jdraw]

?? I'm not sure we're making any headway on this.

I looked at your latest post and have reworded your requirement as follows:

I work in a surgery center where physicians perform various activities on patients. We want to create a database to track quality indicators. Each activity is allotted a specific time. One task is to show actual time taken by each physician to perform the activity compared to the allocated time.

I have highlighted in red the nouns that I see.

You will need a list of Activities and allotted times.
You will need to define and list your intended Quality indicators.
As mentioned previously, you will need some sort of table to identify "time occupiers".
I have other questions.

I’m sure that anesthesia is quite important but you have not tied it to your activities/services. Where does anesthesia fit in your services?

Please describe your temperature monitoring table. Whose temperature? Under what conditions? Patient temperature, temperature in the operating room?

Are you calling all (surgeons, anesthesiologists, cardiologist, orthopedics, neuro….) physicians?

Who is Visiting whom? Where does this tie in temperature monitoring table?
What is the relationship?

I suggested in earlier post that you read and follow the 12 steps to database.
I am providing the link again and encourage you to read the Example and apply the process to your situation.
http://www.rogersaccesslibrary.com/forum/uploads/5/12-Steps_to_Better_Databases.zip

Good luck with your project.