Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Referential Integrity Requiirements?

Status
Not open for further replies.

Chris121

Technical User
Aug 18, 2003
34
GB
Hello,
I have designed an Access Database for my friends heating business. I have just 2 tables [customers] and [servicing]. Both have numerical ID's for each record. [customers].ID is autonumber and [servicing].ID is number. The ID in each case is the tables primary key. The [servicing] table includes several fields from the [customers] table (title, surname, phone# etc.)

A 1 to 1 relationship links the two primary keys.

So that a [servicing] record is created whenever a [customer] record is added, or removed when a[customer] record is deleted, I enforced referential integrity with update and delete boxes ticked.

Unfortunately the auto update and delete dont work. The database works fine if you manually add or delete [servicing] records when adding or deleting [customers].

Can anyone tell me what I've missed?

TIA
Christine.
 
I would name the ID field in Customers "CustomerID" and in the Servicing table "ServiceID". Find and use a good naming convention before it gets too late and creates too much work.

Don't you expect repeat business so there might be multiple Service records for one customer? I would add a CustomerID field to the Servicing table with a 1 to many relationship between customers and servicing.

I wouldn't delete records from the Customer table. Add a field for CustomerStatus so users can set them to Inactive.

Duane
Hook'D on Access
MS Access MVP
 
Thanks for the advice Duane, Les

I guess I should have mentioned - (gulp!)....

I actually did this database a year ago. It came back to me with 1500 records in [customers] and 1466 records in [servicing].
I filled in some data in the missing [servicing] records and got back to 1500 in both.
Is there a quick fix to prevent this problem recurring or is starting over the only way?

"Christine hangs her head in shame!!
 
This all depends on your current table and relationships. I think you can simply modify what you have by adding a customerID in the servicing table and then changing the relationship.

Duane
Hook'D on Access
MS Access MVP
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top