Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103

    Unanswered: Appending selected records.

    I'm hoping someone may have a suggestion for appending selected records to a existing make table? I've attached an Access 2000 database with a couple sample tables. Table B contains 12 records which represent a complete set for each unique "Grade" in Table A. I need to append any missing records in Table A from Table B. Table C is an example of the desired outcome.

    For additions to Table A, I need to include the correct "Manager", "Grade", "Rating", "Group", and the "Count" would be 0 since the employee doesn't really exist. I can provide further clarification if this isn't clear. I'm really struggling with this!
    Attached Files Attached Files

  2. #2
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122
    Two queries needed:
    AllCombinations:
    SELECT A.Manager, A.Grade, 0 AS Cnt, B.Rating, B.Group
    FROM A, B
    GROUP BY A.Manager, A.Grade, 0, B.Rating, B.Group;


    Final result query:
    INSERT INTO A ( Manager, Grade, [Count], Rating, [Group] )
    SELECT AllCombinations.Manager, AllCombinations.Grade, AllCombinations.Cnt, AllCombinations.Rating, AllCombinations.Group
    FROM AllCombinations LEFT JOIN A ON (AllCombinations.Group = A.Group) AND (AllCombinations.Rating = A.Rating)
    WHERE (((A.Manager) Is Null));


    And a personal comment: avoid using reserved words as field names (Count)

Posting Permissions

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