Help with SELECT

G

Gary Walter

lex parsimoniae

Occam's razor --
All things being equal, the simplest solution
tends to be the best one.

Bamar's razor --
All things that are too complicated to understand easily
must be wrong.
 
K

Kozaw

lex parsimoniae

Occam's razor --
All things being equal, the simplest solution
tends to be the best one.

Bamar's razor --
All things that are too complicated to understand easily
must be wrong.

Bamar said:
Hi every body!,
Click to expand...
All are going with more and more complicated. Gone out the range of my
understanding.
Perhaps, vovan just want sorting with parentAccountID and then
accountID in background, and hide the sorting column.
This should be just the sql, run check at your end. If vovan's maximum
AccountID and parentAccountID is 99, format should 00, or is 999,
format should be 000. Thalt's all.
Click to expand...
SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY Format([Account]![ParentAccountID],"00") & Format([Account]!
[AccountID],"00");
Click to expand...
dear vovan, pleased respond your result.
_________________________________
Click to expand...
Click to expand...

Sorry. Don't misunderstand on me.
Should Simple coding with same result be the goal?
___________________________________________
 
G

Gary Walter

_________________________________
Sorry. Don't misunderstand on me.
Should Simple coding with same result be the goal?
___________________________________________
Click to expand...
I apologise for being a little snarky, but I
do not believe you will get "same result."

I believe vovan should have given us some
sample data like:

AccountID AccountName ParentAccountID
1 Checking 0
2 Savings 0
3 Expenses 0
4 Fixed Assets 0
5 Travel 3
6 Computers 4
7 Equipment 4
8 Printers 7
9 Fax 7
10 Laser 8
11 InkJet 8
12 Rent 3
13 MainOffice 12
14 BranchOffice 12
15 Copiers 7

Save the above to a table Account
and run your query to see how it works.

This is what I think vovan wants (I could
be wrong):

{accts are indented to show nesting}

AccountID AccountName ParentAccountID
1 Checking 0
2 Savings 0
3 Expenses 0
5 Travel 3
12 Rent 3
13 MainOffice 12
14 BranchOffice 12
4 Fixed Assets 0
6 Computers 4
7 Equipment 4
8 Printers 7
10 Laser 8
11 InkJet 8
9 Fax 7
15 Copiers 7

If we knew *for sure* there was only
this deep level of nesting, we could just
use 4 instances of table to sort, i.e.,

{formatting assumes AccountID<=9999}

SELECT
Parent.AccountID,
Parent.AccountName,
Parent.ParentAccountID
FROM
((Account AS Parent
LEFT JOIN
Account AS Child
ON
Parent.ParentAccountID = Child.AccountID)
LEFT JOIN
Account AS GrandChild
ON
Child.ParentAccountID = GrandChild.AccountID)
LEFT JOIN
Account AS GreatGrandChild
ON
GrandChild.ParentAccountID = GreatGrandChild.AccountID
ORDER BY
Format([GreatGrandChild].[ParentAccountID],"0000")
&
Format([GrandChild].[ParentAccountID],"0000")
&
Format([Child].[ParentAccountID],"0000")
&
Format([Parent].[ParentAccountID],"0000")
&
Format([Parent].[AccountID],"0000");

What if there is one more level of nesting?
Just add another "GreatGreatGrandChild"
instance of the table?

Usually it best not to assume level of nesting
and go for a more general approach.

Here be more step-by-step so maybe you can
follow along and understand Volk's method
better (I'm going to assume you have a test db
with the Account table and data above -- if not,
then how do you learn?)

create a table (say "tblOrg")

ID Text(10) pk
Parent Text(10) (allow Null, i.e., Required = No)
AccountName Text(255)
Depth Long (allow Null)
Lineage Text(255) (allow Null)

to easily create it, just run following query:

CREATE TABLE tblOrg
(ID TEXT(10) NOT NULL CONSTRAINT PK_ID PRIMARY KEY,
Parent TEXT(10),
AccountName TEXT(255),
Depth Long,
Lineage TEXT(255));

Create an append query (say "qryGetData")
that gets AccountID, ParentAccountID, and AccountName from your
Account table and fills tblOrg, but converts ID and
Parent to "0 justified" text in the process.

qryGetData:

INSERT INTO tblOrg ( ID, AccountName, Parent )
SELECT
Format([AccountID],"0000000000") AS ID,
Account.AccountName,
Format([ParentAccountID],"0000000000") AS Parent
FROM Account;

tblOrg should then look like:

