Tables linking problems.

  • Thread starter Thread starter Nevyn
  • Start date Start date
N

Nevyn

Hi Folks, I have a wee problem can you help please? I run
a charitable organisation. I have one database with one
major table containing all data. I have three smaller
tables that take information from the main table. At
present I have to type in all the info seperately. I would
like to be able to type the data once in the main table
and for it to be entered in the other tables. I have tried
linking tables but I do not seem to be doing it right. I
would appreciate some help.
Ta
Mike
 
Hi Folks, I have a wee problem can you help please? I run
a charitable organisation. I have one database with one
major table containing all data. I have three smaller
tables that take information from the main table. At
present I have to type in all the info seperately. I would
like to be able to type the data once in the main table
and for it to be entered in the other tables. I have tried
linking tables but I do not seem to be doing it right. I
would appreciate some help.
Ta
Mike
Click to expand...

You're heart's in the right place but your database design may not be,
yet.

Where I think you're going wrong is trying to store the same data in
two tables. THIS SHOULD ALMOST NEVER BE NECESSARY. Databases work on
the "Grandmother's Pantry Principle": "a place - ONE place! - for
everything, everything in its place".

Your first step should be to identify the "Entities" - real-life
people, things, or events - which are relvant to your application.
Each kind of Entity should have its own separate table; that is,
information about a person such as their name should be stored in a
People table - AND NOPLACE ELSE. Information about a Donation should
be stored in the Donations table - AND NOPLACE ELSE; you would
probably have a PersonID field in the Donation table linked to the
PersonID in the People table to record who made the donation, but the
donor's name, address etc. would *not* be stored in the Donation
table.

Read up about "Normalization", frex at
http://support.microsoft.com/default.aspx?scid=kb;en-us;324613 or
http://members.iinet.net.au/~allenbrowne/casu-06.html
 
Hi John,
Thank you for your info. I have one main table that
contains all information about volunteers. I have another
table containing the details of thier training and another
with all details of the work that they have done. There
are three common fields to each table. Id number, Last
Name, and First Name. Where am I going wrong?
 
Hi John,
Thank you for your info. I have one main table that
contains all information about volunteers. I have another
table containing the details of thier training and another
with all details of the work that they have done. There
are three common fields to each table. Id number, Last
Name, and First Name. Where am I going wrong?
Click to expand...

What is the Last Name of a training episode? It doesn't have one, of
course!

Since I have no idea what information you need to record about
"training" or "work", I can only speculate here; but I'd suggest the
following five tables. An asterisk * indicates that the field is (part
of) the Primary Key of the table:

Volunteers
*VolunteerID
LastName
FirstName
<contact info, address, phone, etc.>

with one record for each volunteer; no traiing or work information in
this table

TrainingCourses
*CourseID
Description

Each type of training (I don't know if this is really Courses, but
surely you train volunteers on different topics) would have one record
in this table; no information in this table about Volunteers

Tasks
*TaskID
Description

For instance, "Organizing Fundraiser", "Host", "Cleanup", "Database
Design", all the different kinds of things that the volunteers might
do.

VolunteerTraining
*VolunteerID <link to Volunteers, who's being trained>
*CourseID <link to Training Courses, what they were trained on>
*TrainingDate Date/Time <when>
Comments

If a volunteer is trained on six types of service, there would be six
records for that employee in VolunteerTraining.

VolunteerTasks
*VolunteerID <link to Volunteers>
*TaskID <link to Tasks>
*TaskDate
Comments

You could use a Form based on the Volunteers table, with two Subforms
based on VolunteerTraining and VolunteerTasks, to record a history of
the training and service of each volunteer, while seeing all the
information at once; you will note that it is NOT necessary (or even a
good idea) to store the volunteer's name in the Training or
VolunteerTraining tables! You'ld either use a Form/Subform, or for a
Report a Query joining the two tables to bring that information
together.
 
Hi John, Anyway I can email you direct? You seem to have
all the knowledge oh Great One lol.

Anyway. I have one table "Members" with general
information on. I have another table marked new Members
protocol with all training records. I have a common field
which is linked through the relationships menu as "ID
number". This works fine. I have another table which
details all the time that this ID number has put in to the
organisation. My problem lies now in linking this table to
the Members. Again the only common field is "ID" How do I
link up the three tables? When I try to use the
relationship menu it does not work. Thanks for all your
help by the way it is much appreciated. My plan is to
enter a new members details in one table (members) and for
it to be entered in all the other tables. Is this a dream.
Mike
 
Hi John, Anyway I can email you direct? You seem to have
all the knowledge oh Great One lol.
Click to expand...

jvinson <at> wysardofinfo <dot> com. Note that I'll reply with my
consulting terms; private EMail support is available for paying
customers, it's beyond what I'm comfortable doing on a volunteer
basis.
Anyway. I have one table "Members" with general
information on. I have another table marked new Members
protocol with all training records. I have a common field
which is linked through the relationships menu as "ID
number". This works fine. I have another table which
details all the time that this ID number has put in to the
organisation. My problem lies now in linking this table to
the Members. Again the only common field is "ID" How do I
link up the three tables? When I try to use the
relationship menu it does not work. Thanks for all your
help by the way it is much appreciated. My plan is to
enter a new members details in one table (members) and for
it to be entered in all the other tables. Is this a dream.
Mike
Click to expand...

Each table should have ITS OWN "primary key" field - the MemberID,
trainingID, whatever. Each linked table should have a "foreign key"
field to provide the link. I don't know (because you haven't said)
what are the Primary Key and Foreign Key fields in the "New Members
Protocol" table, nor in the time table. If you'ld care to post that
information here I (or the other volunteers) can probably help; if you
would prefer to send me the database, just be aware that I'll send
back a consulting bill (at my reduced non-profit organization rate)
with the reply.
 
Back
Top