Table design

[Jean]

Hi everyone,

My task is as follows.

I have an Excel spreadsheet with data that I want to save
in an Access table.

Each row is a person, which will have a field e.g.
PersonNumber. The rest of the data for each person
consists of up to 20 different bodily measurements
(height, foot size, upper-arm length etc.). Now the
problem is that over the years, different measurement
systems were used for these people, and there are 4
different sets of measurements (e.g. those from years 1991
till 1995 will have 20 type A measurements, those from
1995 till 2000 will have 18 type B measurements etc.)
These measurements differ in some ways, so I cannot place
all 4 types under one set of common fields.

I would like to know, if I'm maybe missing something here.
Would it make more sense to keep all the measurements in
one table, i.e. have 4 x measurement sets for field
headings and only enter data in the respective fields for
each person in whatever relevant system he/she was
measured? Or is it better to break it up into multiple
tables, say with one table constisting only of people with
measurement A, and another with measurement B etc.?

Later I would like to perform queries, that will query all
the data available. Also, when more entries are added, it
should be added under field headings for the latest
measurement system.

Thanks in advance for your tips!

 

[David Cleave]

Hi there

In the interests of economy, I would avoid putting all the
fields for all the measurement systems in the one table. I
would do this:
- Create a table for people, which includes fields which
all the people share (e.g. name, person number etc)
- Create a separate 'measurements' table for each of the
measurement systems
- Create a one-to-one join type from the 'people' table to
each of the 'measurements' tables (use person number as
the primary key in the 'people' table and each of
the 'measurements' tables)

Therefore, for a particular person, their record in
the 'people' table can be linked to a record in whichever
of the 'measurements' tables is appropriate.

If you feel the need to, you can design a query containing
the people table and all the measurement type tables so
all the information can be presented in the one place.

Also, you can specify in VB code that whenever a new
record is created in the 'people' table, a linked record
is created in whichever 'measurements' table corresponds
to the current measurements system.

Hope this helps

Cheers

David

 

[Guest]

Thanks David, I'll give that a shot!

-----Original Message-----
Hi there

In the interests of economy, I would avoid putting all the
fields for all the measurement systems in the one table. I
would do this:
- Create a table for people, which includes fields which
all the people share (e.g. name, person number etc)
- Create a separate 'measurements' table for each of the
measurement systems
- Create a one-to-one join type from the 'people' table to
each of the 'measurements' tables (use person number as
the primary key in the 'people' table and each of
the 'measurements' tables)

Therefore, for a particular person, their record in
the 'people' table can be linked to a record in whichever
of the 'measurements' tables is appropriate.

If you feel the need to, you can design a query containing
the people table and all the measurement type tables so
all the information can be presented in the one place.

Also, you can specify in VB code that whenever a new
record is created in the 'people' table, a linked record
is created in whichever 'measurements' table corresponds
to the current measurements system.

Hope this helps

Cheers

David




.