UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Complex Many To Many Relationships, Access 2010    
 
   
zfarina
post Jan 28 2018, 09:30 AM
Post#1



Posts: 17
Joined: 18-December 16



Hi All

I have a data normalisation and table relationships problem which I am sure has a simpler solution than I am currently come up with.

The database is to track doctors working in a particular department and the issue is that the department has several posts assigned to it. Simultaneously HR has a Payroll system where the doctors are assigned to particular PostNumbers. These can change during the doctors tenure in the department, but needs to be tracked for a variety of reasons. (I can't just track the posts in the department, but need to simultaneously track what HR is doing.

I think it needs four primary tables:

tblEmployees
  1. EmployeeID
  2. EmployeeName
  3. EmployeeSurname

tblDepartmentPosts
  1. PostID
  2. DepartmentPostName

tblPayroll
  1. PayrollID
  2. PayrollPostnumber
  3. PayrollPostTypeID_FK

tblPayRollPostType
  1. PayRollPostTypeID
  2. PayrollPostType


and two junction tables
tbljxnEmployees&Posts
  1. jxnE&PID
  2. EmployeeID_FK
  3. PostID_FK
  4. StartEmploy
  5. EndEmploy

tbljxnPayroll&Employees
  1. jxnPayroll&EID
  2. EmployeeID_FK
  3. PayrollID_FK
  4. StartonPayrollID
  5. EndOnPayrollID


With this structure I am then having problems with making data entry forms and quries to answer the fundamental questions I want my database to be able to answer

  1. What posts are vacant on a particular date?
  2. Which employee is in which post on a particular date?
  3. Which PayrollPost is assigned to which DepartmentPost at a particular date?
  4. Which DepartmentPosts have been assigned to which employee over time?
  5. Which PayRollPosts have been assigned to which department posts over time?


I am not sure if the issue is that I am going wrong in my normalisation, or alternatively if the normalisation is correct, then my queries structures are not what they should be.

I also wish to check that I am on the correct route with planning my data entry method on my forms (particularly with regard to how the various employment dates are entered. Obviously they are interdependent (can't start work until on payroll, but they also must be able to be independently manipulated (doctor can stay on payroll and move to another department's post etc)

I don't want to move on to this stage until I know that my data normalisation is optimised.

I attach a sample with a small amount of data and some queries.

I know that junction tables can be made without their own primary keys and use composite keys, however I have rejected this design as the same doctor can return to the department and occupy the same post. I could make a composite key incorporating the employment dates, but have found it easier to just have a primary key in the junction table for this purpose. Comments on this would be welcome.

link to file: Removed external Link
This post has been edited by GroverParkGeorge: Jan 28 2018, 11:05 AM
Reason for edit: Please refresh your reading of the forum Guidelines
Go to the top of the page
 
zfarina
post Jan 29 2018, 11:31 AM
Post#2



Posts: 17
Joined: 18-December 16



Apologies - Zipped file loaded
Attached File(s)
Attached File  SamplePosts_Jobs1.zip ( 28.43K )Number of downloads: 4
 
Go to the top of the page
 
mike60smart
post Jan 30 2018, 10:03 AM
Post#3


UtterAccess VIP
Posts: 12,610
Joined: 6-June 05
From: Dunbar,Scotland


Hi Zane

Does the attached do what you need?

I have only given an example of the Employees Payroll Details.

Attached File  SamplePosts_Jobs1.zip ( 35.43K )Number of downloads: 7

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
zfarina
post Feb 18 2018, 02:45 PM
Post#4



Posts: 17
Joined: 18-December 16



Thanks Mike

That looks very good.

Now I need to see if I can manipulate it to see if it does what I want.

Essentially I will need to promote an employee out of a post, get a new employee into the old post, and sometimes change the payroll details of the payroll post attached to the employee post

kind regards

Zane
Go to the top of the page
 
mike60smart
post Feb 18 2018, 03:10 PM
Post#5


UtterAccess VIP
Posts: 12,610
Joined: 6-June 05
From: Dunbar,Scotland


Hi Zane

Then you need to approach this so that an Employee has Many Posts

Your Main Form is based on the Post and the Subform shows the Employee currently in the Post

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
mike60smart
post Feb 18 2018, 03:31 PM
Post#6


UtterAccess VIP
Posts: 12,610
Joined: 6-June 05
From: Dunbar,Scotland


Hi Zane

You need Forms which allows you to:-

1. select a Post
2. select an Employee for that Post
3. enter Payroll and Post Type details for that Employee

Something like that shown in the attached.


Attached File  Posts.JPG ( 66.82K )Number of downloads: 13

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th June 2018 - 05:22 PM