Loop a Query and Stop when it reaches a specified value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with 2 million records. [Partnr] and [nha] are the fields I
am working with. [nha] is the next higher assembly to the [partnr].

What I need to do is build a query that will take a [partnr] and find a
match in [nhr] and then find a match in [partnr] and then find a match in
[nha] and so on and so on, until it reaches a specific value.

Can anyone help?
 
That is recursive. It can be done in MS SQL Server 2007, but not with
Jet.... natively. You can surely use a Nested Set construction, of more
simply loop, in VBA. I assume you are proficient in VBA. I just 'confirm'
that while it can be done with Jet, you need much more initial work than by
using only VBA. Sure, if the number of 'nesting' (iteration) is very very
low, say n times, then you can do it with a query where the table is joined
to itself 'n-1' times.


Vanderghast, Access MVP
 
Back
Top