Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2009
    Location
    Pittsburgh, PA
    Posts
    3

    Unanswered: Multiple Records/Fields Help

    Recently I was "asked" to design an access database for my office which tracks many things including student attendance at events.

    I am having the most trouble with is how to enter the students who attend. Each event could have anywhere from 1 to 1000 students attend and I'd rather not have 1000 fields in my table.

    My current fields are:
    EventID (AutoNumber) - Primary Key
    EventName (text)
    EventDateTime (date/time)
    EventLocation (text)
    EventCost (Number)
    Guests (Yes/No)
    ***This would be where I'd put the student field(s)

    In a separate table in my database I have a table that holds Student ID #'s and each student's contact information. So I'd eventually like to run a report that pulls the name, phone, email for each student who attended (which would pull from the student contact table).

    Thanks in advance

  2. #2
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    If I'm reading this correctly you need a table that would hold the StudentID of the Student that attended the event and the EventID for the Event that was attended.

    C

  3. #3
    Join Date
    Mar 2009
    Location
    Pittsburgh, PA
    Posts
    3
    c, thats correct, the table will need to hold the student id (which will have a relationship to another table) and the event information, which includes the event id, but the event id is just a way to set a primary key for the table, since two events could have the same name, just different dates and time.... hope that helps.

  4. #4
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    You need to have 3 tables to do this. One for the Student info, one for the Event info, and one that holds the PK from Student and Event. It would be a many to many relationship.

    The Event table just holds the details about the event (Name, date, time, location, etc), not who attended. The 3rd table would just have the two PK's for the student and what event they attended. I've attached a screenshot of the relationship window.

    Hopefully this is what you are looking for.

    C
    Attached Thumbnails Attached Thumbnails Relationship.jpg  

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are recording student attendance then Canupus's design will work if their is only a single event.
    if the same or similar event occurs you may need to refine his suggestiojn to include a date element
    eg if you are recording attendance to a series fo lectures you may neeed a table that records the type of lecture, a table which records the schedule of lectures and a table that associates a student with a specific scheduled lecture. so you will then be able to see what events a student attended AND (through the power of SQL) what events they missed
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Mar 2009
    Location
    Pittsburgh, PA
    Posts
    3
    Thanks for all the help. For what I need canupus' suggestion worked great.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •