normalization for variable data storage and reporting

[Tinnius Olsen]

Have some experience using MS Access 2002, probably around
intermediate level, but limited in true relational
database sciences. After checking out topics like
procedural query languages based on relational algebra,
nonprocedural query languages based on relational
calculus, unions, intersections, and inner and outer joins
it may be some time. However, I have been given the job of
creating a database for our Quality Department to hold
data from product audits. Each record will have data from
anywhere between five to forty measurements taken from
between two to three attributes, lot number,
date, "auditID", etc. Have purchased books on Microsoft
2000, 2002 Access development but they seem to be specific
to the more traditional type databases, for things -
names, addresses, book collections, etc. The biggest
problem I see is normalizing so statistical calculations
can be performed on each audit, Averages, Standard
Deviation and such. I would appreciate any help
especially if someone can point me to a book that covers
databases specific to working with variable data storage
and reporting.

(e-mail address removed)

 

[John Vinson]

Each record will have data from
anywhere between five to forty measurements taken from
between two to three attributes, lot number,
date, "auditID", etc.

It sounds like you have a perfectly traditional many (records) to many
(measurements) relationship, involving three tables and no variability
in table structure. Am I missing something?

 

[Tinius Olsen]

-----Original Message-----

It sounds like you have a perfectly traditional many (records) to many
(measurements) relationship, involving three tables and no variability
in table structure. Am I missing something?


.

Would I then Normalize:
Audit(RecordID, Product, Auditor)
|
|_____ RecordID/MeasurID______Length(MeasurID,L)
|
|______Diameter(MeasurID,D)

This could give me 400,000 length and diameter records to
10,000 audit records - will cause Access to take an
unreasonable long time to generate reports. As I said I
am still learning concerning DB design.