Help with SELECT

V

vovan

I have a table with Accounts, let's call it Account.
It has the following fields:
AccountID
AccountName
ParentAccountID

Data in the table:
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

I need to write SELECT which will return data in the following format:

AccountID AccountName
ParentAccountID
1 Checking
0
2 Savings
0
3 Expenses
0
5 Travel
3
4 Fixed Assets
0
6 Computers
4
7 Equipment
4

How do I do that?
Thank you

vovan
 
V

vovan

Unfortunately the format I used to write my question was changed by outlook
express and now it's hard to see the columns.
There are 3 columns AccountID, AccountName, ParentAccountID and 7 rows with
values

vovan
 
G

Guest

Try this --
SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY Account.AccountName;
 
V

vovan

No, it doesn't work the way I wanted.
I do not need just to order records by AccountName. If you noticed I need
them to be listed in order Parent then all its children, then next parent
with its children. There may be needed self join, or something else.

Anyway, thank you
vovan
 
G

Guest

I try again ---
SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY Account.ParentAccountID, Account.AccountID;
 
V

vovan

No, thanks.

vovan

KARL DEWEY said:
I try again ---
SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY Account.ParentAccountID, Account.AccountID;
Click to expand...
 
G

Guest

I am lost as what your order is to be. Your post has this --
1 Checking 0
2 Savings 0
3 Expenses 0
5 Travel 3
4 Fixed Assets 0 Seems out of order -- why is this correct?
6 Computers 4
7 Equipment 4

Can you explain some more - maybe with a different set of data.
 
G

Gary Walter

vovan wrote:
I have a table with Accounts, let's call it Account.
It has the following fields:
AccountID
AccountName
ParentAccountID

Data in the table:
AccountID AccountName ParentAccountID SortBy
1 Checking 0 /01/
2 Savings 0 /02/
3 Expenses 0 / 03/
4 Fixed Assets 0 / 04/
5 Travel 3 / 03/05/
6 Computers 4 / 04/06/
7 Equipment 4 / 04/07/

I need to write SELECT which will return data in the following format:

AccountID AccountName ParentAccountID
1 Checking 0
2 Savings 0
3 Expenses 0
5 Travel 3
4 Fixed Assets 0
6 Computers 4
7 Equipment 4

////////////////////////////////
I added a SortBy field to your table data that
should sort as you want (if I understand correctly)

this can be a calculated field column in your query

