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!!

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

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

Sam,

Let me know if this helps??

Sent you a Private Message as well.

Dan

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

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

I don't think that would hurt matters.

By the way, what's DOB?

Sam

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

Yeow! Makes me tired just to read it!

Best of luck,

Sam