1 to 1 relationship (Sub-classing) problem 1

[DeanWilliams]

Hello All,

I have a 1 to 1 relationship in my DB which sub-classes the Customer data. My tables are as follows:

tblCustomer (Main table)

tblIndividual (Person-specific data)
tblCompany (Company-specific data)

tblCustomerType (The type of customer, i.e: individual/company)

The relationships look like this.

tblCustomerType 1 --------------- m tblCustomer 1 ------------> 1 tblIndividual
1 `-----------> 1 tblCompany

The problem I am having is that when I try to enter data, for example, in the Company table joined with the Customer table, it wants a matching record in the Individual table and vice versa.

I have set this scenario up successfully in the past (as in this link:

http://www.mvps.org/access/tables/tbl0013.htm),

but cannot now get it to work.

Any help would be appreciated.

Dean.

 

[JonFer]

Seems like there should be many individuals associated with a customer. Do you only track one individual?

 

[Steve101]

Dean,

Just a guess here, but try removing the relationships between the three tables (customer, individual and company); then re-create them, being very careful to create the relationship FROM Customer to Individual; then from Customer TO Company. I think that creating the relationships the wrong way might lead to the type of problem you're having if you have Referencial Integrity set on.

Hope this helps,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[danvlas]

Looks like you're entering data through a query that joins the 3 tables.

Make sure he joins are Outer Joins (arrows going from tblCustomer and pointing to tblIndividual and to tblCompany) and no join line goes from tblIndividual to tblCompany and/or viceversa. Also, make sure to include the primary key of tblCustomer and the foreign keys of the other tables in your query.

And a second thought: foreign keys shold have a default value of Null. Otherwise they will get the value 0 which I suppose does not exist in the primary table.

Now...I don't know about the tblIndividual (looks to me like it can be fully embedded in tblCustomers or the relationship should go one-to-many as JonFer said above). But tblCompany infers that one customer is part of only one company. I myself work for two companies at the same time, so this relationship may also be changed to 'one to many'

HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[DeanWilliams]

Thanks guys - problem sorted!!

All I did was follow what Steve101 said. I deleted and recreated the relationships making sure they were created in the right direction.

I didn't actually know that made any difference.

Cheers guys.

Dean