Continuous loop through recordset - help

[monrosal]

I need to loop through a recordset and grab a value while I'm looping.

First let me display an example table I'm using:
level levelAbove qty
------ ----------- ----
levelA none 1
levelB levelA 1
levelC levelB 2
levelD levelB 3

The "levelAbove" field is a field that stores the level above a specific level. So, levels levelC and levelD belong to levelB.

So let's use levelD. First I want to grab the qty of levelD. Next I look at levelD's levelAbove which is levelB and move to levelB's record then I grab that quantity which is 1. Then I search for the levelB's levelAbove which is levelA and move to that record and grab levelA' quantity which is 1. So it's like a continuous loop until I get to the top level.

Then I multiply the quantities 3 * 1 * 1.

Does anybody know how I can do this?

Thanks so much

Ramon

 

[WildHare]

I'd like to see you break this in to two tables, so you can lookup the values of the "LevelAbove"s quantity. Then you can do this whole operation in a query and forget about looping through a recordset.

Either that, or put this in a spreadsheet, which it may actually be more suited for.

Jim Hare
"Remember, you're unique - just like everyone else"

http://www.jmhare.com

 

[Francis]

I think I am doing a very similar thing with my software so I know it is possibe.
I know of no query that does it, but you can write a recursive subroutine that searches the recordset.
IE The routine calls itself. Note that this works no matter how many levels there are. You also have to be carefull not to get caught in an endless loop!
Something like (not tested, just to indicate the method)

dim total 'declared at module level

sub main
total =1
findParents('LevelD')
end sub

sub findParents(startwith)
'find the contents of startwith
rs.findfirst "level = " & startwith
total = total * rs.qty
if startwith= 'Level1' then'no more to do ,have reached the top
else
'recurse
findParents(rs!LevelAbove)
endif
end sub

 

[B827]

I don't know if this is what you had in mind but for a limited and reasonably small number of levels you could use a series of queries or SQL. For your example above this SQL gives the values for Levels C and D. By adding the Levels table you can go down another level.
For Three levels

SELECT Levels_2.Level, Levels.Quantity AS Q_TopLevel, Levels_1.Quantity AS Q_NextLevel1, Levels_2.Quantity AS Q_NextLevel2, [Q_TopLevel]*[Q_NextLevel1]*[Q_NextLevel2] AS Total
FROM Levels AS Levels_2 INNER JOIN (Levels AS Levels_1 INNER JOIN Levels ON Levels_1.[Level Above] = Levels.Level) ON Levels_2.[Level Above] = Levels_1.Level;

This may make the code much simpler if the number of levels is less than 6 or so.

Hope this helps look at the problem from a slightly different angle.

For Four Levels

SELECT Levels_3.Level, Levels.Quantity AS Q_TopLevel, Levels_1.Quantity AS Q_NextLevel1, Levels_2.Quantity AS Q_NextLevel2, Levels_3.Quantity AS Q_NextLevel3, [Q_TopLevel]*[Q_NextLevel1]*[Q_NextLevel2]*[Q_NextLevel3] AS Total
FROM Levels AS Levels_3 INNER JOIN (Levels AS Levels_2 INNER JOIN (Levels AS Levels_1 INNER JOIN Levels ON Levels_1.[Level Above] = Levels.Level) ON Levels_2.[Level Above] = Levels_1.Level) ON Levels_3.[Level Above] = Levels_2.Level;

 

[Francis]

That is another approach. Note however that it only works consistently if all the data has the same number of levels. And that it takes more text to define even four levels than the recursive method. If your data is more flexible (eg has more or less levels in some instances for example) then the SQL would be restrictive.
The Wonders of Recursion!
But I am not an SQL expert, so does anyone know if there is an SQL way of recursing like this?

 

[monrosal]

Thank you everybody for your replies. I haven't had a chance to work on it, but this is a great starting point.

Ramon