Results 1 to 11 of 11
  1. #1
    GregO is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    6

    Two instances of the same field to represent a process

    Hello,
    I am new to the forum and pretty new to Access.
    I have my tables built for a project I am working on but have a logic issue I am trying to work on before I start working on my queries.
    My project is for a professional development system.
    My tables represent:
    1. Staff demographics [pretty straight forward]
    2. The onboarding for new staff
    3. Staff Annual performance requirements.

    As part of the onboarding process, current staff provide 8 mentoring sessions to the staff being onboarded. These would just record the date for each of the 8 sessions.


    Do I use one 'MentorSession" field multiple times or do I create "Session1", Session2, Session 3 etc. fields.
    I can visualize it in a form, but I'm not sure how to lay this out in the table.

    The Form would look something like:

    Mentor [current staff person]
    Mentee [new staff person]
    Session2date
    Session2date
    Session3date
    etc

    In this process there is a relationship between staff person and a staff person.

    Thanks for any support!

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Answer:

    use one 'MentorSession" field multiple times
    table might look something like this - but you would need to provide more detail to be sure. Sort of thing would need to know - can a mentee have more than one mentor? if no, then what happens if the mentor leaves, goes on holiday or long term sick during the mentoring process?

    tblOnBoarding
    OnBoardingPK
    MentorFK - links to PK of employee table
    MenteeFK - links to PK of employee table
    SessionFK - may not be required if you are not concerned about details of the session such as name/content in which case it would be sessionNum populated with 1,2 3 etc
    SessionDate

    In this process there is a relationship between staff person and a staff person.
    don't understand what this means - a (many to many) relationship is created by the above table so are you talking about some other form of relationship?

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    In this process there is a relationship between staff person and a staff person.
    Sounds like a recursive relationship - a table has relationship to itself which is shown in query by self-join. This is used in databases to define family ancestry, manufacturing components, business hierarchy.
    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.

  4. #4
    GregO is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    6

    Two instances of the same field to represent a process

    Thank you Ajax,

    We haven't ever needed to have more than one mentor. If it ever happened it would be a rare exception. But your logic point is well taken.
    Trying to understand your response . . .
    In the tblOnBoarding example below are you saying that the Mentor (whose name is in the employee table) is linked to the Mentee (who is also in the employee table)?
    That would be where I was saying that a person in the staff table is related to another person in the staff table. Two different people from the same table.
    The only elements I need to track for that process is the Mentor, Mentee, Session number and dates of the 8 session.






    Quote Originally Posted by Ajax View Post
    [COLOR=#333333]Answer:



    table might look something like this - but you would need to provide more detail to be sure. Sort of thing would need to know - can a mentee have more than one mentor? if no, then what happens if the mentor leaves, goes on holiday or long term sick during the mentoring process?

    tblOnBoarding
    OnBoardingPK
    MentorFK - links to PK of employee table
    MenteeFK - links to PK of employee table
    SessionFK - may not be required if you are not concerned about details of the session such as name/content in which case it would be sessionNum populated with 1,2 3 etc
    SessionDate

    don't understand what this means - a (many to many) relationship is created by the above table so are you talking about some other form of relationship?

  5. #5
    GregO is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    6
    Thanks for the reply June7,
    I will need to look into that. I am referring to my effort to have one staff person working with another staff person to mentor them. If they are both in the staff tbl do I need to make any relationship between them other than pulling them both into a query? If my question seems silly - I'm new, trying to learn. :-)

    I have attached a pic of what I imagine the date entry form would look like and I would want a query to produce a similarly structured output. Click image for larger version. 

Name:	Mentor Form.JPG 
Views:	17 
Size:	41.8 KB 
ID:	47064

  6. #6
    GregO is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    6
    Ajax,
    I have attached a pic of what I imagine the date entry form would look like and I would want a query to produce a similarly structured output.
    Maybe this will help?
    Click image for larger version. 

Name:	Mentor Form.JPG 
Views:	17 
Size:	41.8 KB 
ID:	47065

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    The form does not reflect a normalized data structure.
    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.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    The form does not reflect a normalized data structure which may be satisfactory for your situation or may drive you nuts.

    It is a balancing act between normalization and ease of data entry/edit. "Normalize until hurts, denormalize until it works."
    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
    GregO is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    6
    Thanks. I'll see if I can parse it out further.

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    The only elements I need to track for that process is the Mentor, Mentee, Session number and dates of the 8 session.
    The table logic still stands, but if mentor is never going to change in the respect to one mentee, you can include that relationship in the employee table instead of the onboarding table. The rest of the fields still stand

  11. #11
    GregO is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    6
    Thanks Ajax! I'll keep working with that. I'm still at the start of this project and I want to get the tables right

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

Similar Threads

  1. Replies: 3
    Last Post: 03-17-2020, 01:11 PM
  2. Change field name process
    By Numpty in forum Access
    Replies: 6
    Last Post: 02-21-2019, 08:12 PM
  3. Query to Find repeat instances in a field
    By Daddeo in forum Queries
    Replies: 1
    Last Post: 02-04-2016, 05:25 PM
  4. Replies: 4
    Last Post: 10-30-2012, 06:32 PM
  5. How do I make a field represent other field names?
    By Alpana in forum Import/Export Data
    Replies: 6
    Last Post: 01-15-2012, 08:41 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