• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

sorting hierarchically

folks

I have a parent child structure in an access table

asset      parent
ford      car
mondeo      ford
car      
turbo      mondeo


how do i sort it from top down like so

asset      parent
car      
ford      car
mondeo      ford
turbo      mondeo

all help will do
0
rutgermons
Asked:
rutgermons
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Where are you sorting this? If it's in a query, then just set the Sort on the "parent" field to Asc or Desc ... in a table Datasheet view, use the toolbar's Sort commands (the AZ and ZA buttons) ... in a form, you can set the SortOrder in the Properties dialog ...
0
 
rutgermonsAuthor Commented:
not that simple

i have a hieirachy that doesnt display from the top bottom down

ive hear of a NZ function but am not sur ehow it works with my data

could u advise?

r
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Hmmm ... I see what you mean now ... I'm not sure how this could be done. Basically, you need to discover the "top" (which would be the record without a "parent", I'd presume), then move down the list and find the record which has that (asset) listed in the Parent column ... then take that (asset) value, and find the record with that value in teh Parent column ... etc etc ...

I'd be very surprised if you could do this directly in the interface (i.e. in a query view) but I've been wrong before ... you could do something like this in VBA, and then fill a temporary table, then present a query based on that table ... or perhaps in a Treeview ...
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
harfangCommented:
I hear Oracle, and perhaps some other SQL variants, have special query options to work with such a table, but not JetSQL (or MS-SQL).

You will need some helper functions. The first is often the "path" function, returning the "full path" of each asset. It can be as simple as this (assuming a name for your table):

Function BuildPath(Asset, Optional Trailing = Null)
    If IsNull(Asset) Then
        BuildPath = Trailing
    Else
        BuildPath = BuildPath( _
            DLookup("parent", "tblHierarchy", "asset='" & Asset & "'"), _
            Asset & "/" + Trailing)
    End If
End Function

You can then use a query like this:

SELECT *, BuildPath(asset) As Path
FROM tblHierarchy
ORDER BY BuildPath(asset)

That works on the example provided, but is painfully slow with real case data.

A much faster version of this function needs a static table-type recordset, in order to use the .Seek method on an index. I now assume that the table has an index for the field "asset", called "ndxAssets" to avoid any confusion.

Function FastPath(ByVal Asset, Optional Trailing = Null)

    Static srecH As DAO.Recordset
   
    If srecH Is Nothing Then
        Set srecH = CurrentDb.OpenRecordset("tblHierarchy", dbOpenTable)
        srecH.Index = "ndxAssets"
    End If
   
    If IsNull(Asset) Then
        FastPath = Trailing
    Else
        srecH.Seek "=", Asset
        Debug.Assert Not srecH.NoMatch
        FastPath = FastPath(srecH!Parent.value, Asset & "/" + Trailing)
    End If
   
End Function

This is the first step. You need to add some error management, especially if your data might have relational cycles. This would cause an infinite recursion, ending when VB runs out of stack space (which generates a run-time error).

Note that static table-type recordsets open another can of worms, especially in conjunction with FE/BE architecture where you change the BE dynamically, and more generally when designing. Make frequent use of the "code reset" button to release the table, as it will be locked for editing after the function is run for the first time.

This is to say that although I use this type of functions extensively with excellent results in terms of speed, they are a bit more complex.

Good luck!
(°v°)
0
 
Patrick MatthewsCommented:
Hello Scott and Markus,

This appears to be a continuation of:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_22711407.html

Regards,

Patrick
0
 
harfangCommented:
Ouch! Relational data in Excel is one of my nightmares (had too many clients insisting upon Excel in similar cases...) although I love Excel for many other things.

Note that the first version (without index) is quite doable in Excel VBA. But it won't be fast.

(°v°)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now