Relationships

  • Thread starter Thread starter Sarah
  • Start date Start date
S

Sarah

Hi, I was hoping someone could explain to me in "layman" terms about
relationships. I have done 3 course using access and have even had a
consultant come out and spend the day with me. my problem is i cannot figure
out how to make my tables work. I have 25 tables that have nothing in common
with each other. for example, one is for steel parts, another one for
valve's, consumables etc they have nothing in common but at the end they give
me a price for a job installation. How do i make the relationships work with
each one? Should i be putting like an ID number into all of them and making
that the primary key? Please help me as i cannot figure out how to get them
to "connect together". Thanks Heaps Sarah
 
Sarah,

Well, let's start from the beginning... You say you have 25 tables but you
have not told us what you are trying to do. I am also confused why you have
a table of Steel Parts and then one for Valves' and another for Consumables
when it sounds like they could/should be in one table with a field
indentifying what type of part they are. Tell us what and why and we can
help you better. To help you better, look here...

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


http://www.databasedev.co.uk/table-of-contents.html

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
Hi Gina,

What i am trying to do is write a database for quotes. I work for an LPG
company and we do installations of Cylinders and Bulk Tanks. Some of my
tables are for the cylinder side and the others for the Bulk Tanks. My
manager would like all the different parts for the installation's to be on
different forms hence i have all these tables. But not sure how to link them
all together using a relationship?. I have figured out how to get a report
at the end (which works) and gives me a total of all the different things so
we know how much the job is going to cost.
 
Sarah,

Tables don't get designed around reports. Think of it like building a
house... you can't design a house around the rooms and the windows, you
first have to think about the foundation. Your tables are your foundation
and because they are set up incorrectly, creating relationships, designing
forms is MUCH more difficult. The first order of business would be to clean
up your tables, create your relationships and THEN build your reports.

I'm not sure how the Relationships can be set up with your tables in the
state they are in now. I would strongly suggest you get them correct first.
You could start off with something like...

tblQuote
qQuoteID (PK)
qDate
qCompanyID (Linked to tblCompanies)
etc...

tblQuoteDetail
qdQuoteID (FK)
qdQuantity
qdPartID (FK)
qdDescription
qdUnitPrice
etc...

tblParts
pPartID (PK)
pDescription
pPrice
pEquipmentTypeID (FK)
etc...

tblEquipmentTypes
etEquipmentTypeID (PK)
etDescription (Cylinder, Bulk Tank, etc...)

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
Hi Sarah,

It appears that you have created different tables for different categories
of equipment and services that your company provides. If this is the case
then, as Gina suggested, you should put all those items into one table with
an extra field:- "Categories". You'll then be able to filter/sort to that
field.

Merge the fields of your other tables. Eg: You might have had a table of
labour rates, another for unit pricing of valves. You can either add both
fields to the new table and only put a price in the appropriate field, or,

have a "Price" field and a "PriceType" field so that the price is always in
the same field and tblPriceType stores a list of types: Unit, Rate, Labour,
etc.

Hope this helps.
 
Sarah said:
Hi, I was hoping someone could explain to me in "layman" terms about
relationships. I have done 3 course using access and have even had a
consultant come out and spend the day with me. my problem is i cannot figure
out how to make my tables work. I have 25 tables that have nothing in common
with each other. for example, one is for steel parts, another one for
valve's, consumables etc they have nothing in common but at the end they give
me a price for a job installation. How do i make the relationships work with
each one? Should i be putting like an ID number into all of them and making
that the primary key? Please help me as i cannot figure out how to get them
to "connect together". Thanks Heaps Sarah
Click to expand...

Getting your tables and relationships right is absolutely the most
important step. I think you need to grasp two things: how to spot when
you should be creating a relationship, and what the mechanism is for
doing this.

Relationships between what, though? You have to identify the "entities"
(to use less technical language: "thingummies"). An entity is something
which exists in its own right, like a valve, an assembly, a customer, a
computer. It'll have properties (represented by fields which can
contain values) and it will have instances: you may have fifty customers
in your Customers table - that's fifty instances of a Customer. It can
take practice to be able to say with confidence that some value (a
measurement, cost, colour, whatver) is a property of this entity rather
than that entity.