ID Parent AccountName Depth Lineage
0000000001 0000000000 Checking
0000000002 0000000000 Savings
0000000003 0000000000 Expenses
0000000004 0000000000 Fixed Assets
0000000005 0000000003 Travel
0000000006 0000000004 Computers
0000000007 0000000004 Equipment
0000000008 0000000007 Printers
0000000009 0000000007 Fax
0000000010 0000000008 Laser
0000000011 0000000008 InkJet
0000000012 0000000003 Rent
0000000013 0000000012 MainOffice
0000000014 0000000012 BranchOffice
0000000015 0000000007 Copiers


then create query to add "root"

qryAddRoot:

INSERT INTO tblOrg (ID, Parent, AccountName, Depth,Lineage)
VALUES ('0000000000', Null, 'ROOT',0,'/')

tblOrg should then look like:

ID Parent AccountName Depth Lineage
0000000000 ROOT 0 /
0000000001 0000000000 Checking
0000000002 0000000000 Savings
0000000003 0000000000 Expenses
0000000004 0000000000 Fixed Assets
0000000005 0000000003 Travel
0000000006 0000000004 Computers
0000000007 0000000004 Equipment
0000000008 0000000007 Printers
0000000009 0000000007 Fax
0000000010 0000000008 Laser
0000000011 0000000008 InkJet
0000000012 0000000003 Rent
0000000013 0000000012 MainOffice
0000000014 0000000012 BranchOffice
0000000015 0000000007 Copiers

then create following query...

qryOnePass:

UPDATE tblOrg AS T INNER JOIN tblOrg AS P
ON T.Parent = P.ID
SET T.Depth = [P].[Depth]+1,
T.Lineage = [P].[Lineage] & [T].[Parent] & '/'
WHERE
(((P.Depth)>=0)
AND
((P.Lineage) Is Not Null)
AND
((T.Depth) Is Null));

the first time you run it, it will update 4 rows:

ID Parent AccountName Depth Lineage
0000000000 ROOT 0 /
0000000001 0000000000 Checking 1 /0000000000/
0000000002 0000000000 Savings 1 /0000000000/
0000000003 0000000000 Expenses 1 /0000000000/
0000000004 0000000000 Fixed Assets 1 /0000000000/
0000000005 0000000003 Travel
0000000006 0000000004 Computers
0000000007 0000000004 Equipment
0000000008 0000000007 Printers
0000000009 0000000007 Fax
0000000010 0000000008 Laser
0000000011 0000000008 InkJet
0000000012 0000000003 Rent
0000000013 0000000012 MainOffice
0000000014 0000000012 BranchOffice
0000000015 0000000007 Copiers

the next time you run it, it will update 4 more rows:

ID Parent AccountName Depth Lineage
0000000000 ROOT 0 /
0000000001 0000000000 Checking 1 /0000000000/
0000000002 0000000000 Savings 1 /0000000000/
0000000003 0000000000 Expense 1 /0000000000/
0000000004 0000000000 Fixed Assets 1 /0000000000/
0000000005 0000000003 Travel 2 /0000000000/0000000003/
0000000006 0000000004 Computers 2 /0000000000/0000000004/
0000000007 0000000004 Equipment 2 /0000000000/0000000004/
0000000008 0000000007 Printers
0000000009 0000000007 Fax
0000000010 0000000008 Laser
0000000011 0000000008 InkJet
0000000012 0000000003 Rent 2 /0000000000/0000000003/
0000000013 0000000012 MainOffice
0000000014 0000000012 BranchOffice
0000000015 0000000007 Copiers

if you continue to run query until no more rows
are updated, the last 3 fields should look like:

AccountName Depth Lineage
ROOT 0 /
Checking 1 /0000000000/
Savings 1 /0000000000/
Expenses 1 /0000000000/
Fixed Assets 1 /0000000000/
Travel 2 /0000000000/0000000003/
Computers 2 /0000000000/0000000004/
Equipment 2 /0000000000/0000000004/
Printers 3 /0000000000/0000000004/0000000007/
Fax 3 /0000000000/0000000004/0000000007/
Laser 4 /0000000000/0000000004/0000000007/0000000008/
InkJet 4 /0000000000/0000000004/0000000007/0000000008/
Rent 2 /0000000000/0000000003/
MainOffice 3 /0000000000/0000000003/0000000012/
BranchOffice 3 /0000000000/0000000003/0000000012/
Copiers 3 /0000000000/0000000004/0000000007/

qryIndentedList:

SELECT
Space([T].[Depth]*4) & [AccountName] AS Acct
FROM tblOrg AS T
ORDER BY
[Lineage] & [ID];

gives:

ROOT
Checking
Savings
Expenses
Travel
Rent
MainOffice
BranchOffice
Fixed Assets
Computers
Equipment
Printers
Laser
InkJet
Fax
Copiers

or what I thought vovan wanted:

SELECT
CLng([ID]) AS AccountID,
tblOrg.AccountName,
CLng([Parent]) AS ParentAccountID
FROM tblOrg
WHERE
tblOrg.AccountName <>'ROOT'
ORDER BY [Lineage] & [ID];

gives:

{indenting added in post}

AccountID AccountName ParentAccountID
1 Checking 0
2 Savings 0
3 Expenses 0
5 Travel 3
12 Rent 3
13 MainOffice 12
14 BranchOffice 12
4 Fixed Assets 0
6 Computers 4
7 Equipment 4
8 Printers 7
10 Laser 8
11 InkJet 8
9 Fax 7
15 Copiers 7

this method also lends itself to looking
at data in other ways, for example

"subordinates"

what are the subordinate accounts of
Expenses ("0000000003")?

SELECT
CLng([ID]) AS AccountID,
tblOrg.AccountName,
CLng([Parent]) AS ParentAccountID,
tblOrg.Depth
FROM tblOrg
WHERE
tblOrg.Lineage Like '*0000000003*'
ORDER BY [Lineage] & [ID];

Hopefully you can better understand now?

Remember there are other principles besides Occam's Razor...

2 forms of Karl Menger's Law Against Miserliness

"Entities must not be reduced to the point of inadequacy"

and

"It is vain to do with fewer what requires more"

or Kant's "counter-razor":

"The variety of beings should not rashly be diminished."

good luck,

gary
 
K

Kozaw

Sorry. Don't misunderstand on me.
Should Simple coding with same result be the goal?
___________________________________________
Click to expand...

I apologise for being a little snarky, but I
do not believe you will get "same result."

I believe vovan should have given us some
sample data like:

AccountID AccountName ParentAccountID
1 Checking 0
2 Savings 0
3 Expenses 0
4 Fixed Assets 0
5 Travel 3
6 Computers 4
7 Equipment 4
8 Printers 7
9 Fax 7
10 Laser 8
11 InkJet 8
12 Rent 3
13 MainOffice 12
14 BranchOffice 12
15 Copiers 7

Save the above to a table Account
and run your query to see how it works.

This is what I think vovan wants (I could
be wrong):

{accts are indented to show nesting}

AccountID AccountName ParentAccountID
1 Checking 0
2 Savings 0
3 Expenses 0
5 Travel 3
12 Rent 3
13 MainOffice 12
14 BranchOffice 12
4 Fixed Assets 0
6 Computers 4
7 Equipment 4
8 Printers 7
10 Laser 8
11 InkJet 8
9 Fax 7
15 Copiers 7

If we knew *for sure* there was only
this deep level of nesting, we could just
use 4 instances of table to sort, i.e.,

{formatting assumes AccountID<=9999}

SELECT
Parent.AccountID,
Parent.AccountName,
Parent.ParentAccountID
FROM
((Account AS Parent
LEFT JOIN
Account AS Child
ON
Parent.ParentAccountID = Child.AccountID)
LEFT JOIN
Account AS GrandChild
ON
Child.ParentAccountID = GrandChild.AccountID)
LEFT JOIN
Account AS GreatGrandChild
ON
GrandChild.ParentAccountID = GreatGrandChild.AccountID
ORDER BY
Format([GreatGrandChild].[ParentAccountID],"0000")
&
Format([GrandChild].[ParentAccountID],"0000")
&
Format([Child].[ParentAccountID],"0000")
&
Format([Parent].[ParentAccountID],"0000")
&
Format([Parent].[AccountID],"0000");

What if there is one more level of nesting?
Just add another "GreatGreatGrandChild"
instance of the table?

Usually it best not to assume level of nesting
and go for a more general approach.

Here be more step-by-step so maybe you can
follow along and understand Volk's method
better (I'm going to assume you have a test db
with the Account table and data above -- if not,
then how do you learn?)

create a table (say "tblOrg")

ID Text(10) pk
Parent Text(10) (allow Null, i.e., Required = No)
AccountName Text(255)
Depth Long (allow Null)
Lineage Text(255) (allow Null)

to easily create it, just run following query:

CREATE TABLE tblOrg
(ID TEXT(10) NOT NULL CONSTRAINT PK_ID PRIMARY KEY,
Parent TEXT(10),
AccountName TEXT(255),
Depth Long,
Lineage TEXT(255));

Create an append query (say "qryGetData")
that gets AccountID, ParentAccountID, and AccountName from your
Account table and fills tblOrg, but converts ID and
Parent to "0 justified" text in the process.

qryGetData:

INSERT INTO tblOrg ( ID, AccountName, Parent )
SELECT
Format([AccountID],"0000000000") AS ID,
Account.AccountName,
Format([ParentAccountID],"0000000000") AS Parent
FROM Account;

tblOrg should then look like:

