Jon Albright
jon.albright at hawaii.rr.com
Fri Apr 11 01:31:04 CDT 2014
It makes sense to me yes, just not clear how it would be implemented. As I understand what you're stating: The table currently being used to store a company's contact information goes away and the structure of the phone call table gets modified to include a contact person's information or vice versa. This in turn effectively does away with the need to have the second cross table. So.... I now have a table for a company, a table for jobs and a cross table between them to enable the M2M relationship. Will the proper method to record or display information relating to a job, the company(s) bidding the job and the phone calls with the people making the phone calls be to establish a key for a 1 to M between the phone calls table and the company table? I'm stuck on the company contact idea because I am asked often about the name of a person from a company that had called regarding a job we are bidding. I did attach a PDF screen shot of the current tables and relationships to a previous message but not sure if it ever made it. In short, my whole purpose is to be able to pull up a job, display the companies bidding the project(s), the phone calls relating to the jobs, who made the call and from what company. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson Sent: Thursday, April 10, 2014 5:33 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Many to Many relationship issue I think you may be more satisfied storing the contact's details in the calls table rather than looking them up later. Then you are not in doubt as to history. So if the contact changes (eventually they do), New records in the call table will reflect correct, actual info - not run risk of being improperly looked up because someone did not update a contact table. In other words i would consider NOT USING a foreign key to the contact table, just the name and other details. I might do a select unique rows query from past transactions for the company in question, while documenting a Call, to get high probability candidates for who might have been on the current call (most recently used list)... but I wouldn't rely on a contact Id for this purpose. If you later tell the company you spoke to person X you better have your facts right, and not rely on an update process that might lag. Does this make sense to you? It's different if it were a personnel table in your own company, where you cannot even conduct, let alone document, a transaction prior to having accurate index values. In a call center context you might be dealing with "whoever was there at the time, acting as contact" not who you might expect to be there on a regular basis. I call that characteristic data, it might not be the right term. On Apr 10, 2014 10:37 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 > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com