Results 1 to 12 of 12
  1. #1
    kstrecker1701 is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jul 2018
    Posts
    12

    How many fields should my tables include?

    Hi all. I used to be a whiz with Access 20 years ago ... Now I'm having to re-teach myself and it's like I never knew any of it. Hope you can help me define how I should organize my data.



    I'm creating a database to track a mentorship program at my company. I've broken the information down into what seems like the smallest bits and created four tables:

    tblMenteeInfo: MenteeID, MenteeName, HireDate, Position, Manager
    tblMentorInfo: MentorID, MentorName, AreasOfExpertise, Position, Manager
    tblManagerInfo: ManagerID, ManagerName, Department
    tblPositions: PositionID, PositionName, Skillset

    Now I need the ability to add new information as people enter the program, such as designating which mentee gets assigned to which mentor, the date of the assignment request, start and end dates of the mentoring term, and the manager's comments on the assignment.

    Should I be creating all-new tables for each additional type of information, such as a table containing only mentee name and start/end dates? Or should I add the date info fields to the MenteeInfo table?

    Click image for larger version. 

Name:	test database.png 
Views:	26 
Size:	7.2 KB 
ID:	34842

    I'm sure I'll have many more questions after this; but I'll take it one step at a time! Thanks in advance for the help.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Can a mentee also be a mentor - perhaps not simultaneously but at some point? Suggest you have a single table for all people and their details regardless of their role (manager, mentor, mentee), call it Employees. Then another table for the mentor/mentee assignment details.
    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.

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I would set this up as a Many-to-Many situation between Mentor and Mentee tables. The junction table, called maybe Assignments would contain the start and end date as well as comments and any other data for that particular assignment.
    With this setup, you could have a form to assign Mentees to a Mentor or another form for vice-versa, or both forms. Very flexible. The one Assignments table would serve both forms equally well.
    Last edited by davegri; 07-24-2018 at 03:06 PM. Reason: sp

  4. #4
    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
    kstrecker,

    Here is a link to reference info and tutorials related to Database Planning and Design.
    If you work through 1 or 2 of the tutorials from RogersAccessLibrary mentioned in the link, you will experience a process to get to a database design that can serve as a blueprint for your database.
    Get clear description of your "business", then use the same process with your own data.

    If a Mentor can be a Mentee, then as the other responders said, you are probably dealing with a M:M structure.

    Good luck.

  5. #5
    kstrecker1701 is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jul 2018
    Posts
    12
    Appreciate all the helpful resources here. I've worked through the RogersAccessLibrary examples and I feel pretty solid with database theory. But one small detail about translating the process to Access stumps me.

    Let's say you want to create the relationship shown here from the Class Info System exercise. I understand that the primary keys from the Student and Courses tables are joined to the CourseHistory table so they can have a many-to-many relationship. BUT. If I were to view the CourseHistory table's data, it would just show me the StudentID and CourseID numbers along with the grades, correct? HOW do I also connect the students' names and the course descriptions to that junction table so they're viewable as well?

    If that involves a query, lay it on me.

    Click image for larger version. 

Name:	database diagram.png 
Views:	25 
Size:	22.8 KB 
ID:	34849

    kstrecker:
    I have moved your post from the other thread (to which I gave the link in post 4 above). That post is used as a reference to information on Database Planning and Design and related concepts/articles. Your response (this post) belongs in the thread you created. I realize this is new to you since you only have 2 posts, but for clarity and completeness this post has been moved to this thread. (
    orange)
    Last edited by orange; 07-24-2018 at 09:12 PM. Reason: post moved to ongoing thread

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Lotsa good advice, but in case you meant exactly what you wrote
    such as a table containing only mentee name and start/end dates
    then no. You would use the menteeID from that parent table, not duplicate the name in both tables. Same goes for other similar situations. Reason being is that you otherwise have to be concerned with updating ALL instances of a value when the value is changed in the parent source. Suppose Mary Brown gets married and takes the spousal last name (Smith). While you can address the issue via cascading updates in your relationships, it would mean these have to be a) established and b) all encompassing (better not miss any). Some developers don't bother with relationships, and don't have to be concerned with cascading since they'd use the ID in all "child" records.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    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
    kstecker,

    In answer to your
    HOW do I also connect the students' names and the course descriptions to that junction table so they're viewable as well?
    You use a multi-table SELECT query.

    Here is a link to w3schools SQL JOIN info and example of a multi table query showing getting info from multiple related tables.

    Good luck and glad you found the tutorial useful.

  8. #8
    kstrecker1701 is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jul 2018
    Posts
    12

    Smile

    Quote Originally Posted by kstrecker1701 View Post
    I have moved your post from the other thread (to which I gave the link in post 4 above). That post is used as a reference to information on Database Planning and Design and related concepts/articles. Your response (this post) belongs in the thread you created. I realize this is new to you since you only have 2 posts, but for clarity and completeness this post has been moved to this thread. (orange)
    Understandable. I did post on the Database Planning and Design thread on purpose, since I figured my question was universal and referred to the tutorials referenced there (although they were also helpfully posted in this thread).

  9. #9
    kstrecker1701 is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jul 2018
    Posts
    12
    Making progress! I've successfully created a simple select query based on the junction table suggested above that shows which mentee is assigned to which mentor, the mentee's position and department, and the assignment request date and notes.

    Click image for larger version. 

