Help with table and form design

Hi,

I've got a two part question on table and form design (sorry for the
length but it takes a bit to explain). Our sales department uses a
look-up table to help the them select the best equipment to use for a
customer's needs. It basically consists of a table with the following
setup:

Equipment: Option1: Option2: Option3: Option 4: ...
-------- ------- ------- -------- --------
Model1 Yes No No No
Model2 Yes Yes No No
Model3 Yes Yes Yes No
Model4 No Yes No No

Based on the Options the customer selects, the sales clerk will
recommend the corresponding Model number. All the models are unique
(there will be no models that has the same set of options). All the
Option fields will be Yes/No and if there is no corresponding Model
associated with the selected choices (ex: Yes,No,No,No), then the table
will not give a recommendation.

What they would like to do is for this logic to be programmed in Access
via a form so that the clerk can select the options and the form will
give the matching model number. There's definitely potential to offer
more options and update this lookup table. The people using this will
not be know how to program in VBA but they will know how to use Access
and insert new fields into an existing table. What I was thinking of
doing was to use a form and underlying VBA code to query for the
corresponding model numbers based on the which options are selected.
The form will have to have some sort of input to read the available
options (has to determine number of fields in the look up table) and
give a match if there is any. Does anyone have any recommendations on
how to query for this lookup table? I'm getting stumped on handling
the dynamic fields because the field names can change and also the
number of fields in the lookup table can change. Also, is there an
effective way to design the form so that it doesn't care how many
"option" fields are in the look-up table and but still output the
available options (via checkboxes? maybe) on runtime. If there's a
better way of designing both the look-up table and the query form, I'm
all ears. I'm pretty familiar with VB coding for access and using SQL
statements to query for items but am just stuck on the initial design
aspect of this. Any help would be appreciated. Thanks again.

re: Help with table and form design

greetings,

The first recommendation I would make is to change the design of your
table to something like this:

Equipment Option YesNo
model1 option1 yes
model1 option2 no
model1 option3 yes
model2 option4 no
model2 option3 yes
model2 option5 no
...

A cardinal rule that is written in stone and in every developer's
handbook is that if you have to keep adding columns to a table after it
is in production - the table was designed incorrectly. The process of
designing the table in the correct manner is called normalization. And
the result of an improperly designed table is that it becomes very
difficult to query it for useful information and very difficult to
mainain thus making an application unscalable.

With the desing above you can add as many options as you want without
have to alter the design of the table. So the trick is to design your
tables so that they do not require altering after they are in
production. If you know that you will have to alter the table down the
road, then you need to rethink the design so that you don't have to
alter it. More times than not - this is not easy. But if the goal is
to design a real application for business - which would require table
relationships - without proper design - normalization - it will be
almost impossible to add relationships, and all you get (I say this
respectfully) is an amateur effor that is very limited in what it can
do. Just remember that the tables of a Database application are the
backbone of that application.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

re: Help with table and form design

Thanks for the input! I do have one question regarding the
implementation of looking up the new table. So I made a listbox that
contains the possible options one can select. Using the multi-select
feature, I'm able to determine which option has been selected. Now
that I have this, how do I go about actually finding the corresponding
model number? I know how to do this if the Options were fields in a
table but in this new top-down structure, I'm kinda lost. My initial
thought was to go through the list in a loop and compare each model
type's options to the selected options in the listbox but I think I'm
still not thinking in terms of databases and queries. Is there a way
to make a SQL statement to compare the options automatically? Thanks.


Rich P wrote:

re: Help with table and form design

I do not understand what you are trying to query for. If you want to
query for all models that have option1, then use option1 as the query
parameter. You have to be more specific what you are trying to query
for. I will give you another example about how to use normalized
tables.

You can have one other table where you store each option. You create a
relationship between that table and the main table by linking the option
fields together. It would be a one to many relationship. If someone
tries to enter an invalid option - an option that is not in the options
table - that entry would be rejected because it would violate referntial
integrity (because of the relationship you established). That is what
relationships are for, in addition to cascase updates and deltes.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

re: Help with table and form design

Building on Rich P's structure (table name zSell), your input form would
have 2 columns, Options and YN, (table zSelect). The following query would
return all models with 3 matching options.

SELECT zSell.Equipment, Count(zSell.Option) AS CountOfOption
FROM zSelect INNER JOIN zSell ON (zSelect.YN = zSell.YN) AND (zSelect.Option
= zSell.Option)
GROUP BY zSell.Equipment
HAVING (((Count(zSell.Option))=3));

<yanjie.ma@gmail.comwrote in message
news:1160684711.883211.228580@m73g2000cwd.googlegr oups.com...