ID Parent AccountName Depth Lineage
0000000001 0000000000 Checking
0000000002 0000000000 Savings
0000000003 0000000000 Expenses
0000000004 0000000000 Fixed Assets
0000000005 0000000003 Travel
0000000006 0000000004 Computers
0000000007 0000000004 Equipment
0000000008 0000000007 Printers
0000000009 0000000007 Fax
0000000010 0000000008 Laser
0000000011 0000000008 InkJet
0000000012 0000000003 Rent
0000000013 0000000012 MainOffice
0000000014 0000000012 BranchOffice
0000000015 0000000007 Copiers

then create query to add "root"

qryAddRoot:

INSERT INTO tblOrg (ID, Parent, AccountName, Depth,Lineage)
VALUES ('0000000000', Null, 'ROOT',0,'/')

tblOrg should then look like:

ID Parent AccountName Depth Lineage
0000000000 ROOT 0 /
0000000001 0000000000 Checking
0000000002 0000000000 Savings
0000000003 0000000000 Expenses
0000000004 0000000000 Fixed Assets
0000000005 0000000003 Travel
0000000006 0000000004 Computers
0000000007 0000000004 Equipment
0000000008 0000000007 Printers
0000000009 0000000007 Fax
0000000010 0000000008 Laser
0000000011 0000000008 InkJet
0000000012 0000000003 Rent
0000000013 0000000012 MainOffice
0000000014 0000000012 BranchOffice
0000000015 0000000007 Copiers

then create following query...

qryOnePass:

UPDATE tblOrg AS T INNER JOIN tblOrg AS P
ON T.Parent = P.ID
SET T.Depth = [P].[Depth]+1,
T.Lineage = [P].[Lineage] & [T].[Parent] & '/'
WHERE
(((P.Depth)>=0)
AND
((P.Lineage) Is Not Null)
AND
((T.Depth) Is Null));

the first time you run it, it will update 4 rows:

ID Parent AccountName Depth Lineage
0000000000 ROOT 0 /
0000000001 0000000000 Checking 1 /0000000000/
0000000002 0000000000 Savings 1 /0000000000/
0000000003 0000000000 Expenses 1 /0000000000/
0000000004 0000000000 Fixed Assets 1 /0000000000/
0000000005 0000000003 Travel
0000000006 0000000004 Computers
0000000007 0000000004 Equipment
0000000008 0000000007 Printers
0000000009 0000000007 Fax
0000000010 0000000008 Laser
0000000011 0000000008 InkJet
0000000012 0000000003 Rent
0000000013 0000000012 MainOffice
0000000014 0000000012 BranchOffice
0000000015 0000000007 Copiers

the next time you run it, it will update 4 more rows:

ID Parent AccountName Depth Lineage
0000000000 ROOT 0 /
0000000001 0000000000 Checking 1 /0000000000/
0000000002 0000000000 Savings 1 /0000000000/
0000000003 0000000000 Expense 1 /0000000000/
0000000004 0000000000 Fixed Assets 1 /0000000000/
0000000005 0000000003 Travel 2 /0000000000/0000000003/
0000000006 0000000004 Computers 2 /0000000000/0000000004/
0000000007 0000000004 Equipment 2 /0000000000/0000000004/
0000000008 0000000007 Printers
0000000009 0000000007 Fax
0000000010 0000000008 Laser
0000000011 0000000008 InkJet
0000000012 0000000003 Rent 2 /0000000000/0000000003/
0000000013 0000000012 MainOffice
0000000014 0000000012 BranchOffice
0000000015 0000000007 Copiers

if you continue to run query until no more rows
are updated, the last 3 fields should look like:

AccountName Depth Lineage
ROOT 0 /
Checking 1 /0000000000/
Savings 1 /0000000000/
Expenses 1 /0000000000/
Fixed Assets 1 /0000000000/
Travel 2 /0000000000/0000000003/
Computers 2 /0000000000/0000000004/
Equipment 2 /0000000000/0000000004/
Printers 3 /0000000000/0000000004/0000000007/
Fax 3 /0000000000/0000000004/0000000007/
Laser 4 /0000000000/0000000004/0000000007/0000000008/
InkJet 4 /0000000000/0000000004/0000000007/0000000008/
Rent 2 /0000000000/0000000003/
MainOffice 3 /0000000000/0000000003/0000000012/
BranchOffice 3 /0000000000/0000000003/0000000012/
Copiers 3 /0000000000/0000000004/0000000007/

qryIndentedList:

SELECT
Space([T].[Depth]*4) & [AccountName] AS Acct
FROM tblOrg AS T
ORDER BY
[Lineage] & [ID];

gives:

