Contacts DB - Single Organisation, Multiple Addresses

[benjames_au]

Hi.
Im a beginner-intermediate access user (I guess) and I am working on a stakeholder management database, which essentially enables me to separate organisations, individuals, the affiliation between the two, and addresses of either individual or organisation. (pic of relationships included).

Originally, I stored address values on each respective table (ie. org address was on the org table, individual address on the individual table). However then it became apparent that one organisation (in my line of business) can have many address sites, therefore the need to nominate multiple addresses.

So I figured that the best method would be to have an address repository, and orgs or individuals are assigned to that address via a MultiAddress table. The preferred postal address would be nominated by an address preferred field in another junction table.

Seems to work ok through the form (see form pic attached), but Im concerned that when I try to run queries/reports that I'll get duplications or other anomalies.

Can anyone provide me with some tips or advice???

Cheers

 

[Len Boorman]

Basically if an Organisation has multiple address's then you will get multiple records returned when search by Organisation.

Looking at your ER diagram there are a few questions/pointers

Which of these statements is true

An Organisation may have 1 or more address's
An Organisation may have 1 or more Contacts
A Contact may have only 1 address.

If all are true then your ER diagram should reflect not so much an Organisation Address but a contact address and thus where there are multiple contacts you will have multiple address's.

Active or inactive Contacts/Organisations. Do not have separate tables. They are after all the same entities. Simple add a field (Yes/No) to the contacts table. By the link to Address you will then have Active/ In Active address's

It does pay to actually write out statements that describe the relationships between entities in order to work out the actual relationships and exactly what thos relationships are.

My suggestion above is only my interpretation of what you have said. You maybe need to ask more questions and write down the answers.

For instance. Do you have multiple contacts for a given organisation at the same address. If you do then this needs to be reflected in the ER diagram

Hope this has helped at least a little bit

Len