Access query/form tick box problem

[Guest]

Hi,

I have two tables; Collectors and Reassignments. I have a form that has a
tick box relating to a field in the Reassignments table. It works perfectly
fine untill i try including a relationship between the two tables and then it
won't let me change anything. How can i get around this problem? I am fairly
new to access so this might be easy for some of you guys.

Thanks

Paul C
(e-mail address removed)

 

[Guest]

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
table.

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.