Name:	assignments query.png 
Views:	14 
Size:	16.1 KB 
ID:	34869

    Attachment 34868


    I've also created a form based on that query that shows each record and lets me input the request date and notes.

    Click image for larger version. 

Name:	IMG_2475.jpg 
Views:	13 
Size:	134.5 KB 
ID:	34870

    But the form won't let me input NEW records. (Sorry about the photo quality; the error message disappeared with the alt-PrintScreen keystroke!) So here's what I WANT to happen, and my hope is that someone here can provide the next step in the process for me.

    When a new mentee is hired, I want to open the form, INPUT the mentee's name, hire date, position and department; then CHOOSE the mentor's name; and INPUT the request date and any notes.

    What do I need to do to my table relationships? Thanks in advance.

  10. #10
    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
    A few questions for clarity:
    Are all Mentors and Mentees Employees?
    You show RequestDate, but how does a "mentoring" actually take place? Does something happen after Request --eg actual training/instruction/apprentiseship??
    Can a Mentee have multiple Mentors --based on area of expertise (or need)?
    How many areas of Expertise are involved? If you had a list -that could be extended - you could have a separate table and prevent typos, spelling errors.
    Position and Department often change with time -- promotion/transfer.. Do you want to store any historical data?

    Might be easier for responders if you would post a copy of your evolving database.

    Glad things are progressing.

  11. #11
    kstrecker1701 is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jul 2018
    Posts
    12
    Thanks, orange. I appreciate you taking the time to help me out. I see the value in having the entire panoply of fields and data types laid out and organized at the outset of building the database, but I'm just doing test runs on the simplest chunks of data right now so I can understand how to use all of Access' functions. I'm afraid that if I construct the entire detailed beast here, I'll get plenty of expert opinions on joins and indices and SQL and other stuff that's totally relevant, but way beyond my scope right now. If I can grasp the "how" and "why" of just these basic elements and their interactions, I'll be better equipped to analyze the rest of my data and put it to work.

    Let's pretend that our fledgling mentoring program only goes as far as assigning mentors, and so the only functions I need Access to perform are the form input tasks I noted in my last post.

    When a new mentee is hired, I want to open the form, INPUT the mentee's name, hire date, position and department; then CHOOSE the mentor's name; and INPUT the request date and any notes.
    Yes, all mentors and mentees are employees.

    After the request is made, let's assume nothing else happens. The pairings go off and do whatever mentoring pairs do. All we need to know is who's with whom.

    Each mentee can have only one mentor, but each mentor can be paired with several mentees.

    I do actually have separate tables already for AreasOfExpertise, Position, and Department, and I know how to create the relationships between their Primary Keys and the relevant fields in the MenteeInfo and MentorInfo tables. I didn't include them in the AssignmentQuery because I didn't want to muddy the waters. So I would more accurately be choosing Position and Department in this form instead of inputting them.

    And let's not worry about storing historical data on Position or Department. If I can get the input form to work as envisioned, I'll set about creating more detailed records.

    If I were to post my database as it stands now, what's the best format? Screenshots of the datasheets?

    Thanks!

  12. #12
    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
    Post database (accdb) in zip format.

    Describing the business of mentoring as you envisage , and designing your tables and relationships to support that business is NOT muddying the waters. I would refer to it as understanding the requirement and designing a starting data model.
    Do not be too quick to jump into physical database.

    Here is a link to a generic Mentoring (Naval Officers) data model at Barry Williams' site.
    This is generic model that satisfies the requirements/facts he was provided.

    Generic models at his site deal with the most common/typical needs. The models can be adjusted (change entities/delete entities/delete relationships/ add tables......) -they are meant to be a starting point to help the developer with some insight of what makes sense generally.

    As with most projects, you can build a first approximation plan. Based on your plan you can build a prototype, review and adjust your plan as you add details (uncover requirements). See my link on Stump the model.

    If at this time you just want to practice with queries and get some familiarity with Access, then I recommend this youtube series by 599CD. But you will learn by experience if you plan, analyze, design,"develop" your mentoring app.
    Just be prepared to review and refine your plan and approach as you "discover things".

    Good luck with your project.

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

Similar Threads

  1. Replies: 8
    Last Post: 10-26-2017, 05:52 AM
  2. Replies: 4
    Last Post: 07-13-2017, 09:41 AM
  3. Which fields to include in a Query
    By hfreedman1957 in forum Queries
    Replies: 8
    Last Post: 05-19-2017, 07:27 PM
  4. Include Decimals when Adding Fields
    By janmack79 in forum Queries
    Replies: 3
    Last Post: 04-24-2017, 02:01 PM
  5. Replies: 3
    Last Post: 01-05-2012, 12:04 PM

Tags for this Thread

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