Storing check box selections in a textbox as appended strings

[Shannan Casteel via AccessMonster.com]

I'll explain as well as possible...not an experienced user...but learning.

I am working on a database for different kinds of problems with machines. A
user will be able to select a problem category from a combo box (i.e. Engine,
Fuel, Electrical, Steering, Power Train, Brakes, Hydraulic, Chassis/Structure,
etc.). Upon the users selection a series of cooresponding check boxes
appears on the form with Problem Subcategories. For instance, if the user
selects brakes in the combo box a series of check boxes appears with
subcategories related to brakes (i.e. Bracket, Emergency Brake, Brake
Calliper, Cable, Disc, Housing, Knob, Lever Assembly, Switch).

To store the selected check boxes I have put a text box near the bottom of
the form that is labeled Problem Subcategory. I want each selected check box
to appear in the text box. For instance, if the user had selected brakes as
the Problem Category and Bracket, Emergency Brake, Housing, and Switch as the
subcategories, I want the text box to read: Bracket; Emergency Brake;
Housing; Switch.

Hopefully, this will appear in the table under the field "ProblemSubcategory"
as a string that reads just like the text box. If there is a better way of
doing this please let me know. I appreciate any and all help...Thanks alot.

[Tom van Stiphout]

On Wed, 15 Jun 2005 13:44:10 GMT, "Shannan Casteel via
AccessMonster.com" <fo***@nospam.AccessMonster.com> wrote:

I'm OK with you displaying the subcats that way, but you probably
don't want to store them in the database like that. If you do, it
makes future queries such as "per category, report how many times each
subcat occurred in the last 6 months" MUCH more difficult.
The best db design depends on the finer points of your requirements;
the below one is just an approximation.

Create the best database design first; then worry about the best user
interface.

tblProblems
ProblemID autonumber, PK
MachineID
ProblemCatID
DateOccurred

tblProblemDetails
ProblemID PK
ProblemSubCatID PK

tblProblemSubCats
ProblemSubCatID autonumber, PK
ProblemCatID
SubCatDescription

-Tom.

I'll explain as well as possible...not an experienced user...but learning.

I am working on a database for different kinds of problems with machines. A
user will be able to select a problem category from a combo box (i.e. Engine,
Fuel, Electrical, Steering, Power Train, Brakes, Hydraulic, Chassis/Structure,
etc.). Upon the users selection a series of cooresponding check boxes
appears on the form with Problem Subcategories. For instance, if the user
selects brakes in the combo box a series of check boxes appears with
subcategories related to brakes (i.e. Bracket, Emergency Brake, Brake
Calliper, Cable, Disc, Housing, Knob, Lever Assembly, Switch).

To store the selected check boxes I have put a text box near the bottom of
the form that is labeled Problem Subcategory. I want each selected check box
to appear in the text box. For instance, if the user had selected brakes as
the Problem Category and Bracket, Emergency Brake, Housing, and Switch as the
subcategories, I want the text box to read: Bracket; Emergency Brake;
Housing; Switch.

Hopefully, this will appear in the table under the field "ProblemSubcategory"
as a string that reads just like the text box. If there is a better way of
doing this please let me know. I appreciate any and all help...Thanks alot.

[Shannan Casteel via AccessMonster.com]

Tom,

I appreciate your thoughts on the matter.

Just to give a little more information... This data base will be used in the
customer support office of a heavy construction equipment manufacturer.
Everytime a customer support rep. answers the phone he will enter information
into the database.

The CaseNumber will be an autonumber.

The SolutionNumber may or may not exist depending on the frequency of the
problem and whether a solution has been written for that problem.

The DealerNumber will be selected from a combo box. After this has been
selected information will hopefully appear on the screen displaying Phone
Number, Address, Etc. from an externally linked table--I don't know how to
get this info to appear either.

The UnitSerialNumber will be manually typed by the user, as well as UnitHours,
PrimePart, Comments, CaseCreatedBy.

I guess I was really asking for some code to make some of this stuff work
right, especially the dealer number code and the problem subcategory code.

I've got everything working except those 2 items. I was considering adding a
couple of buttons to the database that said something like "Write Solution...
" and "Search". Any suggestions?
If I understood correctly, suggested 3 tables. I have several tables: listed
below:

TechnicalProblemsTable:

CaseNumber
SolutionNumber
Date
DealerNumber
UnitSerialNumber
ModelNumber
UnitHours
Status
PrimePart
Solution
ProblemCategory
ProblemSubcategory
Comments
CaseCreatedBy
ProblemCategoryTable

Engine
Fuel
Electrical
Steering
Power Train
Brakes
Hydraulic
Chassis/Structure
Attachments
Machine Options
Parts
Misc

ModelNumberTable

RT60
TF300B
RT360
RT460
RT560
RT660
RT960
MAXI-C
T560
6010
6030
DD-3238

DealerInformationTable

This table lists the dealer numbers with cooresponding phone numbers,
addresses, etc. It is a linked table.
I have a couple of other minor tables, but nothing significant.

One last question...
Is it really necessary to have a primary key. I've never gotten the purpose
of them.

Sorry to trouble you with this mess...I feel so behind...
Thanks...Shannan

Shannan

[Tom van Stiphout]

On Wed, 15 Jun 2005 15:32:41 GMT, "Shannan Casteel via
AccessMonster.com" <fo***@AccessMonster.com> wrote:

I'll try to address your questions.
Q1: Selecting dealer number displays addl dealer info.
A: In the dropdown list, have additional columns (perhaps hidden) with
Phone etc. Then in the dropdown_AfterUpdate event write something
like:
lblDealerPhone.Caption = dropcown.column(2)
lblDealerAddress.Caption = dropdown.column(3)
etc., thus pulling that information from the additional columns.

Q2: ProblemSubCategory.
A: As I wrote before, the best db design seems to be two tables
ProblemCats and ProblemSubCats in one-to-many (1:M) relation, each
with ID values etc.
Your TechnicalProblems table will have ProblemCatID (NOT
ProblemCatDescription), and a 1:M link to ProblemCats.
The many Subcats would be stored in a TechnicalProblemsSubcats table,
which maylook like this:
CaseNumber
ProblemSubcatID
Then on your form you create a subform where the user can select al
Subcat from a dropdown list (2 columns; hidden ID and visible
Description) which draws its data from tblSubcats. The subform would
allow any number of subcats to be selected (similar to the Northwind
sample application's Orders form allows as many OrderDetails as user
may need).

Q3: Primary Key;
A: VERY important. It forces you to declare what (combination of)
field(s) makes up a uniquely identifyable row in the table. This is
one of the cornerstones of relational database design. We prefer a
natural key (e.g. a short alphanumeric ProductCode for the Products
table), but we'll take a surrogate key (e.g. an autonumber ProductID
for the Products table) that is never shown to users but is used for
internal relational purposes. In this case there typically is another
unique index in the table (e.g. on ProductName) because who wants to
drop down a Products list and see two items by the same name...
Also important because without it a table may not be updatable.

-Tom.

Tom,

I appreciate your thoughts on the matter.

Just to give a little more information... This data base will be used in the
customer support office of a heavy construction equipment manufacturer.
Everytime a customer support rep. answers the phone he will enter information
into the database.

The CaseNumber will be an autonumber.

The SolutionNumber may or may not exist depending on the frequency of the
problem and whether a solution has been written for that problem.

The DealerNumber will be selected from a combo box. After this has been
selected information will hopefully appear on the screen displaying Phone
Number, Address, Etc. from an externally linked table--I don't know how to
get this info to appear either.

The UnitSerialNumber will be manually typed by the user, as well as UnitHours,
PrimePart, Comments, CaseCreatedBy.

I guess I was really asking for some code to make some of this stuff work
right, especially the dealer number code and the problem subcategory code.

I've got everything working except those 2 items. I was considering adding a
couple of buttons to the database that said something like "Write Solution...
" and "Search". Any suggestions?
If I understood correctly, suggested 3 tables. I have several tables: listed
below:

TechnicalProblemsTable:

CaseNumber
SolutionNumber
Date
DealerNumber
UnitSerialNumber
ModelNumber
UnitHours
Status
PrimePart
Solution
ProblemCategory
ProblemSubcategory
Comments
CaseCreatedBy
ProblemCategoryTable

Engine
Fuel
Electrical
Steering
Power Train
Brakes
Hydraulic
Chassis/Structure
Attachments
Machine Options
Parts
Misc

ModelNumberTable

