If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Adding New Relationship

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 14
Question Adding New Relationship

Hello to All,

I have used Access for several years and at best I guess I am between beginer to intermediate as far as experience goes. I learn best by being shown how to fix the problem.

With that said I have a database that tracks healthcre quality data. I have a vists table with a primary key Vid and this is the foreign key in the related tables. At the present time everything works fine producing alll the reports I need.

However I now need to add a table which will hold available time, vacation time, sick time, management requested time off,etc which needs to be broken down by individual. While visits are entered on a daily basis, the new data can be entered between once a month to three or four times a month. I see this as meaning I need a seperate table for this date information and individual data??

In one of the tables which already exists, I have values for start and end time(s). In a future report I plan to calculate the total time used and then compare that to the time available by individual. This is the long way to get to the question of HOW TO SETUP THE RELATIONSHIPS???

I can provide the the relationship info if you can post attachments?

Thanks to anyone who will help me with this!!
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: May 2004
Location: New York State
Posts: 1,177
Quote:
However I now need to add a table which will hold available time, vacation time, sick time, management requested time off,etc which needs to be broken down by individual.
Is there any reason why most, if not all, of these new fields cannot be part of a single record? To put it another way, is there any reason you need multiple records to record this data for a single individual? If you don't need multiple records, why don't you simply add the fields to the Personnel table? Then you don't need any new relationships at all. If you do need multiple records for a single individual, simply make a one-to-many relationship from the PK in your personnel table to the FK in the new table.

Sam
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 14
Post

Quote:
Originally Posted by Sam Landy View Post
Is there any reason why most, if not all, of these new fields cannot be part of a single record? To put it another way, is there any reason you need multiple records to record this data for a single individual? If you don't need multiple records, why don't you simply add the fields to the Personnel table? Then you don't need any new relationships at all. If you do need multiple records for a single individual, simply make a one-to-many relationship from the PK in your personnel table to the FK in the new table.

Sam
Sam,

First thanks for the reply and I apologize for the delay in answering your questions. Work calls and I have not had time to check back.

The element could be stored in one of the current tables I guess. One of the brain cramps I have is how using the visit table would be affected when this data is not a visit and only relys on the date field. Next issue is that the utilization values are not entered daily and could be done once at the end of each month for each provider. I get what your saying on the Pk/Fk relations.

I have attached a zip of the current relationship(s) to see if this will be of value to you in understanding what I am trying to do.

Thanks again ...look forward to further help
Attached Files
File Type: zip Relation.zip (107.5 KB, 2 views)
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: May 2004
Location: New York State
Posts: 1,177
It sounds to me that you have an existing table with work-related data, and that table is sufficient for its needs. Now, however, you want to track personnel data as well.

I can't extract your zipped file on my computer for some reason. Not your fault. I can never seem to do so. In any event, I have no way to see your tables. Am I close to the mark?

If the above is true then you do need a new table. Maybe you should even have a third table for the personnel info such as name, ssn, salary, etc. if you need that info as well. What I'm getting at is that it's easier to enter "445" as an employee number then to enter "John Q. Public" 74 times. The only way that'll happen is if you make this third table, even if you don't need any other info.

Sam
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 14
Question

Quote:
Originally Posted by Sam Landy View Post
It sounds to me that you have an existing table with work-related data, and that table is sufficient for its needs. Now, however, you want to track personnel data as well.

I can't extract your zipped file on my computer for some reason. Not your fault. I can never seem to do so. In any event, I have no way to see your tables. Am I close to the mark?

If the above is true then you do need a new table. Maybe you should even have a third table for the personnel info such as name, ssn, salary, etc. if you need that info as well. What I'm getting at is that it's easier to enter "445" as an employee number then to enter "John Q. Public" 74 times. The only way that'll happen is if you make this third table, even if you don't need any other info.

Sam
I have a table with the individual provider names and a PK that is associated with all the other tables. there is a visits table that holds date, DOB information etc, and a PK which links to five other tables. I think you are right about a third table. My concern is with using the visit table as part of the third table. For instance there would be no DOB data when entering utilization hours values into the third table. Would this cause any issues with how the rest of the tables work?? I don't think so but I am not knowledgeable enough about access to know.

Dan
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 14
Sam,

Let me know if this helps??

Sent you a Private Message as well.

Dan
Attached Files
File Type: doc relationship1.doc (146.5 KB, 2 views)
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: May 2004
Location: New York State
Posts: 1,177
Dan,

What's the 'time' field in the t_physician table?

My angle is that I'm wondering if it can be removed from there and put instead in the new t_DrUtz (do I read it right?) table, and use the t_physician table solely for personnel info. You would then isolate all working/vacation time info and put it into the other table. Of course, you would make a one-to-many relationship between the two tables based on DrID. You could also remove the 'Physician' field from the new table.

Sam
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 14
Sam

The time field was just an initial idea and can be moved where ever is best.

My other concern is with the visit table. If I use it for the date values and the one to many relationships already established, is there any adverse affect by not entering data in to the DOB field?? This would affect the find duplicate(s) queries that I have created, I think??

Dan
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: May 2004
Location: New York State
Posts: 1,177
I don't think that would hurt matters.

By the way, what's DOB?

Sam
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 14
DOB (Date of Birth)

I will try the new table idea and let you know.

be a couple days as I am back working three 12 shifts back to back.

Dan
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: May 2004
Location: New York State
Posts: 1,177
Yeow! Makes me tired just to read it!

Best of luck,

Sam
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On