Field: SortBy: IIF(ParentAccountID=0,"/" & Right("00" & [AccountID],2) &
"/", "/" & Right("00" & [ParentAccountID],2) & "/" & Right("00" &
[AccountID],2) & "/"
Table:
Sort: Ascending
Show: <checked>
Criteria:
Or:
 
K

Ko Zaw

Unfortunately the format I used to write my question was changed by outlook
express and now it's hard to see the columns.
There are 3 columns AccountID, AccountName, ParentAccountID and 7 rows with
values

vovan
Click to expand...

Let me guess.

SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY Format([Account]![ParentAccountID],"00") & Format([Account]!
[AccountID],"00");

Ko Zaw
 
G

Gary Walter

Ko Zaw said:
Unfortunately the format I used to write my question was changed by
outlook
express and now it's hard to see the columns.
There are 3 columns AccountID, AccountName, ParentAccountID and 7 rows
with
values

vovan
Click to expand...

Let me guess.

SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY Format([Account]![ParentAccountID],"00") & Format([Account]!
[AccountID],"00");
Click to expand...
Hi Ko Zaw,

I could be wrong, but I think using your format
vovan would want

SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY
IIF([ParentAccountID=0,
Format([AccountID],"00"),
Format([ParentAccountID],"00") & Format([AccountID],"00");
 
G

Gary Walter

sorry, previous response missing an ending ")"

SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY
IIF([ParentAccountID=0,
Format([AccountID],"00"),
Format([ParentAccountID],"00") & Format([AccountID],"00"));

Gary Walter said:
Ko Zaw said:
Unfortunately the format I used to write my question was changed by
outlook
express and now it's hard to see the columns.
There are 3 columns AccountID, AccountName, ParentAccountID and 7 rows
with
values

vovan







I have a table with Accounts, let's call it Account.
It has the following fields:
AccountID
AccountName
ParentAccountID

Data in the table:
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

I need to write SELECT which will return data in the following format:

AccountID AccountName ParentAccountID
1 Checking 0
2 Savings 0
3 Expenses 0
5 Travel 3
4 Fixed Assets 0
6 Computers 4
7 Equipment 4

How do I do that?
Thank you

vovan
Click to expand...

Let me guess.

SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY Format([Account]![ParentAccountID],"00") & Format([Account]!
[AccountID],"00");
Click to expand...
Hi Ko Zaw,

I could be wrong, but I think using your format
vovan would want

SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY
IIF([ParentAccountID=0,
Format([AccountID],"00"),
Format([ParentAccountID],"00") & Format([AccountID],"00");
Click to expand...
 
V

vovan

Everything is correct here:
1 Checking 0
2 Savings 0
3 Expenses 0
5 Travel 3
4 Fixed Assets 0 Seems out of order -- why is this correct?
This is because (5 Travel 3) row is a child of the (3 Expense 0) row. It's a
subcategory of Expenses.
The row (4 Fixed Assets 0) comes after because it's a new category after
Expenses category. Remaining 2 rows are subcategories of Fixed Assets
category.
6 Computers 4
7 Equipment 4
 
V

vovan

Thank you
It's not what I wanted again.
If you have QuickBooks then look at its Accounting List. That list
represents values in the order I want and all values are stored in a single
table

vovan

Gary Walter said:
sorry, previous response missing an ending ")"

SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY
IIF([ParentAccountID=0,
Format([AccountID],"00"),
Format([ParentAccountID],"00") & Format([AccountID],"00"));

Gary Walter said:
Ko Zaw said:
Unfortunately the format I used to write my question was changed by
outlook
express and now it's hard to see the columns.
There are 3 columns AccountID, AccountName, ParentAccountID and 7 rows
with
values

vovan







I have a table with Accounts, let's call it Account.
It has the following fields:
AccountID
AccountName
ParentAccountID

Data in the table:
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

I need to write SELECT which will return data in the following
format:

AccountID AccountName ParentAccountID
1 Checking 0
2 Savings 0
3 Expenses 0
5 Travel 3
4 Fixed Assets 0
6 Computers 4
7 Equipment 4

How do I do that?
Thank you

vovan

Let me guess.

SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY Format([Account]![ParentAccountID],"00") & Format([Account]!
[AccountID],"00");
Click to expand...
Hi Ko Zaw,

I could be wrong, but I think using your format
vovan would want

SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY
IIF([ParentAccountID=0,
Format([AccountID],"00"),
Format([ParentAccountID],"00") & Format([AccountID],"00");
Click to expand...
Click to expand...
 
G

Gary Walter

Hi vovan,

I'm sorry I don't have QuickBooks.

I really thought that the query would
sort properly *given the sample data
you showed us.*

So...there are 2 possibilities...

1) your AccountID's can be greater than 99?

if that's the case, then change format to accomodate
larger AccountID's

this should handle any Long AccountID you throw at it:

SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY
IIF([ParentAccountID=0,
Format([AccountID],"0000000000"),
Format([ParentAccountID],"0000000000")
& "/" & Format([AccountID],"0000000000"));

in fact, I would make the order by into a calculated field
and double-check result (and maybe copy here) if wrong.
It should string sort as you wanted.

unless...

2) You maybe have "nested sets,"
i.e., an acct can have a parent
and that parent has a parent other 0

for 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 someacct 7

where "someacct" has parent 7,
which has parent 4,
which *then* has parent 0.

If that's the case, then you probably will
have to create a sort table.

One method for this was illustrated by Rob Volk:
http://www.sqlteam.com/item.asp?ItemID=8866

Please respond back if that is what you need
and you need further assistance.

good luck,

gary





vovan said:
It's not what I wanted again.
If you have QuickBooks then look at its Accounting List. That list
represents values in the order I want and all values are stored in a
single table

vovan

Gary Walter said:
sorry, previous response missing an ending ")"

SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY
IIF([ParentAccountID=0,
Format([AccountID],"00"),
Format([ParentAccountID],"00") & Format([AccountID],"00"));

Gary Walter said:
:
Unfortunately the format I used to write my question was changed by
outlook
express and now it's hard to see the columns.
There are 3 columns AccountID, AccountName, ParentAccountID and 7 rows
with
values

vovan







I have a table with Accounts, let's call it Account.
It has the following fields:
AccountID
AccountName
ParentAccountID

Data in the table:
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

I need to write SELECT which will return data in the following
format:

AccountID AccountName ParentAccountID
1 Checking 0
2 Savings 0
3 Expenses 0
5 Travel 3
4 Fixed Assets 0
6 Computers 4
7 Equipment 4

How do I do that?
Thank you

vovan

Let me guess.

SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY Format([Account]![ParentAccountID],"00") & Format([Account]!
[AccountID],"00");

Hi Ko Zaw,

I could be wrong, but I think using your format
vovan would want

SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY
IIF([ParentAccountID=0,
Format([AccountID],"00"),
Format([ParentAccountID],"00") & Format([AccountID],"00");
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

Just one level of hierarchy? if so


SELECT *
FROM account
ORDER BY iif(parentAccountID=0, accountID, parentAccountID), a.accountID



should do.



Hoping it may help,
Vanderghast, Access MVP
 
G

Guest

I had to use two tables to make it work.
Account ---
ParentAccountID AccountID AccountName
0 1 Checking
0 2 Savings
0 3 Expenses
0 4 Fixed Assets
3 5 Travel

Account_Sub ---
ParentAccountID AccountID AccountName
4 6 Computers
4 7 Equipment

SELECT Account.ParentAccountID, Account.AccountID, Account.AccountName,
Account_Sub.AccountName
FROM Account LEFT JOIN Account_Sub ON Account.AccountID =
Account_Sub.ParentAccountID
ORDER BY Account.ParentAccountID, Account.AccountID;
 
V

vovan

Another way to understand what I want it to assume the data in expanded
TreeView:
ParentRecord
ChildRecord
AnotherChildRecord
GrandChildRecord
AnotherParentRecordWithNoChildren
AnotherParentRecord
ChildRecord
AnotherParentRecord
ChildRecord
AnotherParentRecordWithNoChildren
AnotherParentRecordWithNoChildren

and so on

vovan

Gary Walter said:
Hi vovan,

I'm sorry I don't have QuickBooks.

I really thought that the query would
sort properly *given the sample data
you showed us.*

So...there are 2 possibilities...

1) your AccountID's can be greater than 99?

if that's the case, then change format to accomodate
larger AccountID's

this should handle any Long AccountID you throw at it:

SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY
IIF([ParentAccountID=0,
Format([AccountID],"0000000000"),
Format([ParentAccountID],"0000000000")
& "/" & Format([AccountID],"0000000000"));

in fact, I would make the order by into a calculated field
and double-check result (and maybe copy here) if wrong.
It should string sort as you wanted.

unless...

2) You maybe have "nested sets,"
i.e., an acct can have a parent
and that parent has a parent other 0

for 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 someacct 7

where "someacct" has parent 7,
which has parent 4,
which *then* has parent 0.

If that's the case, then you probably will
have to create a sort table.

One method for this was illustrated by Rob Volk:
http://www.sqlteam.com/item.asp?ItemID=8866

Please respond back if that is what you need
and you need further assistance.

good luck,

gary





vovan said:
It's not what I wanted again.
If you have QuickBooks then look at its Accounting List. That list
represents values in the order I want and all values are stored in a
single table

vovan

Gary Walter said:
sorry, previous response missing an ending ")"

SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY
IIF([ParentAccountID=0,
Format([AccountID],"00"),
Format([ParentAccountID],"00") & Format([AccountID],"00"));

:

:
Unfortunately the format I used to write my question was changed by
outlook
express and now it's hard to see the columns.
There are 3 columns AccountID, AccountName, ParentAccountID and 7
rows with
values

vovan







I have a table with Accounts, let's call it Account.
It has the following fields:
AccountID
AccountName
ParentAccountID

Data in the table:
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

I need to write SELECT which will return data in the following
format:

AccountID AccountName ParentAccountID
1 Checking 0
2 Savings 0
3 Expenses 0
5 Travel 3
4 Fixed Assets 0
6 Computers 4
7 Equipment 4

How do I do that?
Thank you

vovan

Let me guess.

SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY Format([Account]![ParentAccountID],"00") & Format([Account]!
[AccountID],"00");

Hi Ko Zaw,

I could be wrong, but I think using your format
vovan would want

SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY
IIF([ParentAccountID=0,
Format([AccountID],"00"),
Format([ParentAccountID],"00") & Format([AccountID],"00");
Click to expand...
Click to expand...
Click to expand...
 
G

Gary Walter

So you have an "adjacency model" (as in #2 of reply)
and SQL solution usually involves non-trivial translation
to "nested sets"

Spend the day searching Google Groups on

"Joe Celko Tree Structures nested sets"

or did you look at Volk solution?

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)

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.

ID: Right("0000000000" & Account.AccountID, 10)
Parent: Right("0000000000" & Account.ParentAccountID, 10)

Then create 2 queries:

qryAddRoot:

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

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));

When you want to run your report

'*** aircode***
Dim db As DAO.Database

Set db = CurrentDb

'clear tblOrg
db.Execute "DELETE * FROM tblOrg", dbFailOnError

'execute your append query
db.Execute "qryGetData", dbFailOnError

'add root
db.Execute "qryAddRoot", dbFailOnError

'make passes until all have Depth and Lineage
Do While Dcount("*","tblOrg","[Depth] Is Null") > 0
db.Execute "qryOnePass", dbFailOnError
Loop

db.Close

'then open report sorted by Depth and Lineage.
'if you need more fields from Account for report
'recordsource, join tblOrgs to Account

'*** end aircode***


The Text(10) should allow you to go to a depth
of at least 22.

Like I said, it is non-trivial, but I have used
successfully on several ocassions...

good luck,

gary

vovan said:
Another way to understand what I want it to assume the data in expanded
TreeView:
ParentRecord
ChildRecord
AnotherChildRecord
GrandChildRecord
AnotherParentRecordWithNoChildren
AnotherParentRecord
ChildRecord
AnotherParentRecord
ChildRecord
AnotherParentRecordWithNoChildren
AnotherParentRecordWithNoChildren

and so on

vovan

Gary Walter said:
Hi vovan,

I'm sorry I don't have QuickBooks.

I really thought that the query would
sort properly *given the sample data
you showed us.*

So...there are 2 possibilities...

1) your AccountID's can be greater than 99?

if that's the case, then change format to accomodate
larger AccountID's

this should handle any Long AccountID you throw at it:

SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY
IIF([ParentAccountID=0,
Format([AccountID],"0000000000"),
Format([ParentAccountID],"0000000000")
& "/" & Format([AccountID],"0000000000"));

in fact, I would make the order by into a calculated field
and double-check result (and maybe copy here) if wrong.
It should string sort as you wanted.

unless...

2) You maybe have "nested sets,"
i.e., an acct can have a parent
and that parent has a parent other 0

for 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 someacct 7

where "someacct" has parent 7,
which has parent 4,
which *then* has parent 0.

If that's the case, then you probably will
have to create a sort table.

One method for this was illustrated by Rob Volk:
http://www.sqlteam.com/item.asp?ItemID=8866

Please respond back if that is what you need
and you need further assistance.

good luck,

gary





vovan said:
It's not what I wanted again.
If you have QuickBooks then look at its Accounting List. That list
represents values in the order I want and all values are stored in a
single table

vovan

sorry, previous response missing an ending ")"

SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY
IIF([ParentAccountID=0,
Format([AccountID],"00"),
Format([ParentAccountID],"00") & Format([AccountID],"00"));

:

:
Unfortunately the format I used to write my question was changed by
outlook
express and now it's hard to see the columns.
There are 3 columns AccountID, AccountName, ParentAccountID and 7
rows with
values

vovan







I have a table with Accounts, let's call it Account.
It has the following fields:
AccountID
AccountName
ParentAccountID

Data in the table:
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

I need to write SELECT which will return data in the following
format:

AccountID AccountName ParentAccountID
1 Checking 0
2 Savings 0
3 Expenses 0
5 Travel 3
4 Fixed Assets 0
6 Computers 4
7 Equipment 4

How do I do that?
Thank you

vovan

Let me guess.

SELECT Account.AccountID, Account.AccountName,
Account.ParentAccountID
FROM Account
ORDER BY Format([Account]![ParentAccountID],"00") & Format([Account]!
[AccountID],"00");

Hi Ko Zaw,

I could be wrong, but I think using your format
vovan would want

SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY
IIF([ParentAccountID=0,
Format([AccountID],"00"),
Format([ParentAccountID],"00") & Format([AccountID],"00");
Click to expand...
Click to expand...
Click to expand...
 
G

Gary Walter

sorry, erroneously cut-and-pasted from an earlier post...

qryAddRoot should be:

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


Gary Walter said:
So you have an "adjacency model" (as in #2 of reply)
and SQL solution usually involves non-trivial translation
to "nested sets"

Spend the day searching Google Groups on

"Joe Celko Tree Structures nested sets"

or did you look at Volk solution?

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)

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.

ID: Right("0000000000" & Account.AccountID, 10)
Parent: Right("0000000000" & Account.ParentAccountID, 10)

Then create 2 queries:

qryAddRoot:

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

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));

When you want to run your report

'*** aircode***
Dim db As DAO.Database

Set db = CurrentDb

'clear tblOrg
db.Execute "DELETE * FROM tblOrg", dbFailOnError

'execute your append query
db.Execute "qryGetData", dbFailOnError

'add root
db.Execute "qryAddRoot", dbFailOnError

'make passes until all have Depth and Lineage
Do While Dcount("*","tblOrg","[Depth] Is Null") > 0
db.Execute "qryOnePass", dbFailOnError
Loop

db.Close

'then open report sorted by Depth and Lineage.
'if you need more fields from Account for report
'recordsource, join tblOrgs to Account

'*** end aircode***


The Text(10) should allow you to go to a depth
of at least 22.

Like I said, it is non-trivial, but I have used
successfully on several ocassions...

good luck,

gary

vovan said:
Another way to understand what I want it to assume the data in expanded
TreeView:
ParentRecord
ChildRecord
AnotherChildRecord
GrandChildRecord
AnotherParentRecordWithNoChildren
AnotherParentRecord
ChildRecord
AnotherParentRecord
ChildRecord
AnotherParentRecordWithNoChildren
AnotherParentRecordWithNoChildren

and so on

vovan

Gary Walter said:
Hi vovan,

I'm sorry I don't have QuickBooks.

I really thought that the query would
sort properly *given the sample data
you showed us.*

So...there are 2 possibilities...

1) your AccountID's can be greater than 99?

if that's the case, then change format to accomodate
larger AccountID's

this should handle any Long AccountID you throw at it:

SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY
IIF([ParentAccountID=0,
Format([AccountID],"0000000000"),
Format([ParentAccountID],"0000000000")
& "/" & Format([AccountID],"0000000000"));

in fact, I would make the order by into a calculated field
and double-check result (and maybe copy here) if wrong.
It should string sort as you wanted.

unless...

2) You maybe have "nested sets,"
i.e., an acct can have a parent
and that parent has a parent other 0

for 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 someacct 7

where "someacct" has parent 7,
which has parent 4,
which *then* has parent 0.

If that's the case, then you probably will
have to create a sort table.

One method for this was illustrated by Rob Volk:
http://www.sqlteam.com/item.asp?ItemID=8866

Please respond back if that is what you need
and you need further assistance.

good luck,

gary





:
It's not what I wanted again.
If you have QuickBooks then look at its Accounting List. That list
represents values in the order I want and all values are stored in a
single table

vovan

sorry, previous response missing an ending ")"

SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY
IIF([ParentAccountID=0,
Format([AccountID],"00"),
Format([ParentAccountID],"00") & Format([AccountID],"00"));

:

:
Unfortunately the format I used to write my question was changed by
outlook
express and now it's hard to see the columns.
There are 3 columns AccountID, AccountName, ParentAccountID and 7
rows with
values

vovan







I have a table with Accounts, let's call it Account.
It has the following fields:
AccountID
AccountName
ParentAccountID

Data in the table:
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

I need to write SELECT which will return data in the following
format:

AccountID AccountName ParentAccountID
1 Checking 0
2 Savings 0
3 Expenses 0
5 Travel 3
4 Fixed Assets 0
6 Computers 4
7 Equipment 4

How do I do that?
Thank you

vovan

Let me guess.

SELECT Account.AccountID, Account.AccountName,
Account.ParentAccountID
FROM Account
ORDER BY Format([Account]![ParentAccountID],"00") &
Format([Account]!
[AccountID],"00");

Hi Ko Zaw,

I could be wrong, but I think using your format
vovan would want

SELECT Account.AccountID, Account.AccountName,
Account.ParentAccountID
FROM Account
ORDER BY
IIF([ParentAccountID=0,
Format([AccountID],"00"),
Format([ParentAccountID],"00") & Format([AccountID],"00");
Click to expand...
Click to expand...
Click to expand...
 
B

Bamar

sorry, erroneously cut-and-pasted from an earlier post...

qryAddRoot should be:

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

Gary Walter said:
So you have an "adjacency model" (as in #2 of reply)
and SQL solution usually involves non-trivial translation
to "nested sets"
Click to expand...
Spend the day searching Google Groups on
Click to expand...
"Joe Celko Tree Structures nested sets"
Click to expand...
or did you look at Volk solution?
Click to expand...
create a table (say "tblOrg")
Click to expand...
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)
Click to expand...
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.
Click to expand...
ID: Right("0000000000" & Account.AccountID, 10)
Parent: Right("0000000000" & Account.ParentAccountID, 10)
Click to expand...
Then create 2 queries:
qryAddRoot:
Click to expand...

INSERT INTO tblOrg (ID, Parent, Position, Depth,Lineage)
VALUES ('0000000000',Null,0,0,'/')
qryOnePass:
Click to expand...

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));
Click to expand...
When you want to run your report
Click to expand...
'*** aircode***
Dim db As DAO.Database
Click to expand...
Set db = CurrentDb
Click to expand...
'clear tblOrg
db.Execute "DELETE * FROM tblOrg", dbFailOnError
Click to expand...
'execute your append query
db.Execute "qryGetData", dbFailOnError
Click to expand...
'add root
db.Execute "qryAddRoot", dbFailOnError
Click to expand...
'make passes until all have Depth and Lineage
Do While Dcount("*","tblOrg","[Depth] Is Null") > 0
db.Execute "qryOnePass", dbFailOnError
Loop
db.Close
Click to expand...

'then open report sorted by Depth and Lineage.
'if you need more fields from Account for report
'recordsource, join tblOrgs to Account
Click to expand...
'*** end aircode***
Click to expand...
The Text(10) should allow you to go to a depth
of at least 22.
Click to expand...
Like I said, it is non-trivial, but I have used
successfully on several ocassions...
Click to expand...
good luck,
gary
Click to expand...

vovan said:
Another way to understand what I want it to assume the data in expanded
TreeView:
ParentRecord
ChildRecord
AnotherChildRecord
GrandChildRecord
AnotherParentRecordWithNoChildren
AnotherParentRecord
ChildRecord
AnotherParentRecord
ChildRecord
AnotherParentRecordWithNoChildren
AnotherParentRecordWithNoChildren
and so on
vovan
Hi vovan,
I'm sorry I don't have QuickBooks.
I really thought that the query would
sort properly *given the sample data
you showed us.*
So...there are 2 possibilities...
1) your AccountID's can be greater than 99?
if that's the case, then change format to accomodate
larger AccountID's
this should handle any Long AccountID you throw at it:
SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY
IIF([ParentAccountID=0,
Format([AccountID],"0000000000"),
Format([ParentAccountID],"0000000000")
& "/" & Format([AccountID],"0000000000"));
in fact, I would make the order by into a calculated field
and double-check result (and maybe copy here) if wrong.
It should string sort as you wanted.
unless...
2) You maybe have "nested sets,"
i.e., an acct can have a parent
and that parent has a parent other 0
for 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 someacct 7
where "someacct" has parent 7,
which has parent 4,
which *then* has parent 0.
If that's the case, then you probably will
have to create a sort table.
One method for this was illustrated by Rob Volk:
http://www.sqlteam.com/item.asp?ItemID=8866
Please respond back if that is what you need
and you need further assistance.
good luck,
gary
:
It's not what I wanted again.
If you have QuickBooks then look at its Accounting List. That list
represents values in the order I want and all values are stored in a
single table
vovan
sorry, previous response missing an ending ")"
SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY
IIF([ParentAccountID=0,
Format([AccountID],"00"),
Format([ParentAccountID],"00") & Format([AccountID],"00"));
:
:
Unfortunately the format I used to write my question was changed by
outlook
express and now it's hard to see the columns.
There are 3 columns AccountID, AccountName, ParentAccountID and 7
rows with
values
vovan

I have a table with Accounts, let's call it Account.
It has the following fields:
AccountID
AccountName
ParentAccountID
Data in the table:
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
I need to write SELECT which will return data in the following
format:
AccountID AccountName ParentAccountID
1 Checking 0
2 Savings 0
3 Expenses 0
5 Travel 3
4 Fixed Assets 0
6 Computers 4
7 Equipment 4
How do I do that?
Thank you
vovan
Let me guess.
SELECT Account.AccountID, Account.AccountName,
Account.ParentAccountID
FROM Account
ORDER BY Format([Account]![ParentAccountID],"00") &
Format([Account]!
[AccountID],"00");
Hi Ko Zaw,
I could be wrong, but I think using your format
vovan would want
SELECT Account.AccountID, Account.AccountName,
Account.ParentAccountID
FROM Account
ORDER BY
IIF([ParentAccountID=0,
Format([AccountID],"00"),
Format([ParentAccountID],"00") & Format([AccountID],"00");
Click to expand...
Click to expand...
Click to expand...

Hi every body!,

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.

SELECT Account.AccountID, Account.AccountName, Account.ParentAccountID
FROM Account
ORDER BY Format([Account]![ParentAccountID],"00") & Format([Account]!
[AccountID],"00");

dear vovan, pleased respond your result.
_________________________________
 

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

WCG Stats Tuesday 15 August 2023 5
WCG Stats Wednesday 19 July 2023 4
WCG Stats Wednesday 16 August 2023 3
WCG Stats Saturday 19 August 2023 3
WCG Stats Sunday 17 September 2023 3
WCG Stats Friday 25 August 2023 2
WCG Stats Friday 21 July 2023 11
WCG Stats Thursday 17 August 2023 3

Top