Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    3

    Unanswered: Variable number of fields in a table.

    Hello
    As a complete novice who's desigining a database for the first time, I'm sure there is an easy solution to this.
    I'm trying to create a database to simulate a school. As such, I have a table containing pupiles, and a table containing classes as well as "roll" and an "assignements" tables For each class (please see picture)
    I want to list the pupiles in the class, the grades and attandance for each student, and the assignements given. The problem is that each class would have a different number of students, and I don't want to create 40 columes titled "Pupile 1", "Pupile 2" etc. Rather, I would like the students to represented in one field, with the entries chosen from the Pupiles table.
    Any help would be great.
    TIA,
    Boist
    Attached Thumbnails Attached Thumbnails DB.JPG  

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Novel way of presenting school work ... Nice try tho.
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jul 2004
    Posts
    3
    It took me a moment to figure out what you ment, considering the subject matter. This is not homework. I am looking to build a managment system for our school, so that we can track students etc. The problem is that I just don't know Access.
    While we're on the topic, this might be a good time to ask a different question: Should I be doing this in Access altogether? We have a copy of SQL server that I could probably install, but I wouldn't know where to begin with it. I'm trying to get this working before the end of the summer, so the easier the option, the better.
    Thanks,
    Boist

  4. #4
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    As far as I am aware the limits to Access relate to the number of records and the number of users it can handle at the same time.

    Contrary to what a lot Access purists might or will say, I think the tables part of Access is quite easy. It is the automation via forms, macros, code and queries where the things can get you bogged down.

    Access lives and breaths on One to Many arrangements. It does really matter too much whether it is a school with pupils and assigments, a doctor with patients or an insurance agent with clients and the client policy benefits.

    I think the first thing you do is establish who is your customer/client/patient etc and then everything starts to hang off that. I think at first it pays to do a trial run on paper or with cards.

    Also, you might have a lot of One to Many such as School to ClassRooms, ClassRooms to Teachers, Teachers to Students etc.

    But if you don't know anything about Access then you will need some books and plenty of "Where there is a will, there is a way"

    Mike

  5. #5
    Join Date
    Jul 2004
    Posts
    3
    Mike,
    Thank you for your reply. I got as far as the relationship setup and so on. However, when I try to setup a "pupiles" field in the classes table(using the lookup wizard), to draw from the pupiles tables (Which is infact listed in Access as "one-to-many") it allows me to pull only one record at a time. Since classes have more then one student, and since they each have a different number of students, I want to be able to list as many records as needed (from the pupiles table) without having to create a seperate location in the classes table. Basically, if you look at the drawing below, I want the setup on the left, rather then the right.
    I have kind od a "poor-mans" solution to the problem, which is listing the classes in the pupile table rather then listing the pupiles in the class table. THe benefit being that I know how many classes each pupile takes, and I can just list them in the table as class1, class2 etc. (IE a colume for each one) But this is just a way to get around the problem instead of solving it. (Also, then I get into trouble trying to integrate the "rollbook" and "assignments" tables in. *sigh*
    Thanks again for your help
    Boist
    Attached Thumbnails Attached Thumbnails db2.JPG  

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Well ... Since this is not school work ... A little DB design is in order ...

    Classes: Make a Primary table for the class with the following attributes:

    ClassID -----> Primary Key
    SubjectID
    TeacherID
    RoomID
    Time Period
    Grade Level
    ... Etc

    Student(s) In Class: Make a related table with the following attributes:

    ClassID --------------> Primary Keys
    StudentID ------------->
    Seat Position
    ... Etc
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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