[AccessD] Many to Many relationship issue

Paul Hartland paul.hartland at googlemail.com
Fri Apr 11 01:05:44 CDT 2014


Arthur,

Not kept up with this thread, so appologies if similar to what I put have
been covered.


This sounds like a similar problem to a situation we had at an old place of
work of mine, we would mainly employ casual staff and there was a situation
where we would have to re-employ them for some HR reason I can't remember
now, we already had a re-employ which would move that person's details to a
new payroll number entered by someone at HR, however it became and issue
that if they needed to find data from their old payroll number, this no
longer existed, so I added a two fields to the personnel record, a Dead
flag and a OldPayrollNo when a user on HR had to re-employ someone that
persons record would be flagged as Dead, then any other information that
needed to be retained by the new payroll number copied across and the
payroll number of their old record entered in the OldPayrollNo field of
their new record, however looking back at it I could of used a link table,
but still had a link back to any of that persons old records

Paul


On 10 April 2014 23:06, Arthur Fuller <fuller.artful at gmail.com> wrote:

> Sorry to add an additional layer to this problem, but I need to. Most of
> the foregoing replies cover the required Inserts and Updates, but neglect
> the problem that all Inserts, Updates and Deletes by definition destroy the
> previous data-history.
>
> In the trivial example, Employee X may have been promoted to Executive
> Level Y, and if all you do is change her LevelID from 1 to 2 then you
> automatically lose her history.
>
> This is why I and several others have created and evolved the PITA
> solution. You can read about it in detail by visiting Red-Gate.com and
> searching for PTA (they were afraid of the other interpretation of the
> abbreviation). To reduce the content to a short email message, the point is
> this: every update or insert or delete destroys the data as it existed
> yesterday. Given just one or two sample domains, a) over the course of your
> life, you have had several family physicians, or perhaps several divorce
> lawyers, or realtors, and so on.
>
>
> On Thu, Apr 10, 2014 at 8:07 AM, Susan Harkins <ssharkins at gmail.com>
> wrote:
>
> > Jon, although a bit tedious, could you share the basic table and key
> > structures you're currently working with? You don't need to include all
> > your fields, just the fields included in the relationship.
> >
> > Susan H.
> >
> >
> > On Thu, Apr 10, 2014 at 1:54 AM, Bill Benson <bensonforums at gmail.com>
> > wrote:
> >
> > > 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
> >
>
>
>
> --
> Arthur
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



-- 
Paul Hartland
paul.hartland at googlemail.com


More information about the AccessD mailing list