design question before I get started

[Scuda]

Hi all, as I continue on my awesome journey of Access I am making efforts to
improve my design.

My question is this. I am adding a new element to my database which is Tugs
and Barges. What my desired outcome is to have a query where somone enters a
BARGE NUMBER and it will return the TUG COMPANY, Phone Number, webiste and
Comments.

Now, I have down this before and it's pretty easy but in this case there may
be ONE Tug Company that has numerous barges. So instead of having to have a
Phone Number, Website and Comments set up for each BARGE NUMBER in one table,
I know there must be a better way. Something like 2 tables:

tblBarge
BargeID
BargeName

tblTugCompany
CompanyID
CompanyName
PhoneNumber
Website
Comments

Do I then just relate the tables? As you can see I am not sure exactly
where do go from here. Any advice greatly appreciated and sorry for the long
post.

Steph

 

[JPC]

Add CompanyID field to tblBarge.

 

[Scuda]

Thanks JPC! I made that change.

I have all the Barges entered into tblBarges and I am beginning to enter in
the Company names into tblTugCompany

How do I relate the Company to the barge?

Thanks again.

 

[Scuda]

Nevermind JPC, I got it! I related the 2 CompanyID's.

Thanks again for all the help.

 

[JPC]

In the query where someone enters a "BARGE NUMBER" (your words, presumably
BargeID), create a relationship between the CompanyID fields of your two
tables. That will enable you to display the CompanyName.

 

[Scuda]

Thanks again JPC.

Quick question: I created the relationship in Relationships. What is the
advantage of doing it there as opposed to just doing it in the query?

SAB

 

[Rick Brandt]

Thanks again JPC.

Quick question: I created the relationship in Relationships. What is
the advantage of doing it there as opposed to just doing it in the
query?

What is in a query is just a join line. It is not a relationship because it has
no enforcement capabilities. Basically the only *real* relationships are the
ones with referential integrity enforced. Otherwise they only serve as
documentation.

 

[Scuda]

Thanks Rick, so if I want them permanent, so to speak I should do them in
Relationships, or Temp, I can do it in the query itself?

Thanks again.