Project and Contact Database

I have created a database to track Jobs.  Each job can have mutiple contacts. 
 I have a jobs table and form and contacts table and form.  how can i create 
a subform on the jobs form where i can list all of the contacts related to 
the particular job?  Each job can have many contacts and each contact can be 
related to many jobs.
0
Utf
2/9/2010 5:11:02 PM
access 16762 articles. 3 followers. Follow

2 Replies
504 Views

Similar Articles

[PageSpeed] 33

"jessa13177" <jessa13177@discussions.microsoft.com> wrote in message 
news:18283447-AA63-4E8E-AB3F-0104626B329E@microsoft.com...
>I have created a database to track Jobs.  Each job can have mutiple 
>contacts.
> I have a jobs table and form and contacts table and form.  how can i 
> create
> a subform on the jobs form where i can list all of the contacts related to
> the particular job?  Each job can have many contacts and each contact can 
> be
> related to many jobs.


You need to have a table to link Jobs and Contacts.  The fields in this 
table will include the primary key of the Jobs table and the primary key of 
the Contacts table.  So, for example, if Jobs has a primary key of JobID (an 
autonumber field), and Contacts has a primary key of ContactID (an 
autonumber field), then you might have a table named "JobsContacts" with two 
fields, JobID and ContactID, both Long Integer fields.  The presence of a 
record in this table with JobID 1 and ContactID 1 would mean that job 1 is 
related to contact 1, and vice versa.

Your subform would then be based on the JobsContacts table, or on a query 
that joins the JobsContacts table to the Contacts table and selects the 
other fields of interest about the contact from that table.  It is crucial 
that the query include the JobID and ContactID fields from JobsContacts, not 
from the related table.

-- 
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

0
Dirk
2/9/2010 5:28:57 PM
You need to model the many-to-many relationship by a third table with two
columns which reference the primary keys of the Jobs and Contacts tables, e.g.


JobContacts
….JobID
….ContactID

Together these two column constitute the table's primary key.  You can add
other non-key columns to the table if necessary to represent other attributes
of a contact's role in relation to the job in question.

Create a query which joins the JobContacts table to the Contacts table on the
ContactID columns.  In the query return the JobID and ContactID from
JobContacts, and those columns from Contacts representing contact details, e.
g. job title, phone number etc, but not the contact's names.

Create a continuous view form based on this query and for the ContactID use a
combo box set up like this:

ControlSource:    ContactID

RowSource:     SELECT ContactID, FirstName & " " & LastName FROM Contacts
ORDER BY LastName, FirstName;

BoundColumn:   1
ColumnCount:  2
ColumnWidths:  0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert them.  The important thing is that the first dimension
is zero to hide the first column and that the second is at least as wide as
the combo box.

Include controls bound to the columns from Contacts and any other non-key
columns from JobContacts, but not a control bound to the JobID column.

Embed this form in the jobs form as a subform, setting the LinkMasterFields
and LinkChildFields properties of the subform control to JobID.

Ken Sheridan
Stafford, England

jessa13177 wrote:
>I have created a database to track Jobs.  Each job can have mutiple contacts. 
> I have a jobs table and form and contacts table and form.  how can i create 
>a subform on the jobs form where i can list all of the contacts related to 
>the particular job?  Each job can have many contacts and each contact can be 
>related to many jobs.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201002/1

0
KenSheridan
2/9/2010 5:41:50 PM
Reply:

Similar Artilces: