Relationship issue? I need some assitance from the experts on this one.

[cnut1]

Forms used:
frmAnnualService
frmPostCardback

Tables used:
tblAnnualService
tblCustomers

I have a database created containing 2 tables. (tblAnnualService and tblCustomers). I have the Repair Order set as the primary key in both tables since it should never have a duplicate. I have a one to one relationship, enforce referential integrity and I have cascade updated related fields. The join type I chose is 3. Include ALL records from tblCustomers and only those records from tblAnnualService where joined fields are equal.

I have a couple issues ongoing at the moment. I have a button on the master form (frmAnnualService) used to input service information for each customer. This button runs and update query that updates tblAnnualService and all fields on this form are bound back to tblAnnualService.

With the setting on the relationship as listed above neither one of the tables is being updated. When I change my relationship and remove enforce referential integrity and cascade updated fields, change join type to 1. Change only include rows where joined fields from both tables are the same the tblAnnualService updates but the tblCustomers does not.

I have another form (a postcard mailer frmPostCardback) that is bound to the tblCustomers. I need to be able to from the frmAnnualService input the data, click the update query table button and have it update the tblAnnualService and the tblCustomers. I assume I do not have my relationship setup correctly. Can someone please assist? I some background with Access but need your comments to be specific and detailed so I can make sure I am correcting the issue properly.

Thanks in advance for any assistance I receive.

 

[neileg]

I don't understand how you have identical primary keys. Are you saying that you have one and only one annual service per customer? It is rather more likely that you have many annual services per customer. For this, you will need to hold the customer PK as a foreign key in the annual service table. Please clarify the business relationship, and then we can determine the databse relationship.

 

[ScottGem]

To add to what Neil said, if the form is bound to the table, then why run an update query? Bound forms are automatically updated as the values in the controls are entered.

 

[cnut1]

Neil

The purpose in the database is for scheduling of technicians to customers in the same area. Example: 3 different customers have annual service in the month of Jan. They are all in the state of New York. The goal is to be able to determine this in advance to make arranges for the tech to do all 3 while he is in the state of New York verses having to fly back and forth each time for each customer. This will then allow the company to split the tech cost between customers in turn saving each customer money on their annual service.

You are correct Neil, in that there are multiple annual services for the each customer. I set the repair order as the primary key since it can have no duplicates in tblAnnualService. Should I do change it as you say and use something else as a primary or foreign key?

 

[ScottGem]

Both tables should have their own PKs. CustomerID should be a FOREIGN Key in tblAnnualService.

 

[neileg]

You can set the repair order field to indexed, no duplicates without it being the primary key.

 

[cnut1]

I managed to work it out neileg. Thank you both for your assistance.