Table design

[Jean]

Hi there,

I have the following challenge ahead, can someone please
offer some advice?:

I have a database with several peoples' measurements. I
created the following table structure:

1 main table, containing the persons' personal details,
age, etc.

1 subtable for each measuring method used on this person,
and the respective measurements (e.g. body height, sitting
height, arm length etc.), in this case then there are 4
subtables. Each person will only appear once in any of the
tables. Therefore I set up a one-to-one relationship
between the main table and each subtable, using the
PersonID as the primary key.

NOW what I want to do, is to compare the persons'
measurements with a data source (at the moment in Excel
but want to import it into Access) This data source
contains about 20 rows, which are ascending intervals in
body height measurements, e.g. 1602mm, 1614mm, 1627mm etc.
There are 3 columns: Each one is a heading indicating
small proportion, middle proportion and big proportion.
Each contains a sitting height measurement. So for
example, for body height of 1621, there will be in the 3
columns for small proportion, middle proportion and big
proportion the following values 830, 865 and 902
respectively.

What table structure should I set up, if I want to link
each person to this data, and indicate e.g. on a form
whether this person is small, middle or big proportioned?

I have already thought of making a relationship between
the body height field of each of the subtables to the body
height field of the new data source, but the problem is
that the person's body height must only be in an interval,
and not exactly equal to the body heights as set in the
new data source.

I will later be setting up similiar data tables (for the
proportional data) which will also contain data for 10
years ago and 10 years in the future, as these values then
differ ( Humans are on average getting bigger by the year,
strange but true! ).

I thank you kindly in advance for your help.

 

[Tim Ferguson]

1 main table, containing the persons' personal details,
age, etc.

1 subtable for each measuring method used on this person,
and the respective measurements (e.g. body height, sitting
height, arm length etc.),

Bad. These are essentially the same things, so a better design would be
something like (== is Primary Key, -- isn't)

PersonID BodyPart Value
======== ======== -----
Eric Arm 52
Eric Bd Hgt 191
Eric Arm 53
Eric Bd Hgt 199
Freya Arm 49
Freya SitHt 103


This is faster and easier to query, and conforms to normal db design
practice.

NOW what I want to do, is to compare the persons'
measurements with a data source (at the moment in Excel
but want to import it into Access) This data source
contains about 20 rows, which are ascending intervals in
body height measurements, e.g. 1602mm, 1614mm, 1627mm etc.

I don't really understand what you are trying to do here, but almost
certainly the anwer is a DLookUp or DMax function. If you are going to look
up the table in Excel, there are the VLookUp and HLookUp and Indirect
worksheet functions you can do too. A lot of choice! The best one depends
on quite what you are trying to look up and how the base data are arranged.

HTH


Tim F