[AccessD] Many to Many relationship issue

Hi Bill,

Am I understanding correctly that since the calls table will now contains
the fields for the person making the call i.e. firstname etc, and that by
also having a fk to the jobs table that it becomes a many to one with the
jobs table?  Does this mean that there are no key fields liking the contact
person with the company they work for? I haven't been able to try this yet
as other tasks during the day prevent me from addressing this so, I work on
this from home.  It's been quite a while since setting up any kind of
database apps so please forgive me for my lack of knowledge.... I do believe
it will all strike home eventually....

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
Sent: Friday, April 11, 2014 1:16 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Many to Many relationship issue

In Calls table there would be fkjobid. Calls might have just one
companycontact per call or a multi-party call.

If the former a call table suffices,  otherwise need a supplementary
callcontact  table is needed.

A new record in Calls in my view would need these elements.

Choose Company from list of active jobs:

Select distinct J.FkCompanyId, C.Company from Jobs as J left join Company as
C on J.fkcompanyid = C.companyid where J.fkcompanyid is not null.

Suppose user chooses Companyid (fkcompanyid) = N.

Now user selects job from:
Select C.companyid, C.Company, J.Jobid, J.JobDesc FROM Job as J Left join
Company as C on J.fkcompanyid=C.companyid where J.fkcompanyid =N

Suppose user selects J.JobID = j

Alternatively user could start a call record by choosing a jobid from the
above query without the where clause.... this would give the companyid as a
hidden column in the list to display.

Either way we have Jobid = j .

To get a list of possible contacts first check (from existing records in the
Calls table):

Select contactname, contactphone, comtacttitle, contactemail from Calls
where fkjobid = j.

If there are multiple contacts on a call that would be

Select cc.contactname, cc.contactphone, cc.contacttitle, contactemail from
CallContact as cc left join Calls on cc.fkcallid = calls.callid WHERE
calls.fkjobid = j.

The goal it to populate an unbound list in a form that also includes an
AddNew control. User can select a Contact already used or add a new Contact.

One contact per call:

After Insert Into creates a record in Calls, Update Calls lets you update
with any new or edited values for the contact...

Multiple contacts: Select as many candidates one by one or an addnew
subform. Insert records in Callcontact.

It won't hurt to build a callcontact table to hold contact details even if
there is just one contact. Then contact details would not reside in Calls
table.
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com