Table design rows or columns

[oli82]

Thanks for your help in advance you have saved me an a number of occasions now.

I am about to built quite a sizable database and need help with the table structure. The database will contain lots of values of chemical tests from rock samples.

If I was to have each chemical that could be conceivably tested for it would be 200+ only 10% of the columns would be populated by a value. The chemicals do have classes however and a previous database has worked by having about 15 tables representing different chemical classes in which each column is a different chemical.

I think its better to have a lookup table of all the possible chemicals and then store the values in row form so that you have the same sample repeated in many rows where each row is a different chemical and then using crosstab queries to tabulate the data later on.

My only concern is data entry surely its going to be a pain to add data through the row method as opposed to the column method.

Is there an easy way to add data using forms in this method and is there any vba to convert data from columns into rows. i.e.

B C
1 q r
2 y n

into

b q
1 c r
2 b y
2 c n

Many Thanks for your help.

Oliver

 

[georgedwilkinson]

I overcame this exact problem by creating a separate subform for each "column" I wanted to represent. In your case, that would equate to 15 - 200 subforms.

The natural solution to me is to create a subform for each of the popular entries and then a subform for adding any other entry from the over 200 available.

BTW, when I used this method, it required zero VBA.

I think your approach and your question is right on. You definitely don't want to "flatten" the data, just make it appear flat when it is appropriate.

 

[oli82]

Thanks for the comments George, to confirm do you agree that putting the data in rows is suitable and then having each subform as a potential different row for a record. I don't quite understand how the subforms would work, do you have lots tabs in 1 form - if thats the case wont the load up time of the form be really long.

Many Thanks for your help,

Oliver