Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    47

    Unanswered: Append records in many-to-many relationship table

    Hi,

    I have three tables with the following structure:

    Name: TblTeachers
    Fields: TeachersID, FirstName, LastName, Subject

    Name: TblStudents
    Fields: StudentsID, FirstName, LastName, RegNo

    Name: TblRelations
    Fields: TeachersID, StudentsID, Course

    I would like to have a function to append TblRelations with all possible StudentsID and TeachersID relationships.

    Can I do that?

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Yes you can, simpy use an unrelated append query using both Student and teachter tables

    Why would you want to do this?

    Regards

  3. #3
    Join Date
    Feb 2004
    Posts
    47
    Thanks for your response Liam,

    Can you help me out with the VBA code for this?

    I need to do this to automate the record entries into the bridging table with all possible combinations. (This is just an example).

    I tried using Dim xx As Database in VBA. It works in one mdb but doesnt work on another. Can you let me know why?

    Thanks


    Originally posted by namliam
    Yes you can, simpy use an unrelated append query using both Student and teachter tables

    Why would you want to do this?

    Regards

  4. #4
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    No need for VBA unless you absolutely need to.... You can use plain SQL to do what you want.

    If your db is not recognizing Database look at your references your missing the reference to Microsoft DAO and/or Microsoft ADO

    Also dont do "As Database" but use DAO.Database and ADODB.Database to split the two ....

    Regards

  5. #5
    Join Date
    Feb 2004
    Posts
    142
    If you are in Access then build a query to do this. Start a new query and drag both tables into it without defining a realtionship between them. If Access sets up a relationship then select it and delete it/ This will creat a cross join that will generate all possible row combinations. This is seldom used but will work and only takes about a minute to do it.
    KC

  6. #6
    Join Date
    Feb 2004
    Posts
    47
    Thanks AZ / Liam, Got it. Also got the ADO thinggy.

    How do I know update the results of the query into the TblRelations table?

    Regards.


    Originally posted by AZ KC
    If you are in Access then build a query to do this. Start a new query and drag both tables into it without defining a realtionship between them. If Access sets up a relationship then select it and delete it/ This will creat a cross join that will generate all possible row combinations. This is seldom used but will work and only takes about a minute to do it.

  7. #7
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Change the query to an append query.....

Posting Permissions

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