Need Input on Database Design

[PaulSq]

I'm creating a database basically for large amouts of text information. I'm
breaking this information into 4 different variable categories, such that the
information can be accessed utilizing 4 pull down menus. (example: selecting
type of automaker, will lead to a selection of car models, which will lead to
a selection of statistical data for the car model, and then the actual data
text field). I would like to utilize pull down menus, because it will be the
easiest way to view the different types of data categories, and to ultimately
access the data in a simple fashion (step by step process).

I need input as to how I can accomplish this (conditional variables that are
linked to each other?). Any example databases?

Any help would be GREATLY appreciated!!

Thank you.

 

[Duane Hookom]

I think you are interested in creating cascading combo boxes. Roger Carlson
has a small sample database of this at
http://www.rogersaccesslibrary.com/TableOfContents3.asp#C.

 

[Stella]

Duane, your link is really helpful; but, as a newbie designing this database,
I am not sure how to set it up.

What is the best way to design the tables to set up the cascading combo box?

The table(s) contain memo fields. Will the form populate the specific table
from where the form pulls the data?

I apologize for these basic questions. Thanks for your help.





Thanks for your help.

 

[Duane Hookom]

You need to re-state your specifications. How about providing some table and
field names with data types. Then tell us what you would like the user to be
able to do and the expected results.

 

[Stella]

Example 4 on http://www.fontstuff.com/access/acctut10.htm is exactly what we
are trying to accomplish.

We are working with the following fields: Solution, Category, Subcategory,
and Class. The solution brings up the category and the category only brings
up the sub categories associated with the category. I'm not sure how to
design the table layout to accomplish this task.

Thank you so much for all your help, Duane. I appreciate your patience very
much.

 

[Duane Hookom]

The solution from Martin Green's web page uses a lookup table. Do you have a
lookup table or tables?
Does a Class value depend on only one Subcategory and a Subcategory depend
on only one Category?

 

[Stella]

Hi Duane,

I found a great example using a single table, form, and subform at
http://www.candace-tripp.com/pages/access_downloads.aspx entitled Cascading
Combo Boxes 2K.

The table serves as the lookup table; the initial category depends on a
subcategory, refining the dataview in the subform:

Category1->Category2->Category3->Category4

I've changed the programming to reflect our data, but am still working
through the coding process and haven't quite gotten our form to work yet.

Thanks again for all your patience and any suggestions.

 

[David M C]

You'll need four tables:

tblSolution:

SolutionID (pk)
SolutionName

tblCategory:

SolutionID (fk)
CategoryID (pk)
CategoryName

tblSubCategory:

CategoryID (fk)
SubCategoryID (pk)
SubCategoryName

tblClass:

SubCategoryID (fk)
ClassID (pk)
ClassName
etc. etc. etc.

You have a 1:n relationship between each.

Now have four comboboxes on your form Combo1, Combo2, Combo3, Combo4. These
comboboxes are unbound.

Combo1 rowsource will be:

SELECT SolutionID, SolutionName FROM tblSolution.

Combo2 rowsource will be:

SELECT CategoryID, CategoryName FROM tblCatgeory WHERE SolutionID =
[Forms]![YourFormName]![Combo1]

Combo3 rowsource will be:

SELECT SubCategoryID, SubCategoryName FROM tblSubCategory WHERE CategoryID =
[Forms]![YourFormName]![Combo2]

Combo4 rowsource will be:

SELECT ClassID, ClassName FROM tblClass WHERE SubCategoryID =
[Forms]![YourFormName]![Combo3]

In the AfterUpdate event of each combo you will requery the next combo.

ie Combo1's AfterUpdate Event will have:

[Forms]![YourFormName]![Combo2].Requery

etc.

Hope that helps.

Dave

 

[Stella]

David,

Many thanks. I'm still quite a newbie with the coding, so your instructions
are extremely helpful. Thanks, too, to Duane. I can't tell you how much you
helped us out.

Have a good weekend.

Stella

You'll need four tables:

tblSolution:

SolutionID (pk)
SolutionName

tblCategory:

SolutionID (fk)
CategoryID (pk)
CategoryName

tblSubCategory:

CategoryID (fk)
SubCategoryID (pk)
SubCategoryName

tblClass:

SubCategoryID (fk)
ClassID (pk)
ClassName
etc. etc. etc.

You have a 1:n relationship between each.

Now have four comboboxes on your form Combo1, Combo2, Combo3, Combo4. These
comboboxes are unbound.

Combo1 rowsource will be:

SELECT SolutionID, SolutionName FROM tblSolution.

Combo2 rowsource will be:

SELECT CategoryID, CategoryName FROM tblCatgeory WHERE SolutionID =
[Forms]![YourFormName]![Combo1]

Combo3 rowsource will be:

SELECT SubCategoryID, SubCategoryName FROM tblSubCategory WHERE CategoryID =
[Forms]![YourFormName]![Combo2]

Combo4 rowsource will be:

SELECT ClassID, ClassName FROM tblClass WHERE SubCategoryID =
[Forms]![YourFormName]![Combo3]

In the AfterUpdate event of each combo you will requery the next combo.

ie Combo1's AfterUpdate Event will have:

[Forms]![YourFormName]![Combo2].Requery

etc.

Hope that helps.

Dave

Hi Duane,

I found a great example using a single table, form, and subform at
http://www.candace-tripp.com/pages/access_downloads.aspx entitled Cascading
Combo Boxes 2K.

The table serves as the lookup table; the initial category depends on a
subcategory, refining the dataview in the subform:

Category1->Category2->Category3->Category4

I've changed the programming to reflect our data, but am still working
through the coding process and haven't quite gotten our form to work yet.

Thanks again for all your patience and any suggestions.