hierarchical fields - best way to define?

[Philos]

I would like to represent a variable depth and width
hierarchical structure within an MSACCESS DB what is the
best way to do this? For example, the hierarchy may
represent the following product categories:

Supplier/
Supplier/Accessories
Supplier/Accessories/Desktop Computers
Supplier/Accessories/Pocket PCs
Supplier/Accessories/Desktop Computers/Mouse
Supplier/Accessories/Pocket PCs/Cables
Supplier/Accessories/Desktop Computers/Mouse/Wireless
Supplier/Accessories/Pocket PCs/Cables/Serial
....

Also what would a SELECT query look like to show all
items at a particular level?

Thanks

 

[Adrian Jansen]

Look at:

Self Joins: tables that look themselves up (Pedigrees example)
at:
http://users.bigpond.net.au/abrowne1/ser-06.html

See alsp Joe Celko's articles on SQL trees, e.g.:
http://www.mvps.org/access/modules/mdl0027.htm
http://www.intelligententerprise.com/001020/celko.shtml
http://www.devx.com/premier/mgznarch/vbpj/2001/07jul01/sqlpro0107/rein0107/
rein-1.asp


--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control