Hi UA,

I am looking for a jump start on a sorting problem. Here are some details.

I have a tblOrg that holds my organizational data. It trackes the level an Org is at called OrgLevel. It has a code for the Organization called Org and it also has a ParentID, which is the Org that it reports up to.

I also have a small routine that creates the pair lists between the top level Org down to the bottom level Org. These pairs are stored in a table called tblOrgDesc. This table maps out my hierarchy.

Between the two, I create reports that allow me to pick an Org and all the subordinates for that Org are included in that report.

My problem is that the Orgs print out sorted on Org and not in the hierarchical fashion in which they are related from top to bottom of the organization level.

It seems to me that I am really close on this but I can't seem to see the solution.

How can I get my query to sort in the hierarchical fashion in which the Orgs are related to each other using the table structures above?

Thanks.

Hi
Use the report Sorting and Grouping feature.
In report design view try sorting and grouping from the view menu.

You can create group headers and sort by other fields on your report, you should find the combination you need.

If you need help post your database and one of us can take a look.

hth
Chris

Chris,

The database is 70 megs big and I am not really at liberty to send the data off site.

Also, I need to get the sorting done in a query rather than through the report sorting features in order to do some other data manipulation.

Hi Bucca

Can you make a sample database with a small amount of false data in the relevant tables and post it along with the query?

Or post the sql from your query..........someone may be able to provide an answer for you from studying your query sql.

ps....
did you turn on the totals row to access the group by in the query design grid (view>totals)


hth
Chris


Edited by: ChrisLeClark on Tue Jun 7 14:46:45 EDT 2005.

Chris,

Here is the query I am working with (with all the other fields as well). The totals functionalily is not the issue. It is getting the data to sort hierarchically. What happens now is that they all sort by the organizaiton level 5,6,7 rahter than some 5,6 and then some 5,6,7 and then back to 5,6 as they would in a hierarchical fashion. What's works great with my query is that if I pick an Org say 3 levels down - it and all of it's subordiantes only display. So if I picked the top level Org - it and all of the Orgs (a.k.a. my entire database) would be displayed. Alas it would be ordered by the level or more typically by the Org field. I just added the OrgLevel to illustrate my levels versus hierarchy dilemena. Agian grouping and totals does not get the results I am after.

Let's see what my SQL will offer in way of some more detail.

==================

SELECT tblOrg_1.OrgLevel, tblOrg_1.Org, tblOrg_1.OrgName, tblFac.Fac, tblFlr.Flr, tblFlr.FlrSortSeq, tblSpa.Room, tblUse.UseName, tblSpaC1.SpaC1Name, tblSpa.SpaDwg, tblSpa.SpaceArea, tblOrgAsgn.OrgPer, ([tblOrgAsgn].[OrgPer]*[tblSpa].[SpaceArea]/100) AS AsgnArea, tblSpa.SpaStatCnt, tblSpa.SpaOccCnt, tblOrg.Org
FROM (tblMjUse INNER JOIN tblUse ON tblMjUse.MjUseID = tblUse.MjUseID) INNER JOIN ((tblSpaC1 INNER JOIN ((tblFac INNER JOIN tblFlr ON tblFac.FacID = tblFlr.FacID) INNER JOIN tblSpa ON tblFlr.FlrID = tblSpa.FlrID) ON tblSpaC1.SpaC1 = tblSpa.SpaC1) INNER JOIN (((tblOrgDesc INNER JOIN tblOrg AS tblOrg_1 ON tblOrgDesc.DescID = tblOrg_1.OrgID) INNER JOIN tblOrg ON tblOrgDesc.OrgID = tblOrg.OrgID) INNER JOIN tblOrgAsgn ON tblOrg_1.OrgID = tblOrgAsgn.OrgID) ON tblSpa.SpaID = tblOrgAsgn.SpaID) ON tblUse.UseID = tblSpa.UseID
WHERE (((tblOrg.Org)="cas") AND ((tblFac.FacStat)="ACTIVE"))
ORDER BY tblOrg_1.OrgName, tblFac.Fac, tblFlr.FlrSortSeq, tblSpa.Room;

==================

Hi Bucca
There is too much going on in your query for me to give you a solution.
If you can manage to send some sample database then I could play around with it and check it out otherwise someone far more experienced than me may be able to offer a solution for you.

One thing I would try though is a make table query.
Adapt the query to a make table query so that when you try to do the sorting you are only working with 1 table.

Using the new table then make a new totals query and group and sort by org level and further sort by subordinates.

Sorry thats all I can come up with.

Chris

Chris,

Thanks anyway, maybe someone else will lend assistance.

Here is the basic SQL that is the issue.

SELECT tblOrg_1.OrgLevel, tblOrg_1.OrgName, tblOrg.Org
FROM tblOrg AS tblOrg_1 INNER JOIN (tblOrg INNER JOIN tblOrgDesc ON tblOrg.OrgID = tblOrgDesc.OrgID) ON tblOrg_1.OrgID = tblOrgDesc.DescID
WHERE (((tblOrg.Org)="cas"))
ORDER BY tblOrg_1.OrgLevel;

If I filter for a 5th level Org of GSE I get a result set of:

Organization Level Organization Name Organization
5 Graduate School of Education GSE
6 Methods of Inquiry Program GSE
6 Learning and Instruction GSE
6 Dean of Graduate School of Education GSE
6 Counseling, School and Educational Psychology GSE
6 Educational Leadership and Policy GSE
7 Region II Rehabilitation Continuing Education Program GSE
7 Urban Education Institute GSE
7 Teacher Education Institute GSE
7 Reading Center GSE
7 English Language Institute GSE
7 Gifted Math Program GSE
7 Early Childhood Research Center GSE
7 Center for Comparative and Global Studies in Education GSE

However organizationally, some of the 7 levels belong under some of the 6 level Orgs. I am looking to get the query to sort them in hierarchical manner.

Hi Bucca
I feel you need some form of hierachyID in your result set so maybe use this query with your tblOrgDesc again, joined by the appropriate field to add your hierachy field i.e. ParentID, plus add OrgID, Org Name, Organization.

Otherwise good luck in finding a solution.

Your post has dropped off the most recent list so you might want to post again but this time in the query forum because it might be better viewed in there.

Chris

Edited by: ChrisLeClark on Tue Jun 7 17:26:56 EDT 2005.