Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    120
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Building a class roster (Access 2002 SP3)

    I have a list of 1200 names of people who have attended classes at a school. One of the fields (called "class and year") in the record for each individual is the class and year of his attendance: for example, London '01, Newport '05. Each indiviual may have attended one or many of these classes, so the "class and year" field may have only one entry, or may have ten or more entries separated by commas. I do not have a complete listing of all the possible entries in this field.

    I need to construct a roster for each class that has the class name and year at the top (e.g., Newport '05) and then the list of people's names who attended. I'm sure that I'm missing some relatively csimple understanding of how to do this, but I'm sure not getting it!

    Thanks for any help.

    Bob Chapman

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Building a class roster (Access 2002 SP3)

    This is an unfortunate design - it would have been far better to have a separate record for each class attended. If possible at all, you should convert to such a design. Creating a roster would then be relatively straightforward.

    If that is not possible, you will have to create a table listing all classes, either manually or using code. You can then create a query based on this table and on your attendance table, with a calculated column

    InStr([ClassTable].[Class and year],[Attendance Table].[Class and year])

    and >0 in the Criteria line for this column. Add the "Class and Year" field from the Class Table, and the attendant's name from the Attendance Table. It should be possible to use this as record source for a report.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Building a class roster (Access 2002 SP3)

    You are going to have to do quite a bit of work to restructure your data to make it more useable.

    It needs to be organised into two tables :

    A student table with a Autonumber Primary key StudentID , and the Name of the student in one or two fields (two is better but this is not essential)
    A classes table with four fields : ClassID autonumber, StudentID, Location, Year

    (It would also be useful to have lookup tables for locations and years, but this is not essential)

    This structure will enable you do do what you want.

    Getting your current data into this structure will be harder.

    * First thing I would do would be to dump it into Excel and use Excel's 'text to columns' to separate each of the location and year pairs into a separate columns. Use the comma as the separator.
    * then use it again to separate each of the location and year values into separate columns
    * and if you like separate the name into two fields.
    * When this is done import it back into Access.

    I attach a picture of what the data might look like in Excel.

    Next step is to run a series of queries to put this data into the new tables.

    Let's leave that step until this first part is completed successfully.
    Regards
    John



  4. #4
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    120
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Building a class roster (Access 2002 SP3)

    Thank you both for your help. I am restructuring to make separate records for each attendee's presence in each class.
    Bob Chapman

Posting Permissions

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