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.