Subcategory lookup tables

[Richard Coutts]

I want to create a table where the first field is a category, the
second field is a subcategory to the first field, and the third field
is a subcategory to the second. E.g., the category table might look
like this:

Toys
Jewelry
Sporting Goods

The second field lookup table might be:

Toys Board Games
Toys Video Games
Jewelry Watches
Jewelry Rings
Sporting Goods Baseball Gloves

And the third field table might be:

Board Games Monopoly
Board Games Risk
Board Games Othello
Video Games The Sims
Video Games Madden NFL
Watches Seiko Chrono
Watches Timex Ironman

So, if the user entered [Toys], the second field would be a lookup of
[Board Games / Video Games]. Likewise, entering [Video Games] would
give a lookup of [The Sims / Madden NFL] for the third field.

What's the best way to do this? I'd like to have a Datasheet format.
I've thought of a couple designs, but my designs seemed like hacks,
with VBA queries writing to temporary scratch tables based on the
contents of the fields. I'm sure there's a more elegant approach. If
someone could pass it along it would be very much appreciated.

Thanks,
Rich

[Don Leverton]

Hi Richard,
IMHO, this hould be done using 3 related tables.

tblCategories
CatID - AutoNumber - PK
Category - Text
.... other fields

tblSubCategories
SubID - AutoNumber - PK
CatID - Long Integer - FK
SubCategory - Text
.... other fields

tblItems
ItemID - AutoNumber- PK
SubID - Long Integer - FK
Item - Text
ItemDetails - Memo?
.... other fields

Relationships
1 ==>M Categories to SubCategories
1 ==> M SubCatagories to Items

Viewing / entering of data would be via a maniform / dual subform
frmCategories
sbfSubCategories
sbfItems
--
HTH,
Don
=============================
E-Mail (if you must)
My*****@Telus.net

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================

"Richard Coutts" <rc*****@comcast.net> wrote in message
news:70**************************@posting.google.c om...

I want to create a table where the first field is a category, the
second field is a subcategory to the first field, and the third field
is a subcategory to the second. E.g., the category table might look
like this:

Toys
Jewelry
Sporting Goods

The second field lookup table might be:

Toys Board Games
Toys Video Games
Jewelry Watches
Jewelry Rings
Sporting Goods Baseball Gloves

And the third field table might be:

Board Games Monopoly
Board Games Risk
Board Games Othello
Video Games The Sims
Video Games Madden NFL
Watches Seiko Chrono
Watches Timex Ironman

So, if the user entered [Toys], the second field would be a lookup of
[Board Games / Video Games]. Likewise, entering [Video Games] would
give a lookup of [The Sims / Madden NFL] for the third field.

What's the best way to do this? I'd like to have a Datasheet format.
I've thought of a couple designs, but my designs seemed like hacks,
with VBA queries writing to temporary scratch tables based on the
contents of the fields. I'm sure there's a more elegant approach. If
someone could pass it along it would be very much appreciated.

Thanks,
Rich

[Pieter Linden]

Rich,

start here...
http://www.mvps.org/access/forms/frm0028.htm

then adapt as you get each piece working.

[Richard Coutts]

> start here...

http://www.mvps.org/access/forms/frm0028.htm

Thanks for the tip. I created a saved Query that populates the
contents of second combobox list by executing a Requery in the
AfterUpdate event of the first combobox. It worked great except for a
hitch -- because I have a datasheet, I'm winding up with all records
being modified when the AfterUpdate Requery executes, when I only want
the current record to be modified.

E.g. If my list is

Board Games Monopoly
Watches Seiko Chrono
Board Games Othello
Watches Timex Ironman

And AfterUpdate Requery fires when I edit the first line, the table
ends up looking like:

Board Games Monopoly
Watches
Board Games Othello
Watches

Likewise, if I edit the second line, the AfterUpdate Requery fires for
all records and I get

Board Games
Watches Seiko Chrono
Board Games
Watches Timex Ironman

Is there a way to keep the Query local to the current record?

Thanks,
Rich