[AccessD] Too many self joins... Help - should be a simple query

> Sorry if this has been done before but my heads killing and I can't 
> figure it at all. Using AXP etc. I have a list of codes, some of which

> are parents and some children (going down 5 levels). What I want is 5 
> columns (1 for each level), each containing not only the codes for 
> that level, but all the codes from the higher levels...

> E.g.

>         Code            Parent
>         1
>         1.1             1
>         1.2             1
>         1.3             1
>         1.2.1           1.2
>         1.2.2           1.2
>         1.1.1           1.1
>         1.1.1.1 1.1.1
>         1.1.1.1.1       1.1.1.1

> Etc. becomes something like

>         L1      L2      L3      L4      L5
>         1       1       1       1       1
>         1       1.1     1.1     1.1     1.1
>         1       1.2     1.2.1   1.2.1   1.2.1

> > This is for dumping into a pivot in Excel and so I can roll totals up 
> > to selected levels, i.e. each record has a column for each level etc. 
> > Some code structures don't go down 5 levels but I still need to show a
> 
> > value in level 5 etc.
> 
> > The problem is column 5 must be unique but in some cases (i.e.) where 
> > the codes are less than 5 levels deep) I get duplicates.
> 
> > Sorry if all this sounds a bit odd... It is :@) I am trying to avoid 
> > temp tables and code as I'm trying to keep it all data driven as
> > (hopefully) this is going to fit an existing reporting tool which is 
> > driven primarily by saved SQL strings...
> 

Use a function to extract the code segments?

Function LevelCode(Code As String, Position As Long) As Double
'return double rather than string so that sorting works
    Position = Position - 1 'Split returns 0 based array
    Dim strSegment() As String
    strSegment = Split(Code, ".")
    If Position > UBound(strSegment) Then
            Position = UBound(strSegment)
    End If
    LevelCode = CDbl(strSegment(Position))
End Function


Then you can: 

Select LevelCode(Code,1) as Level1, LevelCode(Code,2) as Level2.....
   


 
-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System 
Support.