[AccessD] Many to Many relationship issue

You could add a new field to the table -- a yes/no field -- that tracks old
records and then just enter a brand new one. The relationships and
information would all still remain intact and easily accessible.

Susan H.


On Thu, Apr 10, 2014 at 10:36 PM, Jon Albright
<jon.albright at hawaii.rr.com>wrote:

> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>