Bill Benson
bensonforums at gmail.com
Sat Apr 12 17:21:45 CDT 2014
In this case, tblPhoneCalls table needs a FKJobID to the Jobs table because you might be talking to ContactID about his company's involvement on Job X for the benefit of Customer Q (the municipality). Make sense? -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jon Albright Sent: Saturday, April 12, 2014 6:12 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Many to Many relationship issue That's a pretty good analogy of it Bill :) What I was doing started out initially as just a simple "keep track of who was calling" thing but quickly grew. This is what I was trying to get working: tblCompany PK CompanyID tbl Jobs PK JobID Cross table tblCompany_Jobs FK CompanyID FK JobID tblCompanyContact PK ContactID FK CompanyID tblPhoneCalls PK CallID Cross table tblContact_Calls FK ContactID FK CallID I was able to use both sets separately with no problems. I then began trying to get them to work together where but could only do it on a limited basis. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson Sent: Saturday, April 12, 2014 11:42 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Many to Many relationship issue Jon, Trying to understand what you mean. I was initially picturing your business looking to work on Job X at company Y. Now I am starting to read this as Job X is run by municipality Q, and companies A, B, and C might bid on Job X... and you would be keeping a database of companies who might want your bid, and you might talk to Company A and B both about Job X (which is Customer Q's Job). Is that right? If so, then You would not have a FK to the Company Table in the Job table, as that Job might actually get more than one Company working on it. You would need a cross table Company_Jobs, and have entered both Jobs and Companies separately into their respective entity containers. And the municipality, who is the end customer, might be a superset of the Job (a Customer table)... or a characteristic field within the Job table and therefore part of the Job's description. You might also want an industry table so that the Customer can be grouped by that (e.g., Government). -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jon Albright Sent: Saturday, April 12, 2014 4:58 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Many to Many relationship issue Hi Bill, What is being expected is to enter information relating to a job out for bid, enter companies interested in receiving bids from us and who made the request. Enter phone calls: incoming and outgoing, what job the phone calls were about. If a new job then be able to create a new job "on the fly" and who was involved. Provide lookup capability to find a job by job name (can be partial name), by the type of job, search and display phone calls which relate to this job and/or phone calls from a particular company or person. Most of the people in our company have some level of computer skills but the search parameters would need to be in a combo box or some form of list. When entering information as a new entry, if the company or caller does not exist then the ability to add "on the fly" is presented. There are really no formal reports being required, at least not yet but you never know... I get the requests for the above types of information from my boss and a couple managers, sometimes quite often. The need for historical information is keen... the projects we bid to the federal government sometimes take up to 18 months before being awarded and that obviously involves having to go searching later on but with an accurate database, when the basic information is displayed via a search, it makes the job much easier. By being able to add new companies and/or contacts "on the fly" will also reduce time for filling in the blanks for subsequent data entry. One of my main goals, is to make it user friendly so it will involve minimum involvement from me. -- 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