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