design question before I get started

[Guest]

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

 

[Guest]

Add CompanyID field to tblBarge.

 

[Guest]

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.

 

[Guest]

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

Thanks again for all the help.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Rick Brandt]

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?

It's not really a question of "permanent". The joins in queries are simply NOT
relationships as that term is defined in the context of developing applications
with Access. They are simply joins, another term altogether.

Part of a properly designed database is setting up the realtionships. That is
done in the relationships window and not in queries.