Linking Tables

  • Thread starter Thread starter Carlo
  • Start date Start date
C

Carlo

HI

I'm new to Access, and am trying to build a database which can
simultaneously record the details of our children's centre users, and log how
often everyone uses. As an example, there are Adult users who come in for
training, who have children who are then deposited at the creche. We need to
add the adult to a table whcih records all of the adult's details (DOB,
Address etc), and have a fields which record who their children are. I would
ahve thought that I could then have the childrens details in a separate table
(as I need to store different information eg dietary reqs, allergies), but
would want to be able to click on the 'child1' field in the adult table to
open a form and add the children. Does that make any sense? And if so, does
anyone know who I might go about it?

Once that is done, I assume that I just set ip another table holding details
of the 'events' or 'trainign days' we are holding, whcih could ahve a list of
attendees from the adult users, and a list of creche attendees from the child
users...

Any help whatsoever would be very very welcome...

Thanks

Carlo
 
HI

I'm new to Access, and am trying to build a database which can
simultaneously record the details of our children's centre users, and log how
often everyone uses. As an example, there are Adult users who come in for
training, who have children who are then deposited at the creche. We need to
add the adult to a table whcih records all of the adult's details (DOB,
Address etc), and have a fields which record who their children are. I would
ahve thought that I could then have the childrens details in a separate table
(as I need to store different information eg dietary reqs, allergies), but
would want to be able to click on the 'child1' field in the adult table to
open a form and add the children. Does that make any sense? And if so, does
anyone know who I might go about it?

Once that is done, I assume that I just set ip another table holding details
of the 'events' or 'trainign days' we are holding, whcih could ahve a list of
attendees from the adult users, and a list of creche attendees from the child
users...

Any help whatsoever would be very very welcome...

Thanks

Carlo

Hi Carlo,

You're right in thinking that you'll need related tables. At the very
simplest, you'll want tables for Adults, Children, Events and
CrecheVisits.

These tables have one-to-many relationships. One Adult can have many
Children. To connect the tables in Access, you need to store the
primary key (AutoNumber works well) of the Adult table (let's call it
AdultKey) in the Child table. Then in the Relationships window you
define a relationship between the two, and you enforce referential
integrity.

You're on the wrong track to think of a Child "field" in the Adult
table. You won't need this.

To edit information about the "many" records (like Child or Events)
you'll use a Subform on the Adult form to list the Children related to
that Adult.

This is the briefest of introductions to normalized database design.
There are whole books written on the subject. If you're interested,
check out Database Design for Mere Mortals by Michael Hernandez. It's
very easy and accessible.

Hope this gets you started,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Carlo:

The relationship type between Adults and Children is really many-to-many as
each adult can have one or more child and each child has two parents, along
with other relatives such as grandparents, aunts or uncles who might be the
carer depositing them at the crèche.

In a relational database a many-to-many relationship is modelled by a third
table with two foreign key columns (fields), each referencing the primary key
of one of the other two tables. So in this case you'd have a table Adults
with primary key AdultID say, and a table Children with primary key ChildID.
In both cases the keys can conveniently be autonumbers. The relationship
between the two is modelled by a third table, Custodianship say, with columns
AdultID and ChildID and probably a Relationship column with values such as
Father, Mother, Grandmother etc. In this table the AdultID and ChildID
columns are not autonumbers, however, but straightforward long integer number
data type. This table has a composite primary key of AdultID and ChildID.

So if Adult 43 (the children's mother) brings in children 123 and 124 the
table would have rows:

AdultID ChildID Relationship
43 123 Mother
43 124 Mother

If the children's father, Adult 99, also brings them two more rows would be
added:

99 123 Father
99 124 Father

You'd also have a Relationships table, which is simply a list of all
possible relationships, Father, Mother etc in a single column, defined as the
primary key. This allows referential integrity to be enforced in its
relationship with Custodianship so that only valid relationship values can be
entered in that table.

For data entry the usual approach would be to have a form, in single form
view, based on the Adults table and within this a subform, in continuous form
view based on the Custodianship table, linked on the AdultID columns. This
table would have two controls, both combo boxes, one bound to ChildID, the
other to Relationship. The ChildID combo box would be set up as follows so
that you can select a child by name, but the underlying value will be the
numeric ChildID:

ControlSource: ChildID

RowSource: SELECT ChildID, FirstName & " " & LastName FROM Children
ORDER BY LastName, FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

The Relationship combo box would be set up as follows:

ControlSource: Relationship

RowSource: SELECT Relationship FROM Relationships ORDER BY Relationship;

Its other properties can be left as the defaults.

If space on the form is a problem then the subform can be placed on a
separate page of a tab control on the form.

To log the use of the centre you can have another table, Attendances say,
for this with the date, type of training (probably referencing yet another
table of training activities) etc. The question now arises as to what you
relate this table to in order to record who is attending. If its merely to
record the adult attending, and you infer from this that they are accompanied
by the children you've already assigned to them (a risky assumption I'd say),
then you simply need a foreign key AdultID referencing the key of Adults. If
it’s the adult and the actual accompanying children being logged then you
again have a many to many relationship, this time between Attendances and
Custodianship, so another table is necessary, AttendancePeople say. As
before this will have foreign keys referencing the primary keys of each
referenced table. In the case of Attendances this can be an autonumber,
AttendanceID, so the foreign key in AttendancePeople would be a long integer
number AtendanceID. In the case of Custodianship, though, remember that this
has a composite primary key for AdultID and ChildID, so the foreign key in
AttendancePeople would also be a composite one of AdultID and ChildID. For
data entry in a form combo boxes can be used, set up as described above.

As you see you've now got a framework of related tables. This is the
'logical model' or 'schema' and is really just a model of that part of the
real world with which you are concerned in terms of its entity types
(modelled by tables) and the relationship types between them (in some cases
modelled by a foreign key, in others, where the relationship type is
many-to-many, by a third table which resolves the is many-to-many
relationship type into two one-to-many relationship types). You'll have
probably found it difficult to get a mental picture of this 'logical model'
from my description, but if you draw it out diagrammatically on paper with
boxes representing each table and directional line representing the
relationships between them (what's called an entity relationship diagram) you
should get a better idea of how it all fits together, particularly if you
supplement this with some background reading as Armen suggested.

You will find that you'll need other tables too, e.g. a Cities table for use
in recording addresses, which in turn might reference a Regions or States
table. The important thing is that each 'entity type' is modelled by its own
table, which is then referenced where necessary. This means that each 'fact'
is recorded only once in the database, eliminating redundancy. The process
of eliminating redundancy by 'decomposing' a table into more than one table
is known as normalization.

An example of its lack of proper normalization can be found in the sample
Northwind database's Customers table. You'll see that this has City, Region
and Country columns so we are told numerous times that São Paulo is in SP
region (as is Resende) and that SP region is in Brazil. Not only does this
require repetitive data entry, but more importantly it opens up the risk of
inconsistent data, e.g. it would be perfectly possible to put São Paulo in
California in one row and California in Ireland! Proper normalization as I
described above would prevent this as the fact that São Paulo is in SP region
would be stored only once in the database as would the fact that SP region is
in Brazil and California is in the USA.

Ken Sheridan
Stafford, England
 
Belated thanks to both of you for your help on this. I was finding it
difficult to understand without the background, so have bought and read
Database Design for mere mortals. Now much much clearer. Thanks again.

Carlo
 
Back
Top