Repost...Newbie needs help with SQL or DAO problem

P

Paolo

Hi I've been banging my head with this for some time.

I'm converting Joe Celko's nested sets into Access, and I have it
working, but it's so inelegant, I'm convinced there's got to be a
better solution. Basically a nested set is a hierarchical
representation for relational databases. There's not much need to
understand other than each record has two field named 'left' and
'right' which determine where the record falls in a hierarchy and that
Element ID is a primary key. Let's say I have 3 fields in tblElements:



ElementID Left Right


The top record alone would look like this:


Parent 1 2


If I were to add a child record, it would look like this:


Parent 1 4
Child 2 3


In SQL to add a record I can do this:


---------------------------------------------------------------------------­--------------------------

1) SELECT Left, Right
FROM tblElements
INTO tblIsolateElementAdd
WHERE ElementID= 'parent element as determined by the value of some
form control'


2) UPDATE tblElements, tblIsolateElementAdd
SET tblElements.Left = tblElements.Left+2
WHERE tblElements.Left>tblIsolateElementAdd.Right


3) UPDATE tblElements, tblIsolateElementAdd
SET tblElements.Right = tblElements.Right+2
WHERE tblElements.Right >= tblIsolateElementAdd.Right


4) UPDATE tblIsolateElementAdd
SET ElementID ='value of some control',
=
,
=
+ 1,
=
+ 1,


5) INSERT INTO tblElements (ElementID,
,
)
SELECT ElementID,
,

FROM tblIsolateElementAdd


6) DROP TABLE tblIsolateElementAdd
---------------------------------------------------------------------------­---------------

As you can see I am using Cartesian joins and well as creating a
temporary table. Indeed, I could have created a one record recordset
(snapshot) using DAO to do avoid the cartesian joins and the temporary
table (pseudocode):
---------------------------------------------------------------------------­-----------------

Set rs = Db.OpenRecordset("SELECT Left, Right FROM " & _
"tblElements WHERE ElementID ='" & parent element as determined

by the value of
some form control & "'", dbOpenSnapshot)


UPDATE tblElements
SET
=
+2
WHERE
>rs!Left


UPDATE tblElements
SET
=
+2
WHERE
>= rs!Right


INSERT INTO tblElements (ElementID,
,
)
VALUES 'value of some control', rs!Right, rs!Right+1
---------------------------------------------------------------------------­---------------------

Is the latter solution the way to go? Or is there a simpler solution?
What about avoiding the SQL altogether and just using DAO? Does that
sound like a better way to go?


Thanks in advance,


Paolo


(e-mail address removed), eliminating NOSPAM​
 
D

Douglas J. Steele

Sorry, haven't taken that close a look at what you're doing, but thought I'd
point out that there's a sample of Joe's Nested Sets in Access at
http://www.mvps.org/access/queries/qry0023.htm at "The Access Web". You
might take a look at it to get some ideas.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi I've been banging my head with this for some time.

I'm converting Joe Celko's nested sets into Access, and I have it
working, but it's so inelegant, I'm convinced there's got to be a
better solution. Basically a nested set is a hierarchical
representation for relational databases. There's not much need to
understand other than each record has two field named 'left' and
'right' which determine where the record falls in a hierarchy and that
Element ID is a primary key. Let's say I have 3 fields in tblElements:



ElementID Left Right


The top record alone would look like this:


Parent 1 2


If I were to add a child record, it would look like this:


Parent 1 4
Child 2 3


In SQL to add a record I can do this:


---------------------------------------------------------------------------­--------------------------

1) SELECT Left, Right
FROM tblElements
INTO tblIsolateElementAdd
WHERE ElementID= 'parent element as determined by the value of some
form control'


2) UPDATE tblElements, tblIsolateElementAdd
SET tblElements.Left = tblElements.Left+2
WHERE tblElements.Left>tblIsolateElementAdd.Right


3) UPDATE tblElements, tblIsolateElementAdd
SET tblElements.Right = tblElements.Right+2
WHERE tblElements.Right >= tblIsolateElementAdd.Right


4) UPDATE tblIsolateElementAdd
SET ElementID ='value of some control',
=
,
=
+ 1,
=
+ 1,


5) INSERT INTO tblElements (ElementID,
,
)
SELECT ElementID,
,

FROM tblIsolateElementAdd


6) DROP TABLE tblIsolateElementAdd
---------------------------------------------------------------------------­---------------

As you can see I am using Cartesian joins and well as creating a
temporary table. Indeed, I could have created a one record recordset
(snapshot) using DAO to do avoid the cartesian joins and the temporary
table (pseudocode):
---------------------------------------------------------------------------­-----------------

Set rs = Db.OpenRecordset("SELECT Left, Right FROM " & _
"tblElements WHERE ElementID ='" & parent element as determined

by the value of
some form control & "'", dbOpenSnapshot)


UPDATE tblElements
SET
=
+2
WHERE
>rs!Left


UPDATE tblElements
SET
=
+2
WHERE
>= rs!Right


INSERT INTO tblElements (ElementID,
,
)
VALUES 'value of some control', rs!Right, rs!Right+1
---------------------------------------------------------------------------­---------------------

Is the latter solution the way to go? Or is there a simpler solution?
What about avoiding the SQL altogether and just using DAO? Does that
sound like a better way to go?


Thanks in advance,


Paolo


(e-mail address removed), eliminating NOSPAM​
 
P

Paolo

Hi Doug.

Yes I've looked at that solution. It's pretty brilliant, but does not
provide a solution for the addition or deletion of a node which is
basically my problem.

Paolo

(e-mail address removed), eliminating NOSPAM
Sorry, haven't taken that close a look at what you're doing, but thought I'd
point out that there's a sample of Joe's Nested Sets in Access at
http://www.mvps.org/access/queries/qry0023.htm at "The Access Web". You
might take a look at it to get some ideas.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi I've been banging my head with this for some time.

I'm converting Joe Celko's nested sets into Access, and I have it
working, but it's so inelegant, I'm convinced there's got to be a
better solution. Basically a nested set is a hierarchical
representation for relational databases. There's not much need to
understand other than each record has two field named 'left' and
'right' which determine where the record falls in a hierarchy and that
Element ID is a primary key. Let's say I have 3 fields in tblElements:



ElementID Left Right


The top record alone would look like this:


Parent 1 2


If I were to add a child record, it would look like this:


Parent 1 4
Child 2 3


In SQL to add a record I can do this:


---------------------------------------------------------------------------­--------------------------

1) SELECT Left, Right
FROM tblElements
INTO tblIsolateElementAdd
WHERE ElementID= 'parent element as determined by the value of some
form control'


2) UPDATE tblElements, tblIsolateElementAdd
SET tblElements.Left = tblElements.Left+2
WHERE tblElements.Left>tblIsolateElementAdd.Right


3) UPDATE tblElements, tblIsolateElementAdd
SET tblElements.Right = tblElements.Right+2
WHERE tblElements.Right >= tblIsolateElementAdd.Right


4) UPDATE tblIsolateElementAdd
SET ElementID ='value of some control',
=
,
=
+ 1,
=
+ 1,


5) INSERT INTO tblElements (ElementID,
,
)
SELECT ElementID,
,

FROM tblIsolateElementAdd


6) DROP TABLE tblIsolateElementAdd
---------------------------------------------------------------------------­---------------

As you can see I am using Cartesian joins and well as creating a
temporary table. Indeed, I could have created a one record recordset
(snapshot) using DAO to do avoid the cartesian joins and the temporary
table (pseudocode):
---------------------------------------------------------------------------­-----------------

Set rs = Db.OpenRecordset("SELECT Left, Right FROM " & _
"tblElements WHERE ElementID ='" & parent element as determined

by the value of
some form control & "'", dbOpenSnapshot)


UPDATE tblElements
SET
=
+2
WHERE
>rs!Left


UPDATE tblElements
SET
=
+2
WHERE
>= rs!Right


INSERT INTO tblElements (ElementID,
,
)
VALUES 'value of some control', rs!Right, rs!Right+1
---------------------------------------------------------------------------­---------------------

Is the latter solution the way to go? Or is there a simpler solution?
What about avoiding the SQL altogether and just using DAO? Does that
sound like a better way to go?


Thanks in advance,


Paolo


(e-mail address removed), eliminating NOSPAM​
Click to expand...
​
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Joe Celko's Nested sets 1
Cartesian join versus DAO? 3
Need Help with adding a specific record or with DSUM 1
MS Access setting outlook reminders from a DAO recordset 0
Help with code 5
Subform with one to many relationship 2
Data Type Mismatch - String versus Long 3
Concatenate hierarchy 1

Top