Updating tables in a multi-table query

[ajerion]

This may be a somewhat complicated problem but I'll do my best to describe the situation.

I have and Access Database (Access 2002) that connects to an MS SQL Server (Small Buisiness Server 2003). The SQL Server database contains 7 Tables.

Table A - Personal Info (Name, Address, etc)
Table B - Basketball Info
Table C - Football Info
Table D - Volleyball Info
Table E - Wrestling Info
Table F - Gymnastics Info
Table G - Track & Field Info

Table A hold the general information about the person and contains the Key field "ID". The other 6 tables also have a field "ID" that corresponds to the main table "ID" field.

I have a form in the Access Database to enter this information into the SQL database. The form is based on a single Query that pulls all the information from all the tables.

The form works great, everything works for updating information or adding a new person. Except information it wont allow input into the Table B information for new people. I know its because there is no record for the new persion in Table B, but the other 5 "Sports" tables automaticaly adds a record for the new person when ever information is entered into any fields for that "Sport".

Ive looked at everthing and have no idea how Table B is different from the other 5 sports tables.

I wasnt expecting the form to add the records into the other tables automaticaly, so because I dont now how its working on the other 5 I cant figure out why the fist one is not working.

I hope that wasnt to confusing.

Anyone know what my problem is?

Thanks,
Andy

 

[mckenneyj]

Take a look at the table relationships.
Sounds like you might have referential integrity turned on on this one

Thanks,
John McKenney
Work Hard... Play Harder

 

[ajerion]

Ive tried it with referential integrity both ways and it didnt seem to change anything.

I think solution is going to be to create records for every person in all 7 tables even if they dont need a particular sport.

Andy