Results 1 to 14 of 14
  1. #1
    BusDriver3 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    46

    Confusion with table relations

    Hi,

    I am currently trying to track general data for about 80 people in my Division and then make that data spit out reports. The problem that I am having is I do not know how to relate all of this data... I have about 5 or 6 tables...



    I have tried putting all of the data in one big table but I don't think that would really allow me to take full advantage of Access. The idea is that each person in the Division would be tracked and I could provide a "Training Status Report" that would include: Name, Date of Training, Topic of Training, etc... data would be pulled from the different tables and BAM, I have a report!

    I can do the Forms, Queries, and Reports... but relating all of these table is beyond me. Can anyone help me with this simple problem?

    Please see attached file.

    Thanks in advance

  2. #2
    Access_Blaster is offline User
    Windows XP Access 2010 32bit
    Join Date
    May 2010
    Posts
    339

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You have the links on the wrong field in 3 of the child tables. The primary key ContactID in Personnel should link to each of the ContactID foreign key fields in the child tables, not the ID primary key of those tables.

    Tables with one-to-one relationship could be all one table, especially if every field will have data for every person. However, max of 255 fields in a table/query.

    Multiple fields with similar names is indication data is not normalized (College Course 1, College Course 2, College Course 3). The first link by Access Blaster deals with this concept and has more references on normalization at the end. Your arrangement is easier for data entry but some data summation and querying might be more difficult. You have to decide how far you want to take normalization. I have tables with repetitive fields because it suits my needs for simplified data entry and report output. I don't search, sort, filter on this numeric data and seldom need to perform statistical analyses. I did have to find way to manipulate it in order to build graphs.

    Recommend no spaces in names.
    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
    BusDriver3 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    46
    Thanks for the links... and the advice... (I will make the changes to the tables when I repost the finished db)... I have read the links and I think that this revision will work... please see attached file.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Still need fixing, links to EvalCheckList, DOR, and PRT tables are on the wrong field.

    Just moving the repetitive fields to a child table is not normalization. A normalized structure for Training table would be more like:

    ID
    ContactID
    CollegeName
    CourseName
    Term (why is this necessary?)
    ClassNum
    ClassDate

    All those CB fields are another issue. They are Yes/No fields so does this mean not all will apply to every applicant? Can more than one be selected? Normalization could mean another table.
    TraningCB (table name)
    TrainingID (foreign key)
    CBCode

    Note that I do not use spaces or special characters. SSN(Last4) should be Last4SSN. Awards/Proof better as AwardsProof or Awards_Proof (underscore is acceptable exception but I don't use it). Gender (M/F) should be just Gender (everyone should know there are only two and you restrict their input options on the form).
    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.

  6. #6
    BusDriver3 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    46
    June7,

    So today, I read and reread the links from Access Blaster and then marinated on your advice… I have a much better understanding now.

    The posted file was proof of concept for me, just stabs in the dark and experiments to see if I could even get the relationships to work in the manner that I needed them to… and after doing some more research I found that I have to rework the tables quite a bit…

    The DB is supposed to track personnel and most of the stuff that we need to track is just One-to-One (i.e. for each Personnel entry only one EvalCheckList, only one PRT, only one DOR is needed per person)… But sometimes I will need to track One-To-Many (i.e. Personnel could take more than one College Course)...

    As far as the CB fields go, those will change… when I took this project a few months ago I had no idea what I was doing (I still don't, lol) so I used fields that I thought would work. After becoming part of this forum and reading posts I have learned a lot!

    I cannot thank you enough for taking your time to provide valued advice and observations, I will post rev3 shortly. Thanks

  7. #7
    BusDriver3 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    46
    Okay, so I am not sure if this (my relationships) is correct but it seems to work... I made a couple of test forms and I was able to update my records, I added a delete button and was successful with a cascading delete ... again, not sure if it is correct

    Please see attached file.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The training tables are linked on the wrong field. These tables are not fully normalized but perhaps this suits your situation.

    There is no form which is where I assume the delete button is so can't test behavior but sounds like you have it working.
    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
    BusDriver3 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    46
    Ok, I made the suggested changes... and made a couple of forms with buttons (I used the wizard )... I want to do this correctly, so i dont mind doing more work to make it right.

    Please see attached file.

  10. #10
    BusDriver3 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    46
    i see what you mean about normalizing data... lol... rev5 attached...

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    tbl_Transferred (transfer in or out?) should have only these fields to not repeat data:
    ContactId
    DateTransfer
    Notes

    Since ContactID is primary key in all tables, the autonumber ID is not needed in child tables. Doesn't hurt to have unless it confuses you.

    Still not sure you grasp the concept of data normalization. If every table will always have a record, and only one record, for every person, splitting into separate tables really serves no purpose. If tables were combined, I count 127 fields, well under the max 255 for a table/query. So unless you normalize the repetitive and other fields that don't always have data into subsidiary tables, one big table is probably all you need. One form for data entry, organized with tab control.
    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
    BusDriver3 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    46
    June7, I think that you are correct, I don't really grasp the concept of normalization... part of the confusion is coming from the advice that I get from my coworkers. Initially I had everything in one big table and then I thought "Am I doing this correctly"... I took an entry level Access course and that is when I got schooled and stupid (lol)...

    It all seems to work with my version at work, until I got in to the problem of College Courses (i.e. one person could take more than one class, meaning I would have to make a relationship One-To-Many)...

    The "Transferred Table" is just "adipose tissue" really, someone in the Division might ask "Hey I know Smith checked out but do we have the date that they checked out?"... I would took in the database to see what was recorded...

    I will repost a file later today, Thanks again for your support on this.

  13. #13
    BusDriver3 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    46
    So, I read a bunch of stuff about "normalization" and realized that you are correct June7... I don't have data that falls in to a model like this:

    Makers > Models > Items Purchased
    Ford > F150/F250 > Fenders/Tires/Mufflers

    I have a bunch of "questions" not data that have come at me over time...
    Example1: "When did Smith check out?"
    Example2: "Who needs flu shots?"
    Example3: "When will Smith take their PT Test and give me a date and time."

    I will follow your advice and simply put all of my fields in to one "Mega-Table". Thanks for providing sound and simple advice... ahh, clarity...

    But I still have no idea how to close this thread... LOL... Thanks again

    Occam's Razor: "the simplest explanation is most likely the correct one."

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You can use the Thread Tools dropdown at top of thread to mark Solved, otherwise, not a concern.

    Could consider the repetitive fields for child tables. As is now, limited to 3 college courses. What if someone has more? See post 5.
    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.

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

Similar Threads

  1. table relations & primary key
    By mwabbe in forum Access
    Replies: 9
    Last Post: 08-19-2010, 10:09 AM
  2. Replies: 1
    Last Post: 04-14-2010, 08:02 PM
  3. Replies: 3
    Last Post: 04-12-2010, 02:16 PM
  4. two relations to one table?
    By kannuberg in forum Forms
    Replies: 19
    Last Post: 09-27-2009, 11:25 AM
  5. Confusion
    By colenzo in forum Access
    Replies: 4
    Last Post: 07-21-2009, 06:38 AM

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