Then you look for relationships in real life. Think first of the most
common relationship: "one-to-many". One customer may place many orders.
One Country may have many counties. One army may have many divisions.
To represent a one-to-many relationship the table in which you store
records for the "many" side should have a field (a "foreign key") which
stores a reference (a unique ID, - the "key") of the relevant record in
the "One" side. Example: the Orders table in a sales database will
contain a field in which the Customer-ID is stored, showing that a
particular order was placed by customer number xyz. If you want details
of that customer, you now know where in the Customers table to find them.

The other common relationship is "many-to-many". Your application may
well have a few of these. If you have an Assembly, that will consist of
many Parts, but it may not be one-to-many, as a given Part may be used
in many different Assemblies. You use a separate table (an
"associative" or "junction" table) to represent this. Both the Assembly
table and the Part table have a field which is the unique ID ("Primary
Key") of the Assembly or Part. Your "associative" table might have
three fields, one for the Assembly-ID, one for the Part-ID, and one
(say) to record the quantity of Parts for the Assembly instance
concerned, so that a particular Pump may need 32 of a particular type of
screw. So for a given Assembly, if you want to know all the parts which
go into it, look in the "associative" table (which might be called
Assembly-Part-tbl) for all the records matching a particular
Assembly-ID, and read off the Part-IDs in those records. You'll use
queries to return matching results from all the tables by "joining" them
- that's the process of limiting results to those where keys match
across tables.

Separating your tables correctly makes all this surprisingly easy once
the penny drops, but if you get it wrong you'll be in trouble.

I hope that helps. (Let us know!)

You may find these favourite references useful too:

Free samples from Lynda.com on table design:
http://www.lynda.com/home/Player.aspx?lpk4=31001 (A2007)
http://www.lynda.com/home/Player.aspx?lpk4=13150 (A2003)

Microsoft tutorial on table design:
http://tinyurl.com/ms-table-design-tutorial

Phil, London
 
HI Gina,

Thanks for all your help, i sort of knew it was wrong but really didnt want
to believe it. Really appreciate all your help.

Thanks
 
(My posts don't seem to be getting through from my newsreader, so this is
from the website - third time lucky?)

Getting your tables and relationships right is absolutely the most important
step. I think you need to grasp two things: how to spot when you should be
creating a relationship, and what the mechanism is for doing this.

Relationships between what, though? You have to identify the "entities" (to
use less technical language: "thingummies"). An entity is something which
exists in its own right, like a valve, an assembly, a customer, a computer.
It'll have properties (represented by fields which can contain values) and it
will have instances: you may have fifty customers in your Customers table -
that's fifty instances of a Customer. It can take practice to be able to say
with confidence that some value (a measurement, cost, colour, whatver) is a
property of this entity rather than that entity.

Then you look for relationships in real life. Think first of the most
common relationship: "one-to-many". One customer may place many orders. One
Country may have many counties. One army may have many divisions. To
represent a one-to-many relationship the table in which you store records for
the "many" side should have a field (a "foreign key") which stores a
reference (a unique ID, - the "key") of the relevant record in the "One"
side. Example: the Orders table in a sales database will contain a field in
which the Customer-ID is stored, showing that a particular order was placed
by customer number xyz. If you want details of that customer, you now know
where in the Customers table to find them.

The other common relationship is "many-to-many". Your application may well
have a few of these. If you have an Assembly, that will consist of many
Parts, but it may not be one-to-many, as a given Part may be used in many
different Assemblies. You use a separate table (an "associative" or
"junction" table) to represent this. Both the Assembly table and the Part
table have a field which is the unique ID ("Primary Key") of the Assembly or
Part. Your "associative" table might have three fields, one for the
Assembly-ID, one for the Part-ID, and one (say) to record the quantity of
Parts for the Assembly instance concerned, so that a particular Pump may need
32 of a particular type of screw. So for a given Assembly, if you want to
know all the parts which go into it, look in the "associative" table (which
might be called Assembly-Part-tbl) for all the records matching a particular
Assembly-ID, and read off the Part-IDs in those records. You'll use queries
to return matching results from all the tables by "joining" them - that's the
process of limiting results to those where keys match across tables.

Separating your tables correctly makes all this surprisingly easy once the
penny drops, but if you get it wrong you'll be in trouble.

I hope that helps. (Let us know!)

You may find these favourite references useful too:

Free samples from Lynda.com on table design:
http://www.lynda.com/home/Player.aspx?lpk4=31001 (A2007)
http://www.lynda.com/home/Player.aspx?lpk4=13150 (A2003)

Microsoft tutorial on table design:
http://tinyurl.com/ms-table-design-tutorial

Phil, London
 
Back
Top