Hi and thanks in advance for any advice,

I use a database to look after the children's charity for whom I work. We have regular trips and I normally create a new table for each event to keep track of who is signed up. So I have two tables - one called Children, with the structure:

ChildID (Primary Key, Autonumber)
First Name
Surname
etc...

and an events table, for instance Xmas Concert with the structure

ChildID (Primary Key, Number)
SignedUpForEvent (yes/no)
NumberofTickets
etc

When I create a relationship between these tables, what I hope for is that I can run a query which will list every child and quickly tick whether or not they are signed up. This seems to work sometimes, but other times, setting it up in what I believe to be the same way I get the message "You cannot add or change a record because a related record is required in table 'Children'." whenever I try to update a record.

The process I have used to create the relationship is as follows:

1 - Go to Relationships Window
2 - Show both tables
3 - Click and drag ChildID in Children table and drag to ChildID in Xmas_Concert table.
4 - Tick Enforce Referential Integrity and Cascade Update Related Fields
5 - Choose Join Type number 2

I am really frustrated. Can someone please spell out the process I need to follow to get this to work consistently. I am using access 2003.

Cheers,

Alan

Hi,

In your events table the ChildID should be a foreign Key and not a primary key.

Foreign keys are not autonumbers.

The ChildID number [Foreign Key] in the events table should match exactly the ChildID [Primary key (Autonumber)]number in your table children.

For instance if Paul Hull has an autonumber of 1 in the children table, then in the events table in the ChildID field the number 1 should also appear

Then using a query you can bring these two tables together and then drag a link from the children table ChildID field to the ChildID field in the events table and the relationship will automatically be made.

click on the relationship join line to open up the join properties and choose the type of join you require.

From the information you have provided I believe that option 1 [only include rows where the joined fields from both tables are equal] is what you need to

That way where the ChildID number in the Child table appears in the ChildID field of the events table, only those records will be displayed.

You can go about setting any other criteria that you desire in the normal way.

I hope this is of assistance.

john

Thanks John. This was very helpful. Where I was going wrong was making a primary key instead of a foreign key I think.

Muchos Gracias!

Hi Sistemalan,

Your welcome, glad to be of assistance.

John