Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    Northfield, Minnesota, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combining records (Access 2000)

    I have records in an old dBase table that I have imported into Access that look like this
    TUTOR(name), STUNUM, DEPT, YR, EMAIL

    Trouble is that there is no key field, and it is possible to have a TUTOR that tutors both MUSIC, SPANISH, MATH,...and so on

    Many times the tutor might even have two records for tutoring the same subject in this mess.

    I would like to change the structure of the table so that we have records that look like this:
    TUTOR_STUNUM, DEPT1, DEPT2, DEPT3, DEPT4, DEPT5, DEPT6

    I will link this table to the STUDENTS table for the EMAIL, YR, and first and last name of the tutor.
    The DEPT1 would be their first choice of subjects to tutor in and a tutor could have empty fields for DEPT2 through 6.
    How can I write a query to create this new table out of the old one? Is there an easy way to do this?
    Thank you for all of your help.

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

    Re: Combining records (Access 2000)

    You have a many-to-many relationship between tutors and subjects. The "correct" way of storing such a relationship is in three tables:
    - A tutors table, containing a unique TutorID, the tutor name and other info specific to the tutor.
    - A subjects table, containing a unique SubjectID, the description of the subject and other info specific to the subject.
    - An intermediary table containing unique combinations of TutorID and SubjectID, plus info specific to the combination, for instance a preference level.
    It seems to me that your table is more or less the intermediary table. You'll need to weed out duplicates; you can use the FindDuplicates Query Wizard to find duplicates.
    The structure you propose make it more difficult to create reports etc. A question such as "how many subjects are taught by each teacher?" can be answered very easily with the three-table structure, and only with a lot of work in the structure you propose.

  3. #3
    New Lounger
    Join Date
    Feb 2003
    Location
    Northfield, Minnesota, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining records (Access 2000)

    You have a very good points. I will change my design. However, I have a ColdFusion page that expects to find the records with
    TUTOR_STUNUM, DEPT1, DEPT2, DEPT3, DEPT4.
    I am supposed to send out a link to this thing out tomorrow. So I need to do it wrong one more time so that I don't need to rebuild the tables, relationships and ColdFusion pages before the students start leaving campus.
    Is there an easy way to convert the multiple records into a record for each tutor?

Posting Permissions

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