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

Sorry Gustav :@(

I had tried a lot of things... This morning I gave up and resorted to
using VBA to populate a table as required. It's actually (probbaly)
quicker as the table only gets updated when the code structure changes
and so, when running reports there is no need anymore to build the
structure...

Thanks again, and sorry for not being clearer... I had tried for most of
the afternoon and was trying to avoid sending a huge email...

Mark

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


Hi Mark

> 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 :@(

Well, why didn't you tell that initially?

Try this:

<SQL>

  Select
    Left(Code, InStr(Replace(Code, ".", "!", 1, 1 - 1) & ".", ".") - 1)
      As L1,
    Left(Code, InStr(Replace(Code, ".", "!", 1, 2 - 1) & ".", ".") - 1)
      As L2,
    Left(Code, InStr(Replace(Code, ".", "!", 1, 3 - 1) & ".", ".") - 1)
      As L3,
    Left(Code, InStr(Replace(Code, ".", "!", 1, 4 - 1) & ".", ".") - 1)
      As L4,
    Left(Code, InStr(Replace(Code, ".", "!", 1, 5 - 1) & ".", ".") - 1)
      As L5
  From
    tblCodes;

</SQL>

This assumes A2000+ or a substitute for Replace().

/gustav


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

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