Don't use a query for this. Rather use a form/subform instead.
First thing is to make sure that there is a primary key in the Collectors
Next go to Tools, Relationships on the menu. Create a relationship between
the two tables based on the Collectors table primary key field and enable
referential integrity. If it won't let you that means there's an orphan
record in the Reassignments table without a match in the Collectors table.
Fix the problem then try to enable referential integrity again.
Next create a form based on the Collectors table. After making sure that it
works right, go back to design view and put a subform on it based on the
Reassignments table. Since you have set up the relationship between the two
tables, Access should properly link the form and subform. Now when you add a
new record in the subform, Access will automatically populate the foreign key
field with the primary key in the Collectors table.
Now when you create a query for a report based on these two tables, it might
seem that there are some missing records. This will happen if you have a
record in the Collectors data table but no matching data in the Reassignments
table. Your query has an inner join and you'll be needing a Left join to see
all the records.
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.