Multiple Tables One-to-One Problem

[djswitters]

I'm an Access novice and would really appreciate some help getting my head around this:

I have a table called Customer w/ PK CustomerID (autonumber). I then have 8 other tables, called Vendor A, Vendor B, Vendor C, etc. Each of these 8 tables has a field CustomerID as their PK, and each of these Customer ID fields in the Vendor tables are connected to the single CustomerID field in the Customer table w/ one-to-one relationships.

In my form, whenever I add data for more than one of the Vendor tables, I get the error message "You cannot add or change a record because a related record is required in table Customer"[/color blue].
Am I missing something fundamental about relationships that this is an inoperable relationship in the first place? I tried adding the Customer data in first with a seperate form and then going back with my main form to edit/add the Vendor tables data but got the same message.
Thank you for any help you can offer. I'm trying to read everything I can about this but can't find a solution.

 

[dhookom]

I can't imagine why you need 8 other tables named VendorA...etc. This is a very unusual structure and requires some explaining...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

have you read the document listed below? That will explain a lot about the relationships and how to structure your tables.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[djswitters]

I think I made this problem unecessarily complicated. Since all of the Vendor tables contained identical data, it makes more sense to just create a one-to-many with the Customer table as parent and the Vendor table as child. But I didn't realize it until I started to answer the replies-- thanks for the responses, I'm on a better track now.