RT60
TF300B
RT360
RT460
RT560
RT660
RT960
MAXI-C
T560
6010
6030
DD-3238

DealerInformationTable

This table lists the dealer numbers with cooresponding phone numbers,
addresses, etc. It is a linked table.
I have a couple of other minor tables, but nothing significant.

One last question...
Is it really necessary to have a primary key. I've never gotten the purpose
of them.

Sorry to trouble you with this mess...I feel so behind...
Thanks...Shannan

Shannan

[Shannan Casteel via AccessMonster.com]

Tom,

I really appreciate your help.

As far as the ProblemSubcategory matter goes, I guess I just don't understand.
Would the table structure be something like this?

ProblemCategoryTable

ProblemCategoryTable

ProblemID ProblemCategory
1 Engine
2 Fuel
3 Electrical
4 Steering
5 Power Train
6 Brakes
7 Hydraulic
8 Chassis/Structure
9 Attachments
10 Machine Options
11 Parts
12 Misc

ProblemSubcategoryTable

CaseNumber ProblemSubcategoryID ProblemSubcategory
(AutoNumber) 1 Air Cleaner
(AutoNumber) 2 Bearing
(AutoNumber) 3 Belt
.. . .
.. . .
.. . .
(AutoNumber) 141 Accesory Electric
Ok, now how would I relate each ProblemSubcategory to each ProblemCategory?
In other words, how does the database know that Air Cleaner is an Engine
Problem?

This stuff is way, way, way over my head.

Thanks for all your help you've been great,

Shannan

--
Message posted via http://www.accessmonster.com

[Tom van Stiphout]

On Thu, 16 Jun 2005 14:32:58 GMT, "Shannan Casteel via
AccessMonster.com" <fo***@AccessMonster.com> wrote:

Good for you for at least trying. I guess the marketing propaganda out
of Redmond doesn't tell you some of this stuff is HARD.
ProblemCategoryTable: perfect.
ProblemSubcategoryTable: add one more field: ProblemCategoryID.
Your #1 gets a value of 1, and #141 a value of 3. That's how you tie
things together in a relational database design. Don't forget to open
the Relationships window, put these 2 tables on the design surface,
and drag-n-drop ProblemCategoryTable.ProblemID to
ProblemSubcategoryTable.ProblemCategoryID, and check the box to
enforce referential integrity. Leave cascade updates unchecked, and
check cascade deletes. Once you do that, and carry the same idea
forward throughout your db, your db design is in the top 25% of all db
designs.

-Tom.

Tom,

I really appreciate your help.

As far as the ProblemSubcategory matter goes, I guess I just don't understand.
Would the table structure be something like this?

ProblemCategoryTable

ProblemCategoryTable

ProblemID ProblemCategory
1 Engine
2 Fuel
3 Electrical
4 Steering
5 Power Train
6 Brakes
7 Hydraulic
8 Chassis/Structure
9 Attachments
10 Machine Options
11 Parts
12 Misc

ProblemSubcategoryTable

CaseNumber ProblemSubcategoryID ProblemSubcategory
(AutoNumber) 1 Air Cleaner
(AutoNumber) 2 Bearing
(AutoNumber) 3 Belt
. . .
. . .
. . .
(AutoNumber) 141 Accesory Electric
Ok, now how would I relate each ProblemSubcategory to each ProblemCategory?
In other words, how does the database know that Air Cleaner is an Engine
Problem?

This stuff is way, way, way over my head.

Thanks for all your help you've been great,

Shannan

[Shannan Casteel via AccessMonster.com]

Tom,

Ok...I've done everything you've said...and so far it looks like its going to
work. Thanks for your help. Last question...I promise. Now that I have my
ProblemSubcategoryTable as above with four fields labeled
ProblemSubcategoryID, ProblemSubcategory, ProblemCategoryID, ProblemCategory,
I need a way of allowing the user to select more than one value from the
Problem Subcategory as long as all his/her choices are within the respective
Problem Category. I would prefer to have a combo box with the Problem
Category, and upon selection a group of check boxes appear for that
respective category, but I don't know how to get the database to store all
those check box selections...or even one of those selections for that matter.
Any suggestions on this would be greatly appreciated...Thanks for all your
help...,

Shannan

--
Message posted via http://www.accessmonster.com