Relationship problem

[idtjes3]

Hello

I'm working on my first data base and I've run into a bit of a snag. I have
a few tables but i'm only really concentrating on 2 for this example. My one
table is called Job information which lists: a contract number(primary key),
a description of the job, and all the finacials for that particular contract.
The other table i'm working with is the contact information table which list
: the contractors ID ( primary key), the contractors name, address, phone,
etc. I've already set up a field in the job information table that allows me
to use a combo box pick the appropriate contractor for the job by
automatically importing the contractor name from the contact table to the
combo box on the job information table..

What i want to be able to do is link the contractor data from the contact
information table to its appropriate data job information table. This way
when I click on the contractor, in the job information table, it will show me
all of their contact information. Also i would like to, while working on the
contacts page, click on a contact and have it list every job we have done for
them. This will come in handy if my boss know who the contractor was but cant
remember the job name offhand or vise versa.

This is the best way ( so far) i can think of to get this information to
him. If there is a better, more efficient way, I'm all ears. I'm kinda
learning access as I go along and it can be frustrating sometimes to find the
best way to do things. Thanks!

 

[Beetle]

First, it sounds like you are working directly in the tables, which you
shouldn't be. Tables are for data storage only. You should be using
forms/queries for data entry/manipulation.

Second, what is the nature of your data. Is it;

1)One contractor can have many jobs (One-to-Many with contractor on the
*one* side)

2)One job can have many contractors (One-to-Many with jobs on the *one* side)

3)A job can have many contractors and a contractor can have many jobs
(Many-to-Many, in which case you need a third junction table)

This needs to be determined in order to get the right table structure.

 

[idtjes3]

Hello,

It would be mostly number 1, one contractor can be associated with many
jobs. Also, is there anywhere that offers a tutorial on entering data to the
tables using forms? Up to this point I have been entering all my data into
the tables like you thought.

 

[Ron2006]

Relative to the easy part (the last part of your question) here are
some places you may want to look through.

See the tutorials here for more resources:


Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html


The Access Web resources page:
http://www.mvps.org/access/resources/index.html


A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html


MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials


Ron

 

[idtjes3]

Thank you both for your responses so far. I will take a look at those
tutorials. Ive done some tutorials before and the ones i've done give you a
lot of info on certain topics, but they really don't go into the whole order
of doing things. For instance, would I be correct if i said you first create
a table, create forms to fill out your tables, then use reports to print/
show the data in your tables?

 

[Ron2006]

Yes, that is the normal sequence.

Forms are stylized windows that are used to view/edit/add information
that is in tables. So you have to have the tables first.

Now, the trick is to build the tables properly, and that requires
knowing you information, and how things are related and how to
"Normalize" the information so that it is not kept redundantly.

The forms then help you display the information and enter it in
logical patterns.

Reports to show the data in a sequence and format that is eyepleasing
and correct AND supplies the information needed by the user in hard
copy. If it is not needed in hard copy then there may not be any need
to have "reports" but rather just forms that display the equivalent
information selected and formated in a visually pleasing manner.

Usually the tutorials/examples that are indicated in those links are
designed to give you ideas on how to approach the problems and also
ideas on how the tables look and how they are related relative to one
to many and many to many, etc.

Ron

 

[idtjes3]

Thanks for everyones help. I was able to fix my problem.