Hi John, Thanks very much for your reply - I will give this a go!
Cheers --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > 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/
