Storing selections for dependent multiselect list boxes or check boxes

[Shannan Casteel via AccessMonster.com]

I have three main tables. The first is the table that my main form will be
based on. This is where the user will enter all the data. The table is
called TechnicalProblemsTable. It looks like this:

I can't fit it on the screen, so I'll just list the field headings vertically.
CaseNumber-pk
SolutionNumber
Date
DealerNumber
UnitSerialNumber
ModelNumber
UnitHours
Status
PrimePart
ProblemCatID
ProblemCat
ProblemSubcatID
ProblemSubcat
Comments
CaseCreatedBy

ProblemCategoryTable...

ProblemCatID-pk ProblemCat
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
ProblemSubcategoryTable

ProblemSubcatID-pk ProblemSubcat ProblemCatID ProblemCat
1 Air Cleaner 1 Engine
2 Bearing 1 Engine
.. . . .
.. . . .
.. . . .
25 Cable 2 Fuel
26 Camshaft 2 Fuel
.. . . .
.. . . .
.. . . .
140 Technical 11 Parts
141 Sales 11 Parts
Ok, now, upon selection of a category in a combo box with the
ProblemCategoryTable used as the source, I would like a series of check boxes
or a multiselect list box with cooresponding subcategories to appear. I
think I know how to accomplish this...maybe, but I can't get the subcategory
selections to store in the TechnicalProblemsTable. Eventually I want the
user to be able to search the database for different Subcategory Problems.

Any help or suggestions would be greatly appreciated.

***I am not an Access expert - this is my first experience with this program.
I have spent about two weeks getting to where I am now.***

Thanks for your time,

Shannan

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

[rkc]

Shannan Casteel via AccessMonster.com wrote:

I have three main tables. The first is the table that my main form will be
based on. This is where the user will enter all the data. The table is
called TechnicalProblemsTable. It looks like this:

Is each individual problem treated as a seperate case?
The alternative would be a case with multiple problems.
There is a big difference in how the two would be modeled
as tables in the database.

If one case can have multiple problems then you need a
Case table and a junction table to link the multiple
problems to the single case.

Case (CaseNumber*, DateCreated, CreatedBy, Comments, etc.)
CaseProblem (CaseNumber, ProblemSubcatID)

The biggest problem you are facing is not getting information
from a form into a table. It's getting the structure of your
database to correctly model your real requirements.

[Shannan Casteel via AccessMonster.com]

rkc,

The purpose of this database will be to serve customer support
representatives(CSR) to a company that makes machines. When someone calls
with a problem on a particular machine the CSR will enter information about
the model number, the unit serial number, how many hours are on the machine,
and the main problem category. Only one main problem will be allowed. Upon
the selection of this main problem the check boxes or list box would appear
with the relevant subcategories. Out of the cooresponding group of
subcategory check boxes or list box, any number of them may be selected.
After this is done the CSR will enter some comments, along with who he is,
the date, and the case number. The next call would be a different case.

When a certain problem has occurred enough times the CSR might write a
solution as to how to resolve the problem and assign it a solution number.
Then that number would be assigned to each respective case that contained
that specific problem.

Thanks for your concern and continued help,

Shannan

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

[rkc]

>The purpose of this database will be to serve customer support

representatives(CSR) to a company that makes machines. When someone calls
with a problem on a particular machine the CSR will enter information aboutthe model number, the unit serial number, how many hours are on the machine,and the main problem category. Only one main problem will be allowed. Uponthe selection of this main problem the check boxes or list box would appearwith the relevant subcategories. Out of the cooresponding group of
subcategory check boxes or list box, any number of them may be selected.
After this is done the CSR will enter some comments, along with who he is,
the date, and the case number. The next call would be a different case.
Seems to me you could purchase something already built for this.
Maybe that's already been considered and rejected.

Anyway, see if this makes sense to you.

CustomerSupportReps (CSRID*, FirstName, LastName, etc.)
Information on your reps. The CSRID can be used in the Case table
so you don't get entries by Robert Dole, Bob Dole, R.Dole and Robbie D.
I don't know. Maybe you already have an employee table you didn't
mention.

Case (CaseNumber*, UnitSerialNumber, UnitHours, ProblemCatID,
CSRID, DateInitiated, Comments)
Case information

CaseProblemSubCategory (CaseNumber*, ProblemSubcatID*)
Relates Problem sub-categories with a case.

ProblemCategory (ProblemCatID*, ProblemCat)
Problem Categories.

ProblemSubCategory (ProblemSubcatID*, ProblemCatID, ProblemSubcat)
Problem sub-categories. related to Problem Categories via
ProblemCatID
When a certain problem has occurred enough times the CSR might write a
solution as to how to resolve the problem and assign it a solution number.
Then that number would be assigned to each respective case that contained
that specific problem.

I'm not sure how you would relate a solution to a problem since two
cases with the same main problem could have different sub-problems.
Have you thought about that yet?