I'm not sure I'm going about this in the right way so any suggestions would be appreciated.
I've got a table that stores parent child relationships. The table looks like this:
primaryID, parentID, Name
I've set up a form that inserts a 0 in the parent ID if there is no parent, but if you've already created a parent you can choose the parent name and the parent's primary ID is placed in the Parent ID column. Think of it as a family tree.
If the family tree was Adam as the first parent Cane as the first child of Adam and Able as the second child of Adam but we also had a first parent (not related to Bob, and a second first parent of John, not related to Adam or Bob and we added a first child of John named Dave the data would look like this:
1, 0, Adam
2, 1, Cane
3, 1, Able
3, 0, Bob
4, 0, John
5, 4, Dave
Adding a child Jill to Cane would give us record ID 6 like this:
6, 2, Jill
What I'd like to do is to generate a sorted list from this data that looks like this:
If anyone has any ideas of how to create a sort that will accomplish this I'd love to see it. I've spent the last 2 hours trying to do this with 1 query. The only way I can make it happen is with 3 and it's a real mess.