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 "ProblemSubcate gory"
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.c om" <fo***@nospam.A ccessMonster.co m> 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

tblProblemDetai ls
ProblemID PK
ProblemSubCatID PK

tblProblemSubCa ts
ProblemSubCatID autonumber, PK
ProblemCatID
SubCatDescripti on

-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
subcategorie s 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
subcategorie s, I want the text box to read: Bracket; Emergency Brake;
Housing; Switch.

Hopefully, this will appear in the table under the field "ProblemSubcate gory"
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 UnitSerialNumbe r 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:

TechnicalProble msTable:

CaseNumber
SolutionNumber
Date
DealerNumber
UnitSerialNumbe r
ModelNumber
UnitHours
Status
PrimePart
Solution
ProblemCategory
ProblemSubcateg ory
Comments
CaseCreatedBy
ProblemCategory Table

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

ModelNumberTabl e

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

DealerInformati onTable

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...Shanna n

Shannan

[Tom van Stiphout]

On Wed, 15 Jun 2005 15:32:41 GMT, "Shannan Casteel via
AccessMonster.c om" <fo***@AccessMo nster.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_AfterU pdate event write something
like:
lblDealerPhone. Caption = dropcown.column (2)
lblDealerAddres s.Caption = dropdown.column (3)
etc., thus pulling that information from the additional columns.

Q2: ProblemSubCateg ory.
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 TechnicalProble ms table will have ProblemCatID (NOT
ProblemCatDescr iption), and a 1:M link to ProblemCats.
The many Subcats would be stored in a TechnicalProble msSubcats 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 UnitSerialNumbe r 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:

TechnicalProbl emsTable:

CaseNumber
SolutionNumb er
Date
DealerNumber
UnitSerialNumb er
ModelNumber
UnitHours
Status
PrimePart
Solution
ProblemCategor y
ProblemSubcate gory
Comments
CaseCreatedB y
ProblemCategor yTable

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

ModelNumberTab le

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

DealerInformat ionTable

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...Shann an

Shannan

[Shannan Casteel via AccessMonster.com]

Tom,

I really appreciate your help.

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

ProblemCategory Table

ProblemCategory Table

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

ProblemSubcateg oryTable

CaseNumber ProblemSubcateg oryID ProblemSubcateg ory
(AutoNumber) 1 Air Cleaner
(AutoNumber) 2 Bearing
(AutoNumber) 3 Belt
.. . .
.. . .
.. . .
(AutoNumber) 141 Accesory Electric
Ok, now how would I relate each ProblemSubcateg ory 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.c om" <fo***@AccessMo nster.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.
ProblemCategory Table: perfect.
ProblemSubcateg oryTable: add one more field: ProblemCategory ID.
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 ProblemCategory Table.ProblemID to
ProblemSubcateg oryTable.Proble mCategoryID, 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 ProblemSubcateg ory matter goes, I guess I just don't understand.
Would the table structure be something like this?

ProblemCategor yTable

ProblemCategor yTable

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

ProblemSubcate goryTable

CaseNumber ProblemSubcateg oryID ProblemSubcateg ory
(AutoNumber) 1 Air Cleaner
(AutoNumber) 2 Bearing
(AutoNumber) 3 Belt
. . .
. . .
. . .
(AutoNumber) 141 Accesory Electric
Ok, now how would I relate each ProblemSubcateg ory 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
ProblemSubcateg oryTable as above with four fields labeled
ProblemSubcateg oryID, ProblemSubcateg ory, ProblemCategory ID, 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...T hanks for all your
help...,

Shannan

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