Results 1 to 5 of 5

Thread: Back to the beginning - again

  1. #1
    Sorbz62 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2011
    Posts
    15

    Back to the beginning - again

    Hi guys,

    Being a newbie, I have written my (large) trainng database with students and courses but again I am getting confused.

    I have students who are doing courses - one student can do many courses AND each course can have many students on it.

    I tried to follow all the many-many tutorials and tips but got myself in a right state! So I opened the student template and used that, importing my data as such ..... At the moment then I have a table listing all students (good) and a table with courses (not so good) which duplicates data, ie a single student is on a few lines if he has done more than 1 course. Get me?

    Now ..... This works at the moment but I want to tidy it up and have a report which I can generate from a combo box which lets me specify department of study.

    Waddya think?
    Jim

    PS: I'm a newb so speak English!

  2. #2
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    24,224
    Well, you are going to hear the same things all over again. A many-to-many relationship requires 3 tables.

    If you want a report by department of study, Courses table must have a field that assigns value to each course.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    Sorbz62 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2011
    Posts
    15
    Thanks. I understand that a many-many relationship requires a junction table. It's just that I am not sure if that is what I need and I am having trouble getting it all working anyway!

    It seems to work as I have it but there is some duplication of data. I will keep plugging away at it.

    Ta,
    Jim

  4. #4
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    24,224
    Some sort of ID for the student will have to be on each line of the courses. Are you saying the student name is duplicated? Or are you see the student name as lookup alias for student ID?

    Should have tables:

    Students

    Courses
    ID
    CourseName
    CourseDept
    CourseCredit

    StudentCourse
    StudentID
    CourseID
    EnrollmentDate
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    orange's Avatar
    orange is offline VIP
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    5,577
    Like June7, I'm trying to follow what you are saying.

    a table with courses (not so good) which duplicates data, ie a single student is on a few lines if he has done more than 1 course. Get me?
    The student will not be on/in the Courses table. The StudentId and the CourseId will be on the records in the StudentCourse table as June7 pointed out.

    Please show a picture of your tables and relationships.

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

Similar Threads

  1. Replies: 3
    Last Post: 01-27-2012, 03:32 PM
  2. Calculated Total with beginning Value
    By mcahal in forum Access
    Replies: 1
    Last Post: 01-03-2012, 09:46 AM
  3. Replies: 5
    Last Post: 10-17-2011, 01:53 PM
  4. Beginning and End of Filter
    By injanib in forum Forms
    Replies: 1
    Last Post: 03-24-2011, 01:01 AM
  5. Beginning Design Help
    By GenericHbomb in forum Database Design
    Replies: 30
    Last Post: 08-20-2010, 08:41 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
  •  
Tech Forums: Microsoft Office Forums