[AccessD] Many to Many relationship issue

I did consider the possibility of the contact person changing companies but
the history at the moment of the phone call, who the person worked for at
the time of the call and the job it was related to is important.  I figured
that if the person changed companies, there will just be a new contact with
the name of this person but there is a notes field in this persons record
that will record the changes.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
Sent: Wednesday, April 09, 2014 7:55 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Many to Many relationship issue

Here is my understanding,  it's late and I may be off base.

Many to many relationships are handled by exploiting the separate many to
one relationships from the component pieces (call - job and call-contact),
using a cross table.

Your jobcontactcall table is a (often misnamed) "lookup table" but more
accurately termed a cross table. It needs minimally, a fkcontactid field
(note: what if it was a conference call with 3 company reps?? Might need
columns for a callcontact1, callcontact2, callcontact3, etc ) as well as a
fkjobid field. And what if on the same call you discussed 3 jobs?
Disgusting stuff...

The contact table has a fkcompanyid field of course. Or you can come at
company through job as well.

CompanyContacts are a touchy business.

There is the dilemma of storing all contact details even for obsolete
contact rows, so that details of what happened with whom and where can be
maintained; yet one doesn't want to assume a contact is still at the company
either. People move all the time, titles change, etc.

Suppose a transaction was created when someone was a project manager. And
someone else takes on that role. You have to weigh whether you need to know
they were a project manager at the time of a transaction (or at least listed
as one), versus the need to know what role they are currently occupying so
as to not call them if they are no longer in that role, yet that role must
be consulted.

If you just update their role you invalidate the history.

So what you end up with is many contactid values for the same actual person,
preferably only one active at a time. That or keep detailed logs.
 On Apr 9, 2014 11:59 PM, "Jon Albright" <jon.albright at hawaii.rr.com> wrote:

> Hi,  I have a bit of a struggle with this concept.  It's been several
years
> that I have done anything with database applications..not since using 
> Foxpro.  Anyway, I work for a contractor and have a need to maintain a 
> customer table (contractor general information), a table for the 
> contact people at the contractor's companies, a projects table (jobs 
> we bid) and a table to log phone calls.  I have the tables created with an
"associate"
> table for the M2M relationship between the customer table and the jobs 
> table, another "associate" table between the customer contact table 
> and
the
> phone calls table.  I also have a one to many created between the 
> customer table and customer contact table.
>
>
>
> If I create forms for either of the two M2M examples  above, they work
fine
> as a standalone but I just can't seem to "tie" the two M2M examples 
> into a functioning arrangement where if you select a company, all the 
> phone calls made by the contact people at the company will display and 
> likewise, if I select a particular job, all the phone calls relating to
this job display.
> I have followed an example by Susan Harkins from 2004 that I dug up in 
> a search but am still unable to get beyond the basic.
>
>
>
>
>
> Jon Albright
>
> Iniki Enterprises
>
> Honolulu, Hawaii
>
> (808) 677-7800 x116
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com