Change 1:1 relationship to 1:M

[sv89]

Hi,

I have a table 'User' and another 'Address'. The userID is included in the 'Address' table as a FK. I'm using Access 2002 and when I go to edit the relationship, it is automatically set as a 1:1 relationship. How do I change it to 1:M?

Thanks.

 

[gemma-the-husky]

where does it show as 1:1?

any relationship is 1:m

1:1 is just an artificial constraint, surely?

 

[vbaInet]

Access decides that for you. There's something about your PKs and FKs which makes Access to believe that you have created a 1:1 relationship. Rethink your design.

Maybe in the meantime let's see a screenshot of your relationships diagram.

 

[sv89]

How/Why does Access decide on it's own?

Attached the screenshot.

Thanks....

 

[vbaInet]

Well, what you've probably done is make appID a Primary Key in the AppAddress table. Hence, the 1:1.

 

[sv89]

Ah, I know what I did wrong.

Had set appID -> Indexed: Yes(No Duplicates) in the appAddress table. Changed it to Indexed: Yes(Duplicates OK)

Thanks.

 

[vbaInet]

Glad you got it solved.

 

[Galaxiom]

How/Why does Access decide on it's own?

Access thinks it is smarter than the average developer. It decides a several things on its own that can cause nightmares for users.

Here are a few.

With AutoCorrect (AKA AutoCorrupt) on, the names of objects in queries, forms and reports automatically change when the names are changed. Not quite everywhere though, especially if the object was created in an older version of Access, leaving you to find the places where it doesn't change.

Many a new developer has changed the name of a table when something goes wrong and then imported another from a backup not realising the original name change took everything else with it.

If encountering a valid but out of region date format, Access will quietly accept it and reverse the the month and day to match the regional setting.

When importing text data, Access guesses the datatype of the destination fields by looking at the a limited sample of records. Rather than rethink its decisions, it can skip values if they don't fit the expectations established from this sample. Nice if you can't get the original data back.

I am sure others could think of more stories of the Wisdom of Access.