Using "OR" relationships with many tables. 4

[JaseUK]

Dear all,

I have the following four tables: Contacts, Companies, Addresses, TelephoneNos. They all have autonumber ID fields and are all joined to a table called Link which itself has an autonumber ID.

I want to be able to join say: ContactID and CompanyID. Then another row with a ContactID and an AddressID. etc etc etc. However, access insists that a record from EACH table must be selected. How do I get around this (short of having a record in each table that is set to the default value in the Link table that is a "NULL" record)??

BTW: All fields are set so that Required = No. Any ideas?

Jason.

 

[UncleJack]

Usually this kind of information is contained in a single table. See for example, the customer table wizard. Doing so would eliminate the problem you're currently experiencing.

Uncle Jack

 

[montrose]

JaseUK,

Correct me if I'm wrong in assuming that you can have:

one company with many contacts

one company with many addresses

one company with many phone numbers

one contact with many phone numbers

one contact with many addresses


If this is correct, then you're talking about many to many relationships. Access does not, per se, support many to many relationships. You need to use junction/resolver tables to kind of force Access to recognize this. I think that's what you are trying to do with your tblLink.

So your resolver/junction tbl between Companies and Contacts would hold the primary keys (which you said were autonumbers) from both of these tbls. In the junction tbl you would change these two IDs to number, long integer fields, and remove the default value of zero. The junction tbl is then used in your query design to build your form/subfrom, and they will be "automatically" linked together (meets Accesses way of saying you've got to have both of these).

You'll then need resolver/junction tbls between companies and addresses, contacts and addresses, etc. Hope this gets you going in the right direction. I use this basic 'model' all the time, it allows for unlimited "links". Let us know if you need more help. HTH, Montrose



Learn what you can and share what you know.

 

[JaseUK]

That is EXACTLY what I needed to know. MANY thanks. Seems a bit of a complicated way to implement it - but then again Access always manages to surprise me...

 

[montrose]

JaseUK, It is indeed a bit complicated at first, and sometimes takes a little extra work in getting set up, getting your forms and queries done. But the payoff in the end is great. A more normalized structure with minimal redundant data, less bloat, etc. I always shudder when I see one table set up with a company name, contacts, addresses, etc. in them. In this day and age few companies really have one address (may have a physical address, and postal address, another location, etc.), and how many people have just one phone!? You end up with a non-relational structure (I've actually seen tables with PhNum1, PhNum2, PhNum3-what happens when you get the fourth, fifth and sixth phone numbers-add more fields?!?). Once you get it set up it's a breeze, and sure beats writing a ton of code or designing overly complex queries to do what Access was designed to do in the first place. You can use lookup tables to indicate and validate the address types, phone types, contact types, etc. Glad this helped. Learn what you can and share what you know.