Functionality Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All:

I'm not sure if I'm in the correct forum. If I'm not please let me know.

I am in the process of designing an ADP for a homebuilder that will track
customer choices on a variety of options on the home.


The main data set is called the Customer Selection sheet and it will consist
of the following subsets of data:

Exterior Color Selections
Interior Color Selections
Appliance Selections
Cabinet Selections
Flooring Selections
Plumbing

Within each of these data subsets, there are any number of individual line
items on which the customer has to make a choice. The number of these line
items depends on the plan of the house. The builder has about 10 different
plans and each or these plans can have up to 10 different flavors. The
availability of the line items in each Selection subset depends upon what
"flavor" of the plan is chosen.

Because of the ongoing problems the company has had with inputing improper
information or not getting required selection choices for a given plan, I
have been asked to design a data entry form that displays all of the required
selection requirements based upon which flavor of the housing plan is
selected. I potentially would have to potentially display up to 100 possible
form views based on the flavor of the plan that is chosen.


Here is my question: What is the best way to approach this issue in the
most efficient manner? I was going to Hide/Unhide fields based upon the ID
of the Plan that is chosen. If I have 100 possible choices, would this be a
feasible approach? My other idea was to possibly store the form definition
data in a table and have Access display the proper fields by looking at
definition in the table for that particular PlanID. My question then
becomes, can this be actually done and how could I use vba to translate the
table data into the form that's needed.

Any comments or suggestions would be appreciated.

Thanks
 
Brennan

You are going to need to model this, but it seems like you can handle most
of the integrity in the database tables. That way your forms don't have to
do as much work. For example, start with tables for the Plans and selection
category.

CREATE TABLE Plans (
plan_id AS CHAR(2) NOT NULL PRIMARY KEY
,descrip AS VARCHAR(20) NOT NULL
);

INSERT INTO Plans VALUES ('A', 'Model 1');
INSERT INTO Plans VALUES ('A', 'Model 1');
CREATE TABLE ExteriorColors (
color_code CHAR(4) NOT NULL PRIMARY KEY
,color_name VARCHAR(15) NOT NULL
);

CREATE TABLE InteriorColors (
color_code CHAR(4) NOT NULL PRIMARY KEY
,color_name VARCHAR(15) NOT NULL
);

....etc.
 
Brennan

(Sorry...my previous post was accidental.)

You are going to need to model this, but it seems like you can handle most
of the integrity in the database tables. That way your forms don't have to
do as much work. For example, start with tables for the Plans and each
selection category. (Obviously I don't know the standard coding structures
used for this industry, so don't laugh at the e.g.)

CREATE TABLE Plans (
plan_id AS CHAR(2) NOT NULL PRIMARY KEY
,descrip AS VARCHAR(20) NOT NULL
);

INSERT INTO Plans VALUES ('AA', 'Model 1');
INSERT INTO Plans VALUES ('BB', 'Model 2');
INSERT INTO Plans VALUES ('CC', 'Model 3');

CREATE TABLE ExteriorColors (
color_code CHAR(4) NOT NULL PRIMARY KEY
,color_name VARCHAR(15) NOT NULL
);

INSERT INTO ExteriorColors VALUES ('1111', 'Red');
INSERT INTO ExteriorColors VALUES ('1112', 'Peach');
INSERT INTO ExteriorColors VALUES ('1113', 'Lime');
INSERT INTO ExteriorColors VALUES ('1114', 'White');

CREATE TABLE InteriorColors (
color_code CHAR(4) NOT NULL PRIMARY KEY
,color_name VARCHAR(15) NOT NULL
);

INSERT INTO InteriorColors VALUES ('2111', 'Bone');
INSERT INTO InteriorColors VALUES ('2112', 'Ivory');
INSERT INTO InteriorColors VALUES ('2113', 'Mustard');
INSERT INTO InteriorColors VALUES ('2114', 'Beige');

....etc.

Next you limit the options by association.

CREATE TABLE PlanExtColors (
plan_id CHAR(2) NOT NULL
REFERENCES Plans (plan_id)
ON UPDATE CASCADE
ON DELETE CASCADE
,allowed_color CHAR(4) NOT NULL
REFERENCES ExteriorColors (color_code)
,PRIMARY KEY (plan_id, allowed_color)
);

INSERT INTO PlanExtColors VALUES ('AA', '1111');
INSERT INTO PlanExtColors VALUES ('AA', '1113');
INSERT INTO PlanExtColors VALUES ('AA', '1114');
INSERT INTO PlanExtColors VALUES ('BB', '1111');
INSERT INTO PlanExtColors VALUES ('BB', '1112');

....etc.

Then you can use views and stored procedures to select the acceptable values
for your forms depending on the plan chosen. You can layer these rules
anyway you want. For example, I know when I had my house painted, selection
of the trim color was limited by the selection of the main color. The same
principle would apply...build an association table and use a view.

The table that actually stores the options for a client, could then use
foreign key references so that the integrity is ensured, e.g.:

CREATE TABLE ClientChoices (
client_id VARCHAR(10) NOT NULL
,survey_date DATETIME NOT NULL
...
,plan_id CHAR(2) NOT NULL
,ext_color_choice CHAR(4) NOT NULL
,int_color_choice CHAR(4) NOT NULL
...
,PRIMARY KEY (client_id, survey_date)
,FOREIGN KEY (plan_id, ext_color_choice)
REFERENCES PlanExtColors (plan_id, allowed_color)
,FOREIGN KEY (plan_id, int_color_choice)
REFERENCES PlanIntColors (plan_id, allowed_color)
...
);

Hopefully that gets you started.

Joe
 
Back
Top