ROOT
Checking
Savings
Expenses
Travel
Rent
MainOffice
BranchOffice
Fixed Assets
Computers
Equipment
Printers
Laser
InkJet
Fax
Copiers

or what I thought vovan wanted:

SELECT
CLng([ID]) AS AccountID,
tblOrg.AccountName,
CLng([Parent]) AS ParentAccountID
FROM tblOrg
WHERE
tblOrg.AccountName <>'ROOT'
ORDER BY [Lineage] & [ID];

gives:

{indenting added in post}

AccountID AccountName ParentAccountID
1 Checking 0
2 Savings 0
3 Expenses 0
5 Travel 3
12 Rent 3
13 MainOffice 12
14 BranchOffice 12
4 Fixed Assets 0
6 Computers 4
7 Equipment 4
8 Printers 7
10 Laser 8
11 InkJet 8
9 Fax 7
15 Copiers 7

this method also lends itself to looking
at data in other ways, for example

"subordinates"

what are the subordinate accounts of
Expenses ("0000000003")?

SELECT
CLng([ID]) AS AccountID,
tblOrg.AccountName,
CLng([Parent]) AS ParentAccountID,
tblOrg.Depth
FROM tblOrg
WHERE
tblOrg.Lineage Like '*0000000003*'
ORDER BY [Lineage] & [ID];

Hopefully you can better understand now?

Remember there are other principles besides Occam's Razor...

2 forms of Karl Menger's Law Against Miserliness

"Entities must not be reduced to the point of inadequacy"

and

"It is vain to do with fewer what requires more"

or Kant's "counter-razor":

"The variety of beings should not rashly be diminished."

good luck,
...

read more »
Click to expand...

Hi ! I don't know why my last post wasn't shown in time.
And will repost again.

Dear Gary Walter,

Yes, Your sorting might be right.
I simulated it on my end.
Based on your query, I would like to amend as follow.
I created a query named "AccountName_Mixed" with following sql.

SELECT Parent.AccountID, IIf([AcName_4]<>"",
[AcName_4],IIf([AcName_3]<>"",[AcName_3],IIf([AcName_2]<>"",[AcName_2],
[AcName_1]))) AS AccountName, Parent.ParentAccountID, [Parent]!
[AccountName] AS AcName_1, Parent.ParentAccountID AS AcLevel1, IIf(Not
IsNull([AcLevel2])," " & [Parent]![AccountName],"") AS AcName_2,
Child.ParentAccountID AS AcLevel2, IIf(Not IsNull([AcLevel3])," "
& [Parent]![AccountName],"") AS AcName_3, GrandChild.ParentAccountID
AS AcLevel3, IIf(Not IsNull([AcLevel4])," " & [Parent]!
[AccountName],"") AS AcName_4, GreatGrandChild.ParentAccountID AS
AcLevel4
FROM ((Account AS Parent LEFT JOIN Account AS Child ON
Parent.ParentAccountID = Child.AccountID) LEFT JOIN Account AS
GrandChild ON Child.ParentAccountID = GrandChild.AccountID) LEFT JOIN
Account AS GreatGrandChild ON GrandChild.ParentAccountID =
GreatGrandChild.AccountID
ORDER BY Format([GreatGrandChild].[ParentAccountID],"0000") &
Format([GrandChild].[ParentAccountID],"0000") & Format([Child].
[ParentAccountID],"0000") & Format([Parent].[ParentAccountID],"0000")
& Format([Parent].[AccountID],"0000");

And then created another query "AccountName_Indented" with following
sql.

SELECT AccountName_Mixed.AccountID, AccountName_Mixed.AccountName,
AccountName_Mixed.ParentAccountID
FROM AccountName_Mixed;

Would you mind to simulate at your end, for comparing results.
It doesn't need action queries. And will go well as far as IIF gone
crazy.

Thanks for your query and links. It means a lot to me.
Rgds,
Kozaw
 
G

Gary Walter

----Kozaw wrote:
Yes, Your sorting might be right.
I simulated it on my end.
Based on your query, I would like to amend as follow.
I created a query named "AccountName_Mixed" with following sql.

