fields being updated the appears in more than one table

[rta]

I have created a form with four linked tables. In these tables, there are more than one primary key, with the same name. The tables are joined together through only one of the primary keys, kwp_id. My table names are approval rate, cycle time, rfcs and rev team questions. Access generated a "kwp_id" for every table on my form like so- [approval rate].kwp_id, [cycle time].kwp_id, etc....and did this for all the other fields with the same name- kwp_type, prog and col_per in all of the tables.

I only want the user to enter a "kwp_id", "kwp_type", "prog" and "col_per" once in one of the tables and have that data sent to all of the other tables in which the field appears.

How do I go about doing this? Any help is greatly appreciated.

Thnx.

rta

 

[kathryn]

Have you set up Relationships? If so, and you have chosen Cascade Update then this will happen immediately. If not, go to Tools->Relationships on the menu and set them up.

What probably happened is that when you built your form you chose all the ID fields. You don't need to do this. Once you have Relationships defined, you can select only one of the linking fields and Access knows about the others through the Relationship. Kathryn

 

[rta]

Kathryn,

The relationships have already been created. When I tried to choose Cascade Update, it gave me an error message.

But yes, when the wizard asked me to choose my fields, I picked the same field name for all of the tables.

How should the relationships be set up? Shpuld I link all the fields in the tables with same field names? If so, then I would have four different links for the four primary keys that are the same in the tables.

Thnx.

rta

 

[kathryn]

OK, let me make sure that I understand you. The primary key for each of your four tables is a multi-field primary key. For each table the primary key made up of four fields: "kwp_id", "kwp_type", "prog" and "col_per".

If this is the case, then Yes, you do need to set up the relationships using all four fields.

I am curious, why you have the data in four tables instead of just one. Do each of the tables have a lot of fields?



Kathryn