[AccessD] Many to Many relationship issue

Bill Benson bensonforums at gmail.com
Fri Apr 11 06:15:38 CDT 2014


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.


More information about the AccessD mailing list