SELECT Parent.AccountID, IIf([AcName_4]<>"",
[AcName_4],IIf([AcName_3]<>"",[AcName_3],IIf([AcName_2]<>"",[AcName_2],
[AcName_1]))) AS AccountName, Parent.ParentAccountID, [Parent]!
[AccountName] AS AcName_1, Parent.ParentAccountID AS AcLevel1, IIf(Not
IsNull([AcLevel2])," " & [Parent]![AccountName],"") AS AcName_2,
Child.ParentAccountID AS AcLevel2, IIf(Not IsNull([AcLevel3])," "
& [Parent]![AccountName],"") AS AcName_3, GrandChild.ParentAccountID
AS AcLevel3, IIf(Not IsNull([AcLevel4])," " & [Parent]!
[AccountName],"") AS AcName_4, GreatGrandChild.ParentAccountID AS
AcLevel4
FROM ((Account AS Parent LEFT JOIN Account AS Child ON
Parent.ParentAccountID = Child.AccountID) LEFT JOIN Account AS
GrandChild ON Child.ParentAccountID = GrandChild.AccountID) LEFT JOIN
Account AS GreatGrandChild ON GrandChild.ParentAccountID =
GreatGrandChild.AccountID
ORDER BY Format([GreatGrandChild].[ParentAccountID],"0000") &
Format([GrandChild].[ParentAccountID],"0000") & Format([Child].
[ParentAccountID],"0000") & Format([Parent].[ParentAccountID],"0000")
& Format([Parent].[AccountID],"0000");

And then created another query "AccountName_Indented" with following
sql.

SELECT AccountName_Mixed.AccountID, AccountName_Mixed.AccountName,
AccountName_Mixed.ParentAccountID
FROM AccountName_Mixed;

Would you mind to simulate at your end, for comparing results.
It doesn't need action queries. And will go well as far as IIF gone
crazy.

Thanks for your query and links. It means a lot to me.
Rgds,
Kozaw

------ response --------

I don't know how you can assume the adjacency table
will be only this deep. What is to keep someone from
saying they are going to branch out the laser printers
into HP and Lexmark (as a silly example)?

AccountID AccountName ParentAccountID
1 Checking 0
2 Savings 0
3 Expenses 0
4 Fixed Assets 0
5 Travel 3
6 Computers 4
7 Equipment 4
8 Printers 7
9 Fax 7
10 Laser 8
11 InkJet 8
12 Rent 3
13 MainOffice 12
14 BranchOffice 12
15 Copiers 7
16 HP 10
17 Lexmark 10

When you find yourself upon a dead horse,
the best advice I can give you is to dismount...

good luck,

gary
 
G

Guest

if you need help with SELECT then you shoudl use an Access Data Project



Sorry. Don't misunderstand on me.
Should Simple coding with same result be the goal?
___________________________________________
Click to expand...

I apologise for being a little snarky, but I
do not believe you will get "same result."

I believe vovan should have given us some
sample data like:

AccountID AccountName ParentAccountID
1 Checking 0
2 Savings 0
3 Expenses 0
4 Fixed Assets 0
5 Travel 3
6 Computers 4
7 Equipment 4
8 Printers 7
9 Fax 7
10 Laser 8
11 InkJet 8
12 Rent 3
13 MainOffice 12
14 BranchOffice 12
15 Copiers 7

Save the above to a table Account
and run your query to see how it works.

This is what I think vovan wants (I could
be wrong):

{accts are indented to show nesting}

AccountID AccountName ParentAccountID
1 Checking 0
2 Savings 0
3 Expenses 0
5 Travel 3
12 Rent 3
13 MainOffice 12
14 BranchOffice 12
4 Fixed Assets 0
6 Computers 4
7 Equipment 4
8 Printers 7
10 Laser 8
11 InkJet 8
9 Fax 7
15 Copiers 7

If we knew *for sure* there was only
this deep level of nesting, we could just
use 4 instances of table to sort, i.e.,

{formatting assumes AccountID<=9999}

SELECT
Parent.AccountID,
Parent.AccountName,
Parent.ParentAccountID
FROM
((Account AS Parent
LEFT JOIN
Account AS Child
ON
Parent.ParentAccountID = Child.AccountID)
LEFT JOIN
Account AS GrandChild
ON
Child.ParentAccountID = GrandChild.AccountID)
LEFT JOIN
Account AS GreatGrandChild
ON
GrandChild.ParentAccountID = GreatGrandChild.AccountID
ORDER BY
Format([GreatGrandChild].[ParentAccountID],"0000")
&
Format([GrandChild].[ParentAccountID],"0000")
&
Format([Child].[ParentAccountID],"0000")
&
Format([Parent].[ParentAccountID],"0000")
&
Format([Parent].[AccountID],"0000");

What if there is one more level of nesting?
Just add another "GreatGreatGrandChild"
instance of the table?

Usually it best not to assume level of nesting
and go for a more general approach.

Here be more step-by-step so maybe you can
follow along and understand Volk's method
better (I'm going to assume you have a test db
with the Account table and data above -- if not,
then how do you learn?)

create a table (say "tblOrg")

ID Text(10) pk
Parent Text(10) (allow Null, i.e., Required = No)
AccountName Text(255)
Depth Long (allow Null)
Lineage Text(255) (allow Null)

to easily create it, just run following query:

CREATE TABLE tblOrg
(ID TEXT(10) NOT NULL CONSTRAINT PK_ID PRIMARY KEY,
Parent TEXT(10),
AccountName TEXT(255),
Depth Long,
Lineage TEXT(255));

Create an append query (say "qryGetData")
that gets AccountID, ParentAccountID, and AccountName from your
Account table and fills tblOrg, but converts ID and
Parent to "0 justified" text in the process.

qryGetData:

INSERT INTO tblOrg ( ID, AccountName, Parent )
SELECT
Format([AccountID],"0000000000") AS ID,
Account.AccountName,
Format([ParentAccountID],"0000000000") AS Parent
FROM Account;

tblOrg should then look like:

ID Parent AccountName Depth Lineage
0000000001 0000000000 Checking
0000000002 0000000000 Savings
0000000003 0000000000 Expenses
0000000004 0000000000 Fixed Assets
0000000005 0000000003 Travel
0000000006 0000000004 Computers
0000000007 0000000004 Equipment
0000000008 0000000007 Printers
0000000009 0000000007 Fax
0000000010 0000000008 Laser
0000000011 0000000008 InkJet
0000000012 0000000003 Rent
0000000013 0000000012 MainOffice
0000000014 0000000012 BranchOffice
0000000015 0000000007 Copiers

then create query to add "root"

qryAddRoot:

INSERT INTO tblOrg (ID, Parent, AccountName, Depth,Lineage)
VALUES ('0000000000', Null, 'ROOT',0,'/')

tblOrg should then look like:

ID Parent AccountName Depth Lineage
0000000000 ROOT 0 /
0000000001 0000000000 Checking
0000000002 0000000000 Savings
0000000003 0000000000 Expenses
0000000004 0000000000 Fixed Assets
0000000005 0000000003 Travel
0000000006 0000000004 Computers
0000000007 0000000004 Equipment
0000000008 0000000007 Printers
0000000009 0000000007 Fax
0000000010 0000000008 Laser
0000000011 0000000008 InkJet
0000000012 0000000003 Rent
0000000013 0000000012 MainOffice
0000000014 0000000012 BranchOffice
0000000015 0000000007 Copiers

then create following query...

qryOnePass:

UPDATE tblOrg AS T INNER JOIN tblOrg AS P
ON T.Parent = P.ID
SET T.Depth = [P].[Depth]+1,
T.Lineage = [P].[Lineage] & [T].[Parent] & '/'
WHERE
(((P.Depth)>=0)
AND
((P.Lineage) Is Not Null)
AND
((T.Depth) Is Null));

the first time you run it, it will update 4 rows:

ID Parent AccountName Depth Lineage
0000000000 ROOT 0 /
0000000001 0000000000 Checking 1 /0000000000/
0000000002 0000000000 Savings 1 /0000000000/
0000000003 0000000000 Expenses 1 /0000000000/
0000000004 0000000000 Fixed Assets 1 /0000000000/
0000000005 0000000003 Travel
0000000006 0000000004 Computers
0000000007 0000000004 Equipment
0000000008 0000000007 Printers
0000000009 0000000007 Fax
0000000010 0000000008 Laser
0000000011 0000000008 InkJet
0000000012 0000000003 Rent
0000000013 0000000012 MainOffice
0000000014 0000000012 BranchOffice
0000000015 0000000007 Copiers

the next time you run it, it will update 4 more rows:

ID Parent AccountName Depth Lineage
0000000000 ROOT 0 /
0000000001 0000000000 Checking 1 /0000000000/
0000000002 0000000000 Savings 1 /0000000000/
0000000003 0000000000 Expense 1 /0000000000/
0000000004 0000000000 Fixed Assets 1 /0000000000/
0000000005 0000000003 Travel 2 /0000000000/0000000003/
0000000006 0000000004 Computers 2 /0000000000/0000000004/
0000000007 0000000004 Equipment 2 /0000000000/0000000004/
0000000008 0000000007 Printers
0000000009 0000000007 Fax
0000000010 0000000008 Laser
0000000011 0000000008 InkJet
0000000012 0000000003 Rent 2 /0000000000/0000000003/
0000000013 0000000012 MainOffice
0000000014 0000000012 BranchOffice
0000000015 0000000007 Copiers

if you continue to run query until no more rows
are updated, the last 3 fields should look like:

AccountName Depth Lineage
ROOT 0 /
Checking 1 /0000000000/
Savings 1 /0000000000/
Expenses 1 /0000000000/
Fixed Assets 1 /0000000000/
Travel 2 /0000000000/0000000003/
Computers 2 /0000000000/0000000004/
Equipment 2 /0000000000/0000000004/
Printers 3 /0000000000/0000000004/0000000007/
Fax 3 /0000000000/0000000004/0000000007/
Laser 4 /0000000000/0000000004/0000000007/0000000008/
InkJet 4 /0000000000/0000000004/0000000007/0000000008/
Rent 2 /0000000000/0000000003/
MainOffice 3 /0000000000/0000000003/0000000012/
BranchOffice 3 /0000000000/0000000003/0000000012/
Copiers 3 /0000000000/0000000004/0000000007/

qryIndentedList:

SELECT
Space([T].[Depth]*4) & [AccountName] AS Acct
FROM tblOrg AS T
ORDER BY
[Lineage] & [ID];

gives:

ROOT
Checking
Savings
Expenses
Travel
Rent
MainOffice
BranchOffice
Fixed Assets
Computers
Equipment
Printers
Laser
InkJet
Fax
Copiers

or what I thought vovan wanted:

SELECT
CLng([ID]) AS AccountID,
tblOrg.AccountName,
CLng([Parent]) AS ParentAccountID
FROM tblOrg
WHERE
tblOrg.AccountName <>'ROOT'
ORDER BY [Lineage] & [ID];

gives:

{indenting added in post}

AccountID AccountName ParentAccountID
1 Checking 0
2 Savings 0
3 Expenses 0
5 Travel 3
12 Rent 3
13 MainOffice 12
14 BranchOffice 12
4 Fixed Assets 0
6 Computers 4
7 Equipment 4
8 Printers 7
10 Laser 8
11 InkJet 8
9 Fax 7
15 Copiers 7

this method also lends itself to looking
at data in other ways, for example

"subordinates"

what are the subordinate accounts of
Expenses ("0000000003")?

SELECT
CLng([ID]) AS AccountID,
tblOrg.AccountName,
CLng([Parent]) AS ParentAccountID,
tblOrg.Depth
FROM tblOrg
WHERE
tblOrg.Lineage Like '*0000000003*'
ORDER BY [Lineage] & [ID];

Hopefully you can better understand now?

Remember there are other principles besides Occam's Razor...

2 forms of Karl Menger's Law Against Miserliness

"Entities must not be reduced to the point of inadequacy"

and

"It is vain to do with fewer what requires more"

or Kant's "counter-razor":

"The variety of beings should not rashly be diminished."

good luck,
...

read more »
Click to expand...

Hi ! I don't know why my last post wasn't shown in time.
And will repost again.

Dear Gary Walter,

Yes, Your sorting might be right.
I simulated it on my end.
Based on your query, I would like to amend as follow.
I created a query named "AccountName_Mixed" with following sql.

SELECT Parent.AccountID, IIf([AcName_4]<>"",
[AcName_4],IIf([AcName_3]<>"",[AcName_3],IIf([AcName_2]<>"",[AcName_2],
[AcName_1]))) AS AccountName, Parent.ParentAccountID, [Parent]!
[AccountName] AS AcName_1, Parent.ParentAccountID AS AcLevel1, IIf(Not
IsNull([AcLevel2])," " & [Parent]![AccountName],"") AS AcName_2,
Child.ParentAccountID AS AcLevel2, IIf(Not IsNull([AcLevel3])," "
& [Parent]![AccountName],"") AS AcName_3, GrandChild.ParentAccountID
AS AcLevel3, IIf(Not IsNull([AcLevel4])," " & [Parent]!
[AccountName],"") AS AcName_4, GreatGrandChild.ParentAccountID AS
AcLevel4
FROM ((Account AS Parent LEFT JOIN Account AS Child ON
Parent.ParentAccountID = Child.AccountID) LEFT JOIN Account AS
GrandChild ON Child.ParentAccountID = GrandChild.AccountID) LEFT JOIN
Account AS GreatGrandChild ON GrandChild.ParentAccountID =
GreatGrandChild.AccountID
ORDER BY Format([GreatGrandChild].[ParentAccountID],"0000") &
Format([GrandChild].[ParentAccountID],"0000") & Format([Child].
[ParentAccountID],"0000") & Format([Parent].[ParentAccountID],"0000")
& Format([Parent].[AccountID],"0000");

And then created another query "AccountName_Indented" with following
sql.

SELECT AccountName_Mixed.AccountID, AccountName_Mixed.AccountName,
AccountName_Mixed.ParentAccountID
FROM AccountName_Mixed;

Would you mind to simulate at your end, for comparing results.
It doesn't need action queries. And will go well as far as IIF gone
crazy.

Thanks for your query and links. It means a lot to me.
Rgds,
Kozaw
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating a Chart on form from selected data in query. 0
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
Subquery not integrating to query to display previous sum of quantities 3
Remove Identical words 0
Using FIND to highlight text (with dashes) 4
Paragraph numbering in Word 1
Arriving at totals 2
how to solve the following in excel 1

Top