[AccessD] Many to Many relationship issue

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



More information about the AccessD mailing list