Table/Form Structure

[Rohn Everson]

I am creating a DB to collect issues/problems down to a root-cause. (We make
flatbed truckbeds) I envision it working like a drill down, if possible.

The user would select a dropdown list of DECK, BUMPER, CARGOBOX, CRANE,
CHASSIS, ect and then mouse over to get an interactive choice after
selecting BUMPER like; ELECTRICAL, PAINT, WELD, PURCHASED PART, ect then
mouse over to get another interactive choice after selecting PAINT like;
SCRATCH, RUN, LIGHT PAINT, WRONG COLOR, ect. you get the idea. Kind of
like when you go FORD.COM and configure a new truck you want to purchase but
I want to collect problems that can be analyzed.

Obviously, paint problems with decks, bumpers and cranes endup in the same
area but how it happens and where it was on the unit is significant in the
root-cause analysis for our quality department.

Ultimately, I need to easily populate the SQL table structure in a form that
is easy to use and also mine the data afterwords.

Any ideas? I am thinking multiple tables and forcing the user to select
from multiple dropdown boxes (because that is what I know) but I am sure
there is an easier way!!!!

Thanks,
Rohn

 

[Evan Keel]

Why don't you post the table structures you've come up so far? We can go
from there.

Evan

 

[peterV1]

I am creating a DB to collect issues/problems down to a root-cause. (We make
flatbed truckbeds) I envision it working like a drill down, if possible.

The user would select a dropdown list of DECK, BUMPER, CARGOBOX, CRANE,
CHASSIS, ect and then mouse over to get an interactive choice after
selecting BUMPER like; ELECTRICAL, PAINT, WELD, PURCHASED PART, ect then
mouse over to get another interactive choice after selecting PAINT like;
SCRATCH, RUN, LIGHT PAINT, WRONG COLOR, ect. you get the idea. Kind of
like when you go FORD.COM and configure a new truck you want to purchase but
I want to collect problems that can be analyzed.

Obviously, paint problems with decks, bumpers and cranes endup in the same
area but how it happens and where it was on the unit is significant in the
root-cause analysis for our quality department.

Ultimately, I need to easily populate the SQL table structure in a form that
is easy to use and also mine the data afterwords.

Any ideas? I am thinking multiple tables and forcing the user to select
from multiple dropdown boxes (because that is what I know) but I am sure
there is an easier way!!!!

Thanks,
Rohn

Creating a Bill of Material is a simple table of [Parent_Id],
[Child_Id] the terminating node where Child_Id=0
And then link the defect to the appropriate level in the BoM
(Descriptions and other level data are held in a separate table
Parent_Id, Parent_Desc,etc.)

 

[Rohn Everson]

I don't have a defined (new) structure yet. I have:

(T)CATEGORY
Category_ID
CategoryName

(T)CUSTOMERCONTACT
RowID
ContactRecordID
Name
City
State
Zip
Phone
UnitID
ContactDate
Status

(T)NOTES
RowID
NoteID
ContactRecordID
NoteDetails
NoteDate
CallRep
CategoryID
Status

(T)REPS
RowID
RepID
FName
LName
EmpID

(DB also has Queries, Forms, Reports, Macros, Modules)

Category table is only one-dimensional! We have been tracking this way for
years but would like to collect more meaningful data! Someone told me, what
I am looking for is called a configurator or Drill-Down but I just want to
get more details associated with each category, like I explained earlier in
the BUMPER example. Right now the specific details are hand typed in a
comment field which is not good for analysis. I would like to easily capture
another level or two of details.

Thanks for any advice you may have,
Rohn

 

[Rohn Everson]

Out of searching the last few days I am starting to think I need database
design help and cascading combo help.

either way HELP

thanks, Rohn

 

[Larry Linson]

Out of searching the last few days I am
starting to think I need database
design help and cascading combo help.

either way HELP

You might find a helpful downloadable example at http://accdevel.tripod.com/dwnindx.htm -- the one entitled "QueryByForm with Three Combo Boxes". It uses cascading combo boxes. I did the example with three, because many people found it difficult to extend the two-combo examples and would ask for additional help in the newsgroups.

Newsgroups are very good sources for help on specific problems, not so good for HELP pleas on general issues like "design". Rebecca Riordan's book "Designing Effective Database Systems" is a good resource; Steve Roman has also written a book on the subject that is well-received. On a lesser, and lighter, level, you could visit http://appdevissues.tripod.com/downloads.htm and take a look at my PowerPoint presentation entitled Practical Table Layout. If you encounter specific stumbling blocks in understanding, post those here, and it's likely you'll get specific assitance.

Larry Linson
Microsoft Office Access MVP