How to Relate Customers

I have designed a database with ten tables linking them with foreign keys. I
have had a request however which I could use some advice on. One of my
tables is called Customers. This table holds all customers address, phone,
Homepages etc, fields. The problem I have is that some of these companies
are related even though there address could be different.

How is the best way to go about relating these Customers, without creating
another duplicated table similar to Customers? I have already tried to
create a RelatedCustomer table. The problem I had with this table was that
it had 2 foreign keys, where both of them linked to the customerid in the
Customer table. I did this so the Customer information would be correct and
not from some other source which could cause some inconsistencies.

On trying to create a query to resolve the Head Customer, and then the
related Customer, was impossible as the related table’s only fetches
information on the first foreign key you create.

Any suggestions would be appreciated, as to create more tables or use VB
Code, etc.

=?Utf-8?B?Um9ubmll?= wrote in
:

The problem I had with this table was that
it had 2 foreign keys, where both of them linked to the customerid in
the Customer table. I did this so the Customer information would be
correct and not from some other source which could cause some
inconsistencies.

This is fairly standard. FWIW, it's called a many-to-many self join. You
need a table to hold the details of the relationship vis

IsRelatedTo
( *Source (FK references Companies),
*TypeOfRelationship (i.e. Owns, IsSubsidiaryOf, IsRivalOf, etc)
Destination (FK references Companies)
)


or, to put it another way

ANYT Owns MERCK
MERCK IsRivalOf SMA
SMA IsSubsidiaryOf ANYT

On trying to create a query to resolve the Head Customer, and then the
related Customer, was impossible as the related table’s only fetches
information on the first foreign key you create.

When you add the Companies table to the query grid for the second time,
it'll have a suffix added like Companies_1, which is just an alias so that
the query generator can tell which instance it's talking about, but that
should not be a bother for you until you start digging round in the SQL
itself.


Hope that helps


Tim F

Tim

I am not sure what you are saying. I think you are saying create a many to
many relationship table. Could you explain the isrelatedto function to me.
I have only been doing this for approximatley about 2 months so new to Access
and VB

Ronnie

=?Utf-8?B?Um9ubmll?= wrote in
:

I think you are saying create a many to
many relationship table.

Yes, that's right.

Could you explain the isrelatedto function
to me.

There is no function: it is the table, with three fields as listed.

I see that I missed out the headings for the example records, which
probably did not improve its readability much! What I meant was:-

Source TypeOfRelationship Destination
====== ================== ===========
ANYT Owns MERCK
MERCK IsRivalOf SMA
SMA IsSubsidiaryOf ANYT

which explains the kinds of interaction between companies known as ANYT,
MERCK, SMA and so on. You may well actually be using numbers for your
CompanyID fields, but you should get the picture.

The Query grid should look something like this (you do need to look at this
with a fixed-width font to make any sense of it!):-


Companies IsRelatedTo
========= ===========
CompanyID ---- Source Companies_1
FullName TypeofRelat' ===========
Address1 Destination ---- CompanyID
.... FullName
Address1
.....


Note that Companies and Companies_1 are the _same_ table, but Access has to
give the second instance of it a different alias.

I have only been doing this for approximatley about 2 months
so new to Access and VB

Don't worry: all of use had only used Access for 2 months once! You are
probably still trying to get your head round tables and relationships and
normalisation and stuff -- do you have a really good book or (better) a
mentor to help?


Hope that helps


Tim F

Tim

Thnaks for your help. This has solved the problem. I wish I had a mentor
to help I would get my tasks achieved a lot quicker. I have got a book call
Microsoft Office Access 2003 Inside Out by John L. Viescas. Its a good
book and explains things very well, except the Visual Basic which can be a
bit confusing. Thats why I have managed to get quite far, but some things
just need a mentor like yourself to get me through.

Thanks

Ronnie

=?Utf-8?B?Um9ubmll?= wrote in
:

Thnaks for your help. This has solved the problem.

Glad you found the solution.

I wish I had a
mentor to help I would get my tasks achieved a lot quicker. I have
got a book call Microsoft Office Access 2003 Inside Out by John L.
Viescas.

Well respected local here, although I don't think I've seen his nare around
for a while..?

It's a good book and explains things very well, except the
Visual Basic which can be a bit confusing.

It's true that you will need some programming ability to make the most of
Access (or any other DB or forms environment, for that matter) but it
doesn't take the place of a really solid understanding of R theory and
normal design principles. A good book does help. but having a real 3-d
person to pester and who will look over your shoulder is definitely easier!

Then of course there is always the NGs here!!

All the best


Tim F