50 FIELDS ON FORM

[Glint]

Hi Guys,
The first part of this question has to do with the design of the database.
The form I need to dsplay has roughly 60 cells. Each cell holds a color and a
number. Is it wise to design the underlying table with 120 fields so that all
the values will be captured, or is it better to have 2 tables (of 60 fields
each) that will be linked by a query? Which design makes for better
performance? I read somewhere that fields should be minimal. But then both
values in each cell have to be kept, no cell being blank. Is there not a
better design than my dumb suggestions?

The second problem is how to retrieve those values. Getting the numbers was
no problem. But the colours gave me horrors. I tried the On Current Event of
the form with Access 2002, and this worked, BUT NOT WITH ACCESS 2007 which I
am now using:

Dim Tx As Control, X As String, Y As Integer
For Each Tx In Me.Controls 'Screen.ActiveForm
If TypeOf Tx Is TextBox Then
For Y = 1 To 9
If Tx.Tag = Y Then
'Tx.BackColor = 16777215
X = Tx.Name & "C"
If Me(X) > 1 Then
Tx.BackColor = 255
Else
Tx.BackColor = 16777215
End If
End If
Next
End If
Next

For this, I had named the fields that held numbers A11 to A80, and the
fields that held the colors a corresponding A11C to A80C. Then I used the Tag
property to separate the textboxes that need to display numbers and colors.

With Access 2007, the numbers display but the form does not change colors,
using the colors of the first record throughout.

My code stretched to Jericho, and I know there has to be a much better way.
Please help.

 

[Stefan Hoffmann]

hi,

The first part of this question has to do with the design of the database.
The form I need to dsplay has roughly 60 cells. Each cell holds a color and a
number. Is it wise to design the underlying table with 120 fields so that all
the values will be captured, or is it better to have 2 tables (of 60 fields
each) that will be linked by a query? No.

Which design makes for better performance?

None of these two.

I read somewhere that fields should be minimal. But then both
values in each cell have to be kept, no cell being blank. Is there not a
better design than my dumb suggestions?

Store the color/number pair in its own table:

ColorNumber:
ID AutoIncrement Primary Key
Color Number(Long)
Number Number(Long)

Then create a junction table between your master table and this color table.

btw, what kind of information is the color/number pair representing?


mfG
--> stefan <--

 

[Glint]

Thanks, Stephan.
If I get you correctly, you suggest that there should be 3 tables:
a) First table (basically a look-up table) consisting of an ID (primary)
field and a field describing the cell. Then it holds 60 records.
b) Second Table (also a look-up table) consisting of ID field and color type.
c) Third table joining the first 2 to have its own ID field, a reference to
First Table, and a reference to Second Table, all in numbers.
So, if I base my form on the third table, each "record" that a user sees
will be an array of 60 records from the Third Table, all having the same ID
of First Table.
Sounds great! Have to try it right away.
Thanks again.

 

[Stefan Hoffmann]

hi glint,

If I get you correctly, you suggest that there should be 3 tables:
a) First table (basically a look-up table) consisting of an ID (primary)
field and a field describing the cell. Then it holds 60 records.

I would not call it a lookup table, besides that this is imho the
correct approach.

b) Second Table (also a look-up table) consisting of ID field and color type.

This is a real lookup table.

c) Third table joining the first 2 to have its own ID field, a reference to
First Table, and a reference to Second Table, all in numbers.
So, if I base my form on the third table, each "record" that a user sees
will be an array of 60 records from the Third Table, all having the same ID
of First Table.
Sounds great! Have to try it right away.

Not quite what I had in mind. But I'm not sure if I understood your problem.

Apparently you have one table like this:

ID;Field1;..;Field60
1;red-10;...;yellow-11

If so you need your color lookup:

Color:
ID AutoInc, primary key
ColorName Text(64) not null, not empty, unique index

If your the fields 1-60 only contain numbers then you can refactor your
table form above:

ID Number(Long) not null
FieldNumber Number(Long) not null
ColorID Number(Long) not null
Value Number(Long) not null

with a combined primary key over ID and FieldNumber.

If the field names contain any meaningful name, then create another lookup:

FieldType:
ID AutoInc, primary key
FieldTypeName Text(64) not null, not empty, unique index

Depending on the content you may choose a more descriptive name instead
of FieldType.

Your refactored table should look like

ID Number(Long) not null
FieldTypeID Number(Long) not null, foreign key to table FieldType
ColorID Number(Long) not null, foreign key to table Color
Value Number(Long) not null

with a combined primary key over ID and FieldNumber.

What I called junction table in my first post depends on your actual
needs. If you need one, then replace the ID field with an appropriate
field and foreign key to its master.


mfG
--> stefan <--

 

[Glint]

Thanks again.
I ge it now. The colors are just two for now, so the color table may not be
necessary.