You can do this either with one or two tables. Some developers like to use a 1-table approach with both Categories and all their subcategories. To get the list of Categories, do a SELECT DISTINCT on the table. You can also do two tables, and you'll save a bit of space if you use artificial keys - but I generally advise against that. A 2-table approach might look like:
tblCategories: CategoryID (P), CategoryDescription tblSubCategories: CategoryID (P1), SubCategoryID (P2), SubCategoryDescription If you avoid artificial keys, a 2-table design looks like: tblCategories: CategoryDescription (P) tblSubCategories: CategoryDescription (P1), SubCategoryDescription (P2) But if you think about it, the tblSubCategories using non-artificial keys alone will suffice. As I noted in the beginning, SELECT DISTINCT CategoryDescription gets you a list of the categories. The only downside is a user might end up creating a new category by mistake by slightly misspelling it. Have fun... John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of karasmith1988 Sent: Friday, January 27, 2006 10:43 PM To: [email protected] Subject: [ms_access] "Categories" table - how to cater for subcategories? Hi all, I need to set up a table that will allow me to enter products into certain categories, and within those categories, further subcategories. However, I don't want cateogories to be "duplicated" to allow for subcategories. For example, I don't want: Clocks & Watches>Mantle Clocks -and- Clocks & Watches>Pocket Watches I don't know if I have explained myself very well, but I hope somebody can make sense of what I am trying to say! What would be the best approach for this? Should I create both a "Categories" table and a "Subcategories" table? My only concern was that doing this would involve creating a new tale each time a new subcategory was established - not very practial! Yahoo! Groups Links Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
