Hello all,
I have a question about Access 2007, hope you can help me out. Here it is:
The “Customers” table:
ID Name Parent
1 A
2 B
3 C 5
4 D 1
5 E
Now I want to make a recordset look like this:
ID Name Parent
1 A
4 D 1
2 B
5 E
3 C 5
How can I do that in Access 2007?
Thanks in advance. 
That works, but I don’t understand the expression. Can you explain it?
The logic is: order the child right below it’s parent.
yes, if you show me the exact query you’re running
ORDER BY COALESCE(parent,id),id
this works only if you’re using NULL in the parent column to represent no parent
oh, and you might need to use the Access NV function instead of COALESCE
Can you help me with another logic: order by name and it’s child right below?
I got the error “Undefined function ‘COALESCE’ in expression”.
What is Access NV function?
What is the logic for the new resultset? Why did you order the records that way?
Normally to indicate in which order the results should be returned you use ORDER BY in your query, but I don’t see the logic of the order in your preferred results.
omg, typo
i meant to say NZ function
i think da manual would do a better job of it
try this – http://office.microsoft.com/en-ca/access/HA012288901033.aspx