Results 1 to 11 of 11
  1. #1
    sichilaba is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Dec 2014
    Posts
    24

    table relationships problem


    Can someone help.

    I have attached the pdf of my tables

    Iam new to access 2013 and I would like to be pulling data from the 4 table:

    tblstudentsInfo which has 29 fields
    tblTerm1 which has 76 fields
    tblTerm2 which has 76 fields
    tblTerm3 which has 76 fields

    I would like to create a one to many relationship but l seem to be missing it.

    Please can someone help me.
    Attached Files Attached Files

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    You should work through this tutorial from RogersAccessLibrary. You have structure issues. Your tables are not normalized. My guess is you do not have a clear statement of requirements.
    You will learn things in the tutorial that will help you with concepts. When you finish the tutorial you will be abe to apply the things you learned to your own database issue.
    Good luck with your project.

  3. #3
    sichilaba is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Dec 2014
    Posts
    24
    Thank you Orange for the useful info.

    Kindly find attached my Attribute Grid:
    After working through the tutorial from Rogers AccessLibrary you gave me, l came up with the following:
    1) 6 tables
    2) A student may take all or a few subjects but still will have to sit for the 6 of those tests
    3) A student will take 6 tests in a year: Term one 2 test, Term two 2 test, Term three 2 test
    4) A student will get a remark depending on the Rate
    5) Please help how l should relate the tables: SEQUENCE
    Attached Files Attached Files

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    You have made a good start, but there are still normalization issues.

    I have found another tutorial on Roger's site that deals with students and course.
    Give this one a try. Let us know how you make out.

    Some of the facts related to Student and Courses:

    A Student registers for a Term
    During the Term a Student takes 1 or more Courses/Classes
    During a Term a student sits 1 or more Tests
    The Student receives a Rating/Mark for each Course Test

  5. #5
    sichilaba is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Dec 2014
    Posts
    24
    Hi Orange

    I went through the tutorial you gave me. I really appreciate.

    But looks like l seem not to fully understand.

    I have attached format of the way the report will be displayed for the the Term One table.

    1. There are 3 terms
    2. Each term a student is required to write 2 tests (meaning a student will have to write 6 test. 2 test term one, 2 tests term two and 2 tests term three
    3. Basically each table will have the same format as reflected on the attached
    4. Please help me come up with the ideal number of tables required and their respective fields
    5. This will help me with the table relationship

    Thank you in advance
    Last edited by sichilaba; 12-17-2014 at 02:34 AM. Reason: forgot to attach the report format

  6. #6
    sichilaba is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Dec 2014
    Posts
    24
    Hi Orange

    Kindly find attached sample of the table format
    Attached Files Attached Files

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    You have done well to put things into grids. But WHAT does each grid represent?
    If these are meant to depict tables, then what names would you assign?

    I was expecting an ERD (Entity Relationship Diagram) along the lines of this. These are the facts that go with this model.
    Your model may be different, but similar in concept -- tables and relationships.

    Each table should be about a single entity (person, place,idea, concept, thing..)

    Student
    Class
    Subject
    Test/Exam
    Mark/Rating

    In your grids, how do you relate Student with Class?

  8. #8
    sichilaba is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Dec 2014
    Posts
    24
    Hi Orange

    Kindly find attached refined attribute grid.
    Attached Files Attached Files

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    Attached is an overview model that I have created. The boxes represent Tables, the lines indicate proposed relationships.
    The boxes with "orangey" backgrounds are comments to help put the tables into context.

    I don't know your exact requirements so this sample model may make assumptions/guesses that are not consistent with your problem/opportunity/instructions. Use it as a starting point. Use your test data to see if this model satisfies your business facts/requirements.

    You can get Class summary information by processing all records in the StudentClass table.

    I hope the model is helpful to you. Again, it may not represent your instructions or requirements.
    Good luck with your project.
    Attached Thumbnails Attached Thumbnails StudentsTermsSubjects.jpg  

  10. #10
    sichilaba is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Dec 2014
    Posts
    24
    Hi Orange

    Thank you for the suggested model. Let me study it, set up the tables and see how it works.

    I will get back when lam done

    Thank you Orange for you patience

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    Make sure your test data and the model work together. You can test this without creating a database.
    Print off a copy of the model. Use a pencil and paper to set up some data in the tables. Use the model to see if you can get the information you need based on the model. If some scenarios do not work, then adjust the model accordingly.

    Good luck.

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

Similar Threads

  1. Replies: 7
    Last Post: 10-03-2014, 05:50 PM
  2. Replies: 5
    Last Post: 09-09-2014, 01:39 AM
  3. 2 one to many relationships in the same table
    By lefty2cox in forum Access
    Replies: 5
    Last Post: 05-24-2013, 08:06 AM
  4. Table Relationships
    By carrod65 in forum Database Design
    Replies: 15
    Last Post: 04-04-2012, 09:22 AM
  5. Relationships problem
    By cedricleo in forum Database Design
    Replies: 3
    Last Post: 09-21-2011, 05:10 AM

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