Results 1 to 13 of 13
  1. #1
    Macawac1 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    35

    Another friendly eye please on initial design - given up and started again!

    Hello all,



    I have given up and started again! I don't know enough and was going around in circles so different subject this time that I thought may be a simpler introduction to learning.

    Subject - employee training records.

    I have put together what I believe is the bare bones of it:

    Click image for larger version. 

Name:	EmployeeTraining.png 
Views:	26 
Size:	63.0 KB 
ID:	31590

    There are more considerations that I need to figure out but wanted to get this bit right first.

    Other considerations:
    Employees are from two different companies (administered centrally so employees from either company could attend courses together).
    Link Instructors to Training Providers - however some training is "in house" so employees could be instructors. I thought I could add both companies to the training providers to achieve this but not sure at this stage in my understanding and I cannot figure out then how to link the "in house" trainers to employees).
    Showing course locations - External providers may have their own venues, or may come to either one of our company sites and the same with in house training - may be at one site or the other or both.

    Would greatly appreciate some advice on whether I am on the right track?

    Many thanks

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    You certainly need a table tblCompanies, but you can easily do without tblProviders (providers are too companies?) There are 2 possible structures for companies table:
    1. tblCompanies: CompanyID, CompanyName, CopmpanyType, where CompanyType = p.e. 1 for both your companies, and 2 for every other, and provider may be any company registered in tblCompanies.
    2. tblCompanies: CpmpanyID, CompanyName, IsClient, isProvider, where for both your companies IsClient = True, and for any other IsClient = False, and providers can be all companies with IsProvider = True.
    3. You don't need a table tblProviders with this setup anymore - you select provider from tblCompanies instead.

    Add CompanyID into table tblEmployees (about this table will be more later).

    Do you really have several providers for course? When yes, then do you have several providers for course run too? What is a course run - a same course run at diferent dates? I'm asking this because:
    a) When you run the course repeatedly, there is s possibility, that the provider will change. So you are avoiding some problems in future, when you link course provider(s) with tblCourseRun instead of tblCourse.
    b) When you have only one provider for course run, then there is no need for linking table (tblProviderCourse in your schema) at all - you can simply add a field ProviderID into tblCourseRun. When several providers can be managing the same course run, you'll need a table tblCourseRunProviders;

    Like tblProviders, you can do without tblInstructors too. Rename tblEmployees p.e. as tblPersons (2 possible designs):
    1. tblPersons: PersonID, CompanyID, FirstName, LastName, PersonType.
    2. tblPersons: PersonID, CompanyID, FirstName, LastName, IsEmployee, IsInstructor.
    Use fields PersonType, IsEmployee, IsInstructor will be like for similar fields in tblCompanies.

    And as last, can a same course run have several instructors, or only one. When several, then you'll need a table tblCourseRunIns, otherwise a field InstructorID in table tblCourseRun is sufficient. InstructorID is set egual to PersonID from table tblPersons (is selected from there) for either of options.

  3. #3
    Macawac1 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    35
    Thank you Arvilaanemets,

    I need to digest your advice and take some time to think and understand - hee hee that may take me a while!

    Will post back when I think I have understood and had a go at applying it.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Did you work through a couple of the tutorials at RogersAccessLibrary?
    It's a great learning tool, but you have to do the work.

    Here's a link with lots of info on database design and planning and the tutorials.

  5. #5
    Macawac1 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    35
    Hi Orange

    I am ashamed to say that I didn't. I have been very very naïve. I thought because I had built one many years ago that worked successfully for my husband in his business I could just jump back on and do it again. Needless to say I have forgotten the months and months of learning I did beforehand to achieve this (taught by two different huge white Microsoft books as we didn't have internet then). I need to take a step back and start again from the beginning (my memory is not so good now either so believing I could remember something from so many years ago is wishful thinking).

    I am trying to run before I can walk.

    I have book marked the site and will work through some of the tutorials - although this will take me a bit of time - and I am back at work next week after having this week off so less time to play!

    Thank you :-)

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Good stuff.
    The Rogers tutorials are a little older, but have a problem statement, a procedure and solution. Once you've done one or 2, you can set up a similar approach with your own data/requirement. Get a data model that you can test.
    Here's a link that may be useful.

    There are some good youtube videos as I mentioned in my link --some for analysis, design, concepts...

  7. #7
    Macawac1 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    35
    Thanks again Orange,

    I am not too good with videos - I prefer the written stuff that I can go through at my own pace to understand (and to re-read little bits again and again without losing my place!). I tend to keep YouTube for plumbing solutions and the like when I need a visual on the bits they are talking about - oh - and music of course!. I have your link in the thread now as reference so will take a look - I may even surprise myself!

    All the little bits I have been doing so far I have asked my husband to sit down and "break it". Needless to say he did several times.

    Study period coming up but :-

    Christmas is coming......
    Back to work on Monday......

    Ohh errr - there are just not enough hours in the day!

    Again thank you all :-)

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Since you are rethinking the design, may be moot but your diagram shows tblCourse link to tblProviderCourse on CourseID and ProviderID fields.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Macawac1 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    35
    Oh! Duh!

    Clumsy mouse control - it would have taken me ages to spot that - even without the kaleidoscope eyes I seem to have now! (Incidentally - it does go to the right one on my drawn out diagram - does anyone have a magic bit of something that puts drawn out things directly into Access?)

    Thank you June7

  10. #10
    Macawac1 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    35
    I cannot figure out how to edit a post - get lost and have to refresh which loses it altogher (more studying required!)

    Just wanted to say June7 - rectified now!

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I find that I cannot edit a new post when I already have other posts in a thread without refreshing first. So trying to make it a habit to refresh browser immediately after adding a post. Glad to know I am not the only one who experiences this. Did not used to be like that.

    The forum does auto-save post edits at regular intervals so if edit is aborted for whatever cause, might be able to recover at least some of the input - but be sure to refresh browser in above described circumstance.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Macawac1 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    35
    Hi again,

    Thank you June7 for the refresh advice.

    Orange - I have had a look now at Rogers Access. I will be going back there many times!

    ArvilLaanemets - thank you - I have had a digest of your advice and a play now.

    I have had a re-design based upon your insight and so tblEmployees is now tblPersons. tblProviders has gone and is now incorporated within tblCompanies. I have done a couple of things slighty differently though:

    tblCompanies:
    I have put in a Yes/No field for IsExternalTrainingProvider - If "no" then it is one of our two and therefore internal. (Field defaulted to yes for future company entries)

    tblCompanyRole:
    Of our two companies some roles apply to both and some are unique to just one or the other - hence the "tblCompanyRole". I have included "external instructor" in tblRoles that I think links to identify the company, person and related stuff.

    I think this negates the need for IsEmployee, Is Instructor.

    But ... I am still learning!!

    I am trying with this learning project to put together an employee training log for two related companies. I would like it to record both external training carried out by third party providers as well as various types of internal training - eg. a simple individual company induction or ongoing "on the job training" (hence the tblModules which is to document the various stages of "on the job training"). At the moment the "internal company trainers" are not defined within either company and so I plan to leave that as a blank canvas at the moment - but still have the ability to record a "mentor" as such until this can be addressed.

    This is where I am at now:

    Click image for larger version. 

Name:	NewRelationshipDiagram.png 
Views:	18 
Size:	114.3 KB 
ID:	31622

    Again - Thank you all



  13. #13
    Macawac1 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    35
    Apologies - had to reply again as not good with editing posts at the moment.

    Forgot to say:

    I am a little confused as to how to deal with the relationship between tblPersons and tblCourseDetails when one entity in tblPersons relates to two in tblCourseDetails. I think this is correct as I can sort of track it all through but not sure really!

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

Similar Threads

  1. Replies: 6
    Last Post: 12-06-2017, 07:59 AM
  2. Initial Design Help
    By Mattyn in forum Database Design
    Replies: 3
    Last Post: 07-05-2016, 04:10 PM
  3. Replies: 3
    Last Post: 11-07-2015, 10:13 AM
  4. Initial Design and Querying
    By TheBrigg in forum Access
    Replies: 2
    Last Post: 12-06-2011, 08:12 AM
  5. Need help on initial design
    By allykid in forum Database Design
    Replies: 2
    Last Post: 11-09-2010, 01:46 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