I just caught (and tossed) your earlier thread where John Vinson had
advised you. I didn't follow it because I know how accurate and
thorough John is ..
Do you remember Set Theory from math? An RDBMS is designed to allow
treating your data as "sets". There are about a dozen rules that were
put forward by E.F. Codd and C.J. Date of IBM (the guys who invented
SQL and gave the world DB2) in the 1970s You'll find something along
the lines of those rules at
www.mvps.org/access entitled "The Ten
Commandments of Relational Databases" (I'm sure the Ten Commandments
part is right). I recommend that you visit the site and look it up.
While you're there, poke around and check out all the goodies!
Everything there was contributed by lots of Access developers for the
benefit of all Access developers. It's an incredibly valuable
resource.
If you haven't yet been introduced to the concepts of "normalization"
then you have some necessary learning to do. Data in a database isn't
really considered usable until it's in at least 3rd Normal Form. It
is essential to understand and employ those concepts. If your tables
are properly designed then you are well on your way toward creating an
application that can be efficiently maintained and extended for years
to come. Disregard the normalization rules and you'll create a mess
that is difficult and expensive to maintain and extend and you'll come
to hate it!
Be patient, all of this is the long set up steps leading to the
answers to your questions about Relationships.
Most people coming to Access got here grudgingly after they found that
Excel isn't really a good database management tool. There are
occasions when I have a very simple list to manage and it's still
easier to quickly whip up something in Excel and then toss it when
done. And of course, we all use Excel for what it is: a powerful and
excellent spreadsheet application. You can also do some interesting
and powerful applications using Automation to play client/server games
with Access, Excel and Word. There are other things that have VBA
that are fair game. But I digress ..
As you consider a real world problem and possible or actual solution
you'll be able to recognize and abstract several "entities" in your
problem/solution space. As you dig deeper into the analysis phase,
more entities will surface. Eventually you may wonder where all that
"stuff" came from. Out of your head, of course. I recommend that you
always create a Problem Statement, a Product Specification and a
Functional Specification before you begin the actual design. I
usually use Word in Outline mode to do those things.
All of each kind of entity in your application space will be recorded
in a single table. Novices often do things like create a table for
each year and post into the newsgroups asking how to purge the old
data and "will it be OK to just change the table names for the new
year"? At that point you know that they don't understand the concepts
I'm working to communicate to you. By the way, this is just a tiny
sketch of some of the things you'll need to know. You'll learn it but
be aware that Access has a long, steep learning curve. Once over the
first few hurdles it gets a little easier. Part of the reason is that
RDBMS concepts are foreign concepts to most of us at first contact.
Another part is that most of Access is a whole bunch of design tools
and user interface goodies layered atop a RDBMS which is natively JET
but can be any of several versions of SQL Server or other RDBMS. It's
(almost) all good stuff!
OK, lets get to the Relationships. Consider a very simple HR
application. In that application you need to track Employees and lots
of things about each employee such as name, address, contact info,
emergency contact info, job title and the date that title was
bestowed, date and rate of salary/wage changes, education and job
skills, maybe even infractions and disciplinary events. That's
already starting to sound like a lot but a modern HR application might
have pages of things they might track about an employee.
In this simple concept, tblEmployee sure looks important! That's the
table in which the core record about each employee will be kept and
maintained. That was the easy part. Beyond that you have to thing
things through as to whether the required data will be in just a field
or two in the main table or whether these things might better be in
separate, RELATED, tables. Anything that might occur more than once
and for which a history is to be kept MUST be in a related (child)
table.
For our simple example we'll focus on just current salary and the date
it began and current title and the date it was bestowed. These are
both things that can and should change over time. Horatio Alger
stories! Young person hires on to the company and gets a low salary
and received the title Mail Clerk. Over the next 40 years there will
be larger salaries and different titles until retirement time.
Those elements will be kept in tblSalary and tblTitle which will be
related to tblEmployee. The magic mechanism you see in the
Relationships Window is used to tell the tables how to behave. Be
warned that it's counter intuitive at the outset. The mechanism is
that each record in the child table has its own Primary Key and,
additionally, a Long Integer Foreign Key which just happens to be the
primary key of this employee's record in tblEmployee. The Foreign Key
field in the related table usually has the exact same name as the
Primary Key field in the parent record. That simple relationship is a
one-to-many relationship between tblEmployee and each of the related
tables. In this example, you'd draw a relationship between
tblEmployee.EmployeeID and tblSalary.EmployeeID. Same for tblTitle.
You then doubleclick on the line between the two tables and set up the
relationship as described above. Enforce Referential Integrity and
enable Cascading Deletes.
With Referential Integrity enforced, you will not be able to create a
record in the child table unless there is a record selected in the
parent table. That means you won't create orphan records. With
Cascading Deletes enabled all child records will be deleted (after
notification) when you attempt to delete the parent record. That way,
no orphan records are left behind. That's magic. You don't have to
do anything else to have the Foreign Key written into each child
record.
Out at the data forms the most common mechanism for handling the
one-to-many relationship is with the form/subform paradigm. The older
versions of Access had very good Help on that and they provide a very
powerful means for creating the subform within your main form. But,
your questions were about Relationships so I'll stop here.
Welcome to the world of Access. Post again as questions arise.
HTH
--
-Larry-
--
John Calder said:
Hi
I run Win2K with Access 2K
I am trying to understand Relationships and hope that someone can help me.
Senario
I have 3 tables
Table 1 called "Personal Details" which includes the columns:
[PRIMARY KEY]
EmoployeeID FirstName Surname Phone
932 Grant Smith 0242965702
934 Fred Jones 0242756494
935 Mary Brown 0246567645
Table 2 called "Salary Details" which includes the columns:
[PRIMARY KEY]
EmployeeID SalaryScale
932 A
934 B
935 C
Table 3 called "SalaryScale" which includes the columns:
[PRIMARY KEY]
SalaryScale Salary
A 32000
B 29000
C 22500
The relationship is as follows:
Table 1 [EmployeeID related to Table 2 EmployeeID]
Table 2 [SalaryScale related to Table 3 SalaryScale]
Questions
Why does Table 1 (EmployeeID) and Table 2 (EmployeeID) have to have the same
in them.
I assume this is what creates the "relationship" However, I don't understand
why 2 different tables have to have the same column if Access is supposed to
reduce the number of data entries by creating tables.
This is also the case forthe relationship in Table 2 (SalaryScale) and Table
3 (SalaryScale)
Does this mean that every table created must have at least one other table
with the same column heading in it?
Any help is much appreciated
John
