Design Question - Fixed numbers in Combo Box

[Rich]

I have a form with approx 20 combination boxes. Each combo box needs to have
numbers 1 through 5 populated in them. Users will be using these boxes to
select risk rankings for various business areas. Thier selection will be
recorded into a master table with a field for each business area. I am not
sure the best method to populate these combo boxes based on sound database
design.

Do I use a fixed 'value list' of 1 thru 5 for each cbo?
Do I have a table contain five records (each containing 1 thru 5) that is
linked in a 1 to many relationship to approx 20 different fields? (this may
create unnecessary overhead and inefficiencies I'm thinking)

Any advice would be helpful.

 

[Duane Hookom]

I would not use "approx 20" fields since this is not normalized. Each
business area should create a record, not a field. Your business area is
data and your application should allow for easy modification of business
areas without modifying tables, forms, queries, reports,...

Then, you have only one lookup table related to you rankings field.

 

[Vincent Johns]

I would not use "approx 20" fields since this is not normalized. Each
business area should create a record, not a field. Your business area is
data and your application should allow for easy modification of business
areas without modifying tables, forms, queries, reports,...

Then, you have only one lookup table related to you rankings field.

My suggestion would be to design your Table of responses so that, for
each risk ranking, you append a record to the Table containing
information such as

- business area identifier or name
- user's response (risk ranking)
- user or questionnaire identifier

I'm not sure exactly how to suggest you populate it, as you might want
to add a record when focus leaves the control, or you might want to wait
until the data-entry person finishes entering all the data from a survey
form. (Do respondents enter their own data? If so, you'll likely need
to include extra error handling.)

The two identifiers may be stand-alone fields, or (depending on what
other information you have) they may link to other Tables, such as
[Business Area] or [Users].

Since you don't need to allow the user to enter any values outside of
the 1-5 set, you might find that list boxes or option buttons, instead
of combo boxes, will do what you want.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[PC Datasheet]

How about the following tables ---
TblEmployee
EmployeeID
FName
MI
LName

TblBusinessArea
BusinessAreaID
BusinessAreaName

TblRisk
RiskID 'will be 1 to 5
RiskDesc

TblRiskAssessment
RiskAssessmentID
EmployeeID
BusinessAreaID
RiskID
AssessmentDate

Use a form/subform. Base the main form on TblEmployee. Create a query based
on TblBusinessArea and TblRiskAssessment. Outer join TblBusinessAreaID in
both tables and your query will produce a list of all business areas.
Include EmployeeID, RiskID and AssessmentDate in the query.

On your form, use an option group with 1 to 5 to enter the risks instead of
a combobox. The users will only have to Click on an option. You can also put
a plase to enter the date in the form header so the user doesn't have to
enter the date for each record. In the subform's AfterUpdate event, just put
the following code:
Me!AssessmentDate = Me.Parent!AssessmentDate.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

If you don't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.