Gustav Brock
gustav at cactus.dk
Wed May 12 05:34:37 CDT 2004
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...