Re: Runaway use of Nulls

Tech-Archive recommends: Fix windows errors by optimizing your registry

Once you limit the number of joins so that you can use simple queries rather
than VBA loops, you have effectively "flattened" the structure. Given that,
it is probably easier to simply store the ten fields so you don't need any
recursive code or 10-level joins. Just be aware that if you need 11 levels
in the future, you have a lot of changes to make.

If you use the self-referencing table, you are not storing the work
breakdown of the parent record, you are storing its primary key which should
be an autonumber. You need to do a 10-level join to obtain the entire work
breakdown structure because you need to gather the piece for each level and
concatenate them into a string.

"Paolo" <vainglory@xxxxxxxxx> wrote in message
news:1154779498.918036.260100@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi Jamie,

Thanks for following this thread. Yes I am aware that Access does not
have procedural techniques and that I will have to rely on VBA to
create an adjacency model. In tems of the nested set model, I have
also, misguidedly, already written it into my code. However, both
options seem to me to be denormalized at this point, the adjacency
model doubly so, in that the positioning information is already written
into the work breakdown structure number. So why create additional
fields to identify where the node is (either a parent field in the
adjacency model or lft, rgt fields in the nested set model)?

As far as I can see, the least denormalized way of doing this, is to
just parse the work breakdown structure number into separate fields (as
suggested by Pat), leaving some fields null as I will write the code to
house at least 10 levels and to use either SQL or VBA/DAO to identify
parent/child elements (I'm a little fuzzy on how I'm going to go about
doing it at the moment -- whether I should concatenate to the work
breakdown number or search the fields themselves).

Suggestions or comments gladly welcomed,

Paolo

paolo@xxxxxxxxxxxxxxxxxxxx

Jamie Collins wrote:
Paolo wrote:
If you are going to
perform recursion, how would you go about doing it?

With the adjacency list model, as suggested by Pat Hartman (noting it
is denormalized model), you have to (self) join as many times as you
*think* may be necessary. When you consider you cannot use a cursor or
other procedural techniques in a Access/Jet SQL procedure, nested sets
is an attractive option... I assume you have the book 'Trees and
Hierarchies in SQL for Smarties' by Joe Celko
(http://www.dbazine.com/ofinterest/oi-articles/celko24)?

Jamie.

--



.