How do I propogate a value to identical fields in multiple tables.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to set up a database to hold some scientific data. Ideally, all
the data would be on one table since only one field is unique to each record.
Unfortunately, I have upwards of 500 fields already and envision more than
2000 fields per record in the future. Since Access limits the number of
fields per table to 255, I've had to split my data into multiple related
tables with identical primary keys.

My question is: How do I go about propogating that unique value to all the
tables from one entry field in an input form?
 
Sounds to me like your database is in serious need of proper normalization.
I can't imagine a legitimate need for 2000 fields in a single table: 20 or
30 is the most I've ever needed.

Here are some links I've gathered over the years. Afraid I can't guarantee
they're all still active:

Normalization:
http://www.webmasterbase.com/article.php/378
http://www.jpmartel.com/bu12_c.htm (aimed at dBase, but still valid)
ACC2002: Database Normalization Basics (Q283878)
http://support.microsoft.com/?id=283878
ACC2000: Database Normalization Basics (Q209534)
http://support.microsoft.com/?id=209534
ACC: Database Normalization Basics (Q100139)
http://support.microsoft.com/?id=100139

http://home.earthlink.net/~billkent/Doc/simple5.htm
http://www.sbpa.csusb.edu/flin/info609/dbdesign/Default.htm
http://www.northern.ac.uk/computing_science/db_design/09_normal/03-1nf.htm
http://burks.bton.ac.uk/burks/foldoc/35/28.htm

http://www.formeremortals.com/Downloads/UnderstandingNormalization.pdf

Database Design Principles
http://msdn.microsoft.com/library/en-us/dndbdes/html/ch04DDP.asp
Fundamentals of Relational Database Design
http://support.microsoft.com/?id=129519
Understanding Relational Database Design
http://support.microsoft.com/?id=234208
Understanding Relational Database Design (downloadable Word doc)
http://support.microsoft.com/?id=164172
Fundamentals of Relational Database Design
http://www.microsoft.com/technet/Access/technote/ac101.asp
 
Maybe I wasn't fully clear on the structure of the data I'm using. What I'm
doing is taking, on average, 3 measurements on each of the bones in a
skeleton (approx 200) and a series of genetic data (approx 400 points at
present and eventually to be about 2000 points) for that particular specimen.
I'd like to be able to store this data and retrieve a particular dataset -
certain skeletal measurements that possibly correlate to the genetic data -
for export to a statistcal software suite. Ideally, all this data would be
stored in a spreadsheet, but that doesn't allow for the querying and
reporting functioality I'll need to extract the proper sub-set of data. Since
the dataset is far too big for an Access table, I've had to break the data up
into various tables while using the specimen ID as the relating field betwen
tables (approx 30 at present)

In terms of normalization, since each skeletal measurement ranges from
approx 0.0001 to 10.0000, using the measurement itself as a key is
impractical. In terms of each gene, in some cases the measurement is
identical across all specimens so far and therefore the data point could be
used as a primary key for that gene, but in most cases, each specimen differs
from all the others and therefore can't be used. I've looked at my tables and
if I'm reading the rules for 1st, 2nd and 3rd normal forms correctly, they
should be OK.
 
At a very minimum, the measurements for each of the 200 bones should be
individual rows in a separate table, not separate fields in the same row of
a table. In fact, depending on the actual measurements, each of the 3
measurements should possibly be separate rows as well.

In other words, you might have a table that stores information about each of
the skeletons, a table that stores information about each of the bones (with
a foreign key to link it to the skeleton from which it came) and a table
that stores the measurement information (with a foreign key to link it to
the specific bone to which it relates)
 
Back
Top