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!
Two queries needed:
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)