Normalization Killed my Database

[BrentG]

Hi Guys,

I decided to normalize my addresses out of COMPANY and CONTRACT entries into a seperate table. This idea works fine until I try to add new entries or update existing entries.

Normalization has made it impossble for my database to function properly.

Heres the situation: Company Mainform, AddressSubform.

AddressID PK for Address
AddressID FK for Company

Add New Company: Enter Company Name, Company Address ID Defaults to 0 (which is not an AddressID), I insert code in the AfterUpdate of the CompanyName to make AddressID "", ok, insert rest of company data.

Insert Unit or Street data for Address, get error message saying I cant enter this data, push ok, continue to enter data, enter new autonumber Manually in AddressID field for Company, pull hair out, scream a little, Close.


... This is the only way I can find of making this happen. The problem is that the AddressID for company needs an AddressID from Address that hasnt been created yet. The error message when entering address information has to do with the fact that the AddressIDs wont match up for this record.

An examples attached... i think its a principal anomoly... let me know if you can resolve it..

Thanks!

BG

 

[ansentry]

You have your relationship backwards, if you want 1 Company can have Many addresses.

You have it as 1 Address can have Many Companys.

Have a look at your relationship.

Here is your sample back with 1 customer many addresses