Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Appending records (XP)

    I've beaten my head against the wall enough about this problem, and finally I think I understand that I cannot update a query that's based on a many-to-one-to-many relationship. I accept that...but there's gotta be a way to do what I need to do. Here goes:

    The tables: tblCourses and tblStudents. I want to somehow display (with a form and a report) all the courses that all the students have completed since 1 January 2000. I built a junction table, tblStudentCourses, that uses the Payroll ID (PID) of the student (students are also employees) and the CourseKey as a combination Primary Key. This same junction table also has two date fields to enter the date a given student completed a course. (Why two dates? One is "historical"

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Appending records (XP)

    Access can import Excel files as well as CVS files.
    File / Get External Data / Import and change Type of File in the combo box down the dialog window.
    When you have your data in a table you can play with Append queries to move the data to your junction table.
    HTH
    Francois

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Appending records (XP)

    Thanks, Fran

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Appending records (XP)

    Why don't you import the data into a temporary table then do an append query to add to the other table from your temporary table.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Appending records (XP)

    You would need to add an outer join to the the tblCourses table or a subquery on that table to return the Title number that matches the text title in your imported table. It would be the Title number from tblCourses that you would insert into the record being appended.
    Charlotte

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Appending records (XP)

    Thanks, Charlotte. I'll try that and see if I can make it work.

Posting Permissions

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