Results 1 to 3 of 3
  1. #1
    mmckenna1029 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2017
    Posts
    2

    Simplifying relationships

    Hello all and thank you in advance,



    I usually try to stick to excel although I have worked with access before on simple projects. I came upon a business problem that I could not solve easily with excel so I thought I would have a go with access.

    I am laying out the basic scenario for a training management database and could use some input for not only what's possible but also best practice. I thought I was doing okay until my relationships ended up in a big circle.


    Click image for larger version. 

Name:	Capture.PNG 
Views:	15 
Size:	37.4 KB 
ID:	27221

    As a basic summary:

    All active employees need training:
    Each employee belongs to one department
    Each employee could have multiple job titles
    Each job title could have multiple employees

    Training Material is assigned by job title:
    Each training material could be assigned to multiple job titles
    Each job title could have multiple training materials

    The thought was to create a record for each training completion and then also be able to report on what has not been completed through a query.

    Perhaps the record does not need the direct relationships...

    I am open to any thoughts because I fear I have overcomplicated this. If there is any other info I can provide please let me know.

    best regards,
    Matthew McKenna

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Usually an Employee occupies a Position
    A Department consists of 1or many Positions
    A Position has a specific JobDescription
    A JobDescription has a specific JobTitle

    I suggest you consider the issue from the Employee perspective rather than JobTitle.

    Aside, you may have 5 BusinessAnalysts all doing similar things. BusinessAnalyst may be the Title, but each would occupy a separate Position (PositionNumber). At least that's how I have experienced things.

    As for Training, here is a jpg I have suggested previously to help with similar question.

    Click image for larger version. 

Name:	EmployeePositionTraining.jpg 
Views:	12 
Size:	40.9 KB 
ID:	27224

    Hope this is helpful. Good luck.

    Update: I just found another jpg I had that was in response to a poster dealing with Employees, Organization, Departments,Positions, Projects and Tasks. I'm adding it in hopes that it is useful.

    Click image for larger version. 

Name:	EmpOrgProjTask.jpg 
Views:	12 
Size:	70.2 KB 
ID:	27225

  3. #3
    mmckenna1029 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2017
    Posts
    2
    Orange,

    Thank you for the response.

    JobTitle and department are the correct categories but I may not have described well or be using language typical of most industries. In the case I have the department is used so that a report can be pulled for employees in a particular area and sent to the applicable lead. Job titles are not unique to the departments, for instance we have machinists in 3 different departments. The job titles in this case is meant to group needed training together. If your a Class B machinist you may need training on x, x, and x. The ability to assign multiple job titles lets me create specializations, for instance perhaps this particular class B machinist is part of the safety committee and needs first aid training, etc.

    Thanks for the graphics, I'll review and hopefully find something I can apply. I know little about queries and that may be my issue. My gut is telling me that the records don't need both relationships.

    Thanks a bunch,
    Matt

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Simplifying Code
    By Thompyt in forum Programming
    Replies: 6
    Last Post: 03-25-2016, 12:26 PM
  2. Replies: 1
    Last Post: 06-27-2015, 11:04 PM
  3. Replies: 29
    Last Post: 06-19-2012, 12:12 PM
  4. Simplifying this code
    By vickan240sx in forum Access
    Replies: 8
    Last Post: 06-06-2012, 06:03 PM
  5. Simplifying a table
    By jrmvt in forum Database Design
    Replies: 5
    Last Post: 02-18-2011, 11:00 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums