Data parameters vary based on user input. Need your suggestions

[parkerjallen]

I'm about to write a database for a fiberoptic cable company where I work. The cables are to be tested and the results will be input into a database. The trouble is, the fibers vary. The popular numbers are 1, 2, 4, 12, 24. But a better list of the common numbers is (1, 2, 4, 6, 8, 12, 24, 48, 72, 96, 144) with the potential of a completely random (hopefully increment of 12) custom order.

Each fiber has to be tested at 2 ends, so for a 12-fiber that would be 24 inputs. What would be the best way to set these tables up? One for each of the values from 1-8, with one for 12s? I get to decide how each fiber is called, its serial number if you will, so I was thinking maybe I can separate the larger ones into sub-fibers based on 12, with an additional suffix on the end of an autonumber used for the serials.

Kind of a weird system, and it's been a while since I touched Access. Your help is greatly appreciated!

 

[BigHappyDaddy]

You need a refresher on data normalization. You should be able to set up one table that holds all of your cables. Try to google data normalization.

 

[parkerjallen]

I think what I've decided to do is, assume each cable has 12 threads (they don't all). If you have less than 12 threads, great, fill out which cells you need. If you go over 12, which is usually in increments of 12, you create a new record with its own serial number for the next batch of 12 threads. Does that seem optimal to you?

 

[parkerjallen]

Well, I guess the "normal" thing to do would be to create a table to assign each cable a serial number, then another table to put in test results for each fiber in that cable as its own record. In the end, I need to be able to print the test results on a label the size of a shipping label.

 

[BigHappyDaddy]

No, it doesn't seem optimal. If you can normalize your tables, it will save you tons of headaches in the future. I think everyone here would tell you the same thing.

 

[BigHappyDaddy]

Well, I guess the "normal" thing to do would be to create a table to assign each cable a serial number, then another table to put in test results for each fiber in that cable as its own record. In the end, I need to be able to print the test results on a label the size of a shipping label.

That's better. Then you could query the test results table by serial number and can see the test results of each fiber. Exactly one record would be returned for each tested fiber in a cable (serial number).