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

That was my very first choice :@) - problem is though that I can't rely
on the length as some branches are complex, e.g. 1.1.23.210.1

I even created a query for each level, e.g. like *.* and not like *.*.*
but that also gave me problems :@(

Thanks anyway

Mark

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: 11 May 2004 20:31
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Too many self joins... Help - should be a simple
query


Hi Mark

How about

<SQL>

  Select
    Left(Code, 1) As L1,
    Left(Code, 3) As L2,
    Left(Code, 5) As L3,
    Left(Code, 7) As L4,
    Left(Code, 9) As L5
  From
    tblCodes;

</SQL>

It looks too simple, but according to your specs ...

/gustav


> 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...

> Any ideas ???

> Cheers

> Mark

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com