Home  |   French  |   About  |   Search  | mvps.org  

What's New
Table Of Contents
Credits
Netiquette
10 Commandments 
Bugs
Tables
Queries
Forms
Reports
Modules
APIs
Strings
Date/Time
General
Downloads
Resources
Search
Feedback
mvps.org

In Memoriam

Terms of Use


VB Petition

Queries: BOM, with Joe Celko Nested Sets

Author(s)
Michel Walsh

As presented in "Joe Celko's SQL for smarties" and discussed in many newsgroups, the nested set solutions are up to 1000 times faster than their equivalent methods (mainly based on cursors or recordsets). 

While the standard discussions turn around maintenance (adding and deletion of nodes in the "graph"), there was no example about the BOM problem based on that kind of solutions, at least, up to now, since now, you can find one, in the Jet-Access 2000 zipped database included here. That database has one table, the nested set (we assume you are familar with the notion), a single form showing graphically the  tree represented in the table, and one query returning the list of the required elements, and in which quantity, to make an arbitrary item described in the nested set.

As you can see by yourself, there is no VBA, no recursion, only plain SQL is used to solve that kind of problem. The query can surely be re-used for any nested-set, not just for the one given as example.

Celko-BOM.zip
(Access 2000 database)

1998-2010, Dev Ashish & Arvin Meyer, All rights reserved. Optimized for Microsoft Internet Explorer