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.