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

MarkH lists at theopg.com
Tue May 11 13:48:12 CDT 2004


Hello folks...

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




More information about the AccessD mailing list