MarkH
lists at theopg.com
Tue May 11 16:00:09 CDT 2004
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