Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    9

    Unanswered: Adding multiple records

    Hi

    I have one table containing a list of pupil names with a Unique Pupil Number as key and a second table with a list of 10 subject names. Is there a way of making access create a new table with 10 records for each pupil, one for each subject, so with a UPN field and a Subject Name field?

    Thanks for your help. Nick.

  2. #2
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    if just one table, why don´t create a new one in design view

  3. #3
    Join Date
    Aug 2004
    Posts
    9
    Because I want to combine the values in the two tables

    Table 1
    UPN
    1
    2
    3
    4

    combined with

    Table 2
    Subject
    a
    b
    c

    makes

    New Table
    UPN - Subject
    1 - a
    1 - b
    1 - c
    2 - a
    2 - b
    2 - c
    3 - a
    3 - b
    3 - c
    4 - a
    4 - b
    4 - c

    Make any sense?

  4. #4
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    This should work.
    SELECT [upn] & " - " & [subject] AS [ComboUPN-Subject] INTO [New Table]
    FROM Table1, Table2;

    TD

  5. #5
    Join Date
    Aug 2004
    Posts
    9
    Thats done it. Thanks V much for your help.

    Nick.

Posting Permissions

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