Split my Table ?

[BillS3]

My primary table in 2003 will store numerical inputs for (2) types of
instruments. Instrument #1 only requires an ID field plus 3 fields for test
points. I expect the input to be about 1 million records per year. Instrument
#2 is identical to #1, and requires the same 3 fields plus 21 more fields for
the additional test points, this will only generate about 100 records per
year. If combine the both instruments into one table, wouldn't there be a
huge amount of wasted space?

Thx - Bill

 

[Pat Hartman\(MVP\)]

This is one of the RARE cases when a 1-1 relationship is useful. Create a
new table to hold the additional fields. Make its primary key the primary
key of the original table. If the original PK is an autonumber, the PK of
the new table needs to be long integer. Create the 1-1 relationship from
the original table to the new table. Enforce RI and specify Cascade delete.
Test to make sure that the original table is really the "parent" of the new
table.
Use a left join in your queries so that the "missing" rows in the new table
won't cause original table records to be lost.

 

[BillS3]

Thank you for the info. There is so much to learn about Access. This will
help set the foundation for my DB. Is there a readily available information
source for the beginner on designing a good, solid set of tables?

Again, Thx for your expertise-Bill

 

[Pat Hartman\(MVP\)]

There is lots of stuff available. One site with several good articles is
www.fmsinc.com - You should also search the MS knowledge base and MSDN
library for articles on normalization. Expand your search with Google and
you'll find tons of info - some more reliable than others.

 

[Lynn Trapp]

In addition to Pat's excellent recommendations, I would suggest that you
check out the Access Junkie (MVP, Jeff Conrad) website. See the link below.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[BillS3]

Pat & Lynn-
Thanks for your time..time and more time. To a older former Excel junkie,
this all helps so much!
Bill