Referential Integrity Requiirements?

[Chris121]

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.

 

[dhookom]

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

 

[lespaul]

you should also read up on what Duane is talking about (which is Normalization)....here's the standard intro to the topic...

http://r937.com/relational.html

Leslie

Come join me at New Mexico Linux Fest!

 

[Chris121]

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!!

 

[dhookom]

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