Help needed on storing non-regular dataset in Excess tables

[yunlai yang]

Hi, I have a problem on storing non-regular data in excess tables when try
to create a database for all the measurements of my samples (over 500).

There are many measured properties for each sample. Most of the properties
are REGULAR, only have one value, it is quite easy to create a table to
store them. However there are a couple of properties are UN_REGULAR, they
have over 50 repeated measurements. An example of this property is the
density of a sample measured at different temperatures. I have difficulties
to store these un-regular properties in tables.

I think there could be two approaches. One is store the dataset of a
property with repeated values normally in a table as for other regular
properties. In this case for each pair of measurements (temperature and
density) I need the sample code (as foreign key) to indicate them. That is
for one sample I need store same number of the SAME sample code as the that
of the repeated measurements. This might work, but I hate it. First the
table looks ugly and unlogic. Second it might make related querries
performance poor.

Another approach I can think of is to store the measured dataset of the
un-regular properties into a text or Excel file and then write a piece a VBA
code to operate them. The draqbacks of this approach are the lack of
reliability and the needs of effort of coding.

I am sure there are some experts overther who have a better solution or can
direct me to a right direction.

Your help is very appreciated.

Yunlai
(e-mail address removed)

 

[Jean-Paul Viel]

Hi,



What you have, for the non regular value, is a relation one to many. As you
said the general information should be in one sheet with a unique
identification number for each and the nun regular on an other sheet with
the unique number to do the link between those information.

 

[HSalim]

I'll second Jean_Paul suggestion to store the Un-Regular properties in a
separate table.
In fact, Considering that you have 500 measures, i'd even say that ALL
measures should be in
one Measures table.
This will give you the flexibility to add an umlimited number of measures
and instances of any measure.
On the other hand, if you expect to need some of those measures side-by-side
say for example Length, width, height so that you could calculate volume
easily, then it may make sense to
store them on the header row.

I would squelch, abandon and forever banish any thoughts of using excel for
this.

HS