Query to join records according to "hierarchy"

My table is something like this:

Fields: Name;   Group
           John;       1-1
           Carl;        1-1
           Brian;      1-2
           Charles;   2-1
           Eddie;      2-2
           Carmen;   3-1
           Ellen;       3-2
           ......and so on

What I need is to combine the field Name in group 1-1 to 1-2, 2-2,
3-2. Then 2-1 with 2-2, 3-2. Then 3-1 with 3-2.

Is this possible? If so, how can I do it?

The results from the query would be something like this:

Name A          Name B        Name C      Name D
John(1-1)        Brian(1-2)      Eddie(2-2)   Ellen(3-2)
Carl(1-1)         Brian(1-2)      Eddie(2-2)   Ellen(3-2)
Charles(2-1)    Eddie(2-2)     Ellen(3-2)
Carmen(3-1)    Ellen(3-2)

Basically every name in group1-1 can be together with group1-2
group2-2 and group3-3. Every name in group 2-1 can only be together
with group2-2 and group3-2... and group 3-1 can only be together with
3-2

thanks for the help.

0
sergio
7/4/2007 10:19:16 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
858 Views

Similar Articles

[PageSpeed] 50

The first stage is to put the table twice into the query design window. Do 
not join them.

something like yourtable, and yourtable_1

as fields pick yourtable.name , yourtable.name_1.name , yourtable_1.group

where yourtable_1.group > yourtable.group

order by yourtable_1.group ascending.

Use the output of that query as the input to a crosstab to get the table 
format that you desire.



<sergio.pringle@gmail.com> wrote in message 
news:1183587556.390345.53040@m36g2000hse.googlegroups.com...
> My table is something like this:
>
> Fields: Name;   Group
>           John;       1-1
>           Carl;        1-1
>           Brian;      1-2
>           Charles;   2-1
>           Eddie;      2-2
>           Carmen;   3-1
>           Ellen;       3-2
>           ......and so on
>
> What I need is to combine the field Name in group 1-1 to 1-2, 2-2,
> 3-2. Then 2-1 with 2-2, 3-2. Then 3-1 with 3-2.
>
> Is this possible? If so, how can I do it?
>
> The results from the query would be something like this:
>
> Name A          Name B        Name C      Name D
> John(1-1)        Brian(1-2)      Eddie(2-2)   Ellen(3-2)
> Carl(1-1)         Brian(1-2)      Eddie(2-2)   Ellen(3-2)
> Charles(2-1)    Eddie(2-2)     Ellen(3-2)
> Carmen(3-1)    Ellen(3-2)
>
> Basically every name in group1-1 can be together with group1-2
> group2-2 and group3-3. Every name in group 2-1 can only be together
> with group2-2 and group3-2... and group 3-1 can only be together with
> 3-2
>
> thanks for the help.
> 


0
David
7/5/2007 9:49:58 AM
<sergio.pringle@gmail.com> wrote in message
news:1183587556.390345.53040@m36g2000hse.googlegroups.com...
> My table is something like this:
>
> Fields: Name;   Group
>            John;       1-1
>            Carl;        1-1
>            Brian;      1-2
>            Charles;   2-1
>            Eddie;      2-2
>            Carmen;   3-1
>            Ellen;       3-2
>            ......and so on
>
> What I need is to combine the field Name in group 1-1 to 1-2, 2-2,
> 3-2. Then 2-1 with 2-2, 3-2. Then 3-1 with 3-2.
>
> Is this possible? If so, how can I do it?
>
> The results from the query would be something like this:
>
> Name A          Name B        Name C      Name D
> John(1-1)        Brian(1-2)      Eddie(2-2)   Ellen(3-2)
> Carl(1-1)         Brian(1-2)      Eddie(2-2)   Ellen(3-2)
> Charles(2-1)    Eddie(2-2)     Ellen(3-2)
> Carmen(3-1)    Ellen(3-2)
>
> Basically every name in group1-1 can be together with group1-2
> group2-2 and group3-3. Every name in group 2-1 can only be together
> with group2-2 and group3-2... and group 3-1 can only be together with
> 3-2
>
> thanks for the help.
>

Sergio Pringle,

You are discussing the materialized path hierarchy (also called path enumeration).

This is not a simple subject.

I don't know where you can find a good MS Access-based discussion of it, but this article
is still good. (You would need to adapt the examples for MS Access.)

http://www.dbazine.com/oracle/or-articles/tropashko4

Nested sets will get you there, too, but you would need a different table schema.

Joe Celko's book, Trees and Hierarchies, has information on this, as well.  His book
points out that the Itzik Ben-Gan/Tom Moreau book, Advanced Transact-SQL for SQL Server
2000 (chapter 16), is a more extensive source of materialized path information.  (You'd
have to adapt examples from either book, as well.)


Sincerely,

Chris O.


0
Chris2
7/6/2007 2:51:40 AM
Reply:

Similar Artilces:

New usenet hierarchy proposal
Hi to all, seems to be that for all interested in further discussions, help etc. on VB Classic themes a new Usenet hierarchy should be created. Now I am no expert here, but AFAIK we need: - a naming - a primary host of the hierarchy - an ok from usenet administration Naming could be: vbclassic.en.misc vbclassic.en.com vbclassic.en.database etc and vbclassic.de.misc vbclassic.de.com vbclassic.de.datenbanken etc -- Ulrich Korndoerfer VB tips, helpers, solutions -> http://www.proSource.de/Downloads/ PS Ulrich Korndoerfer schrieb: > ... > Nami...

Organization Hierarchy Web Part
HI Does anyone know if it's possible to display the assistant in both the assistant field and the direct reports list for a manager? Currently we can only get the user to appear in one or the other depending on whether we assign a user to the assistant field or not. Many thanks, Lee Hi Lee, Having the same problem as well. Have you had any luck or do you have any updates ? Thanks "Lee Diggins" wrote: > HI > > Does anyone know if it's possible to display the assistant in both the > assistant field and the direct reports lis...

Ho to display a hierarchy of accounts
Does anyone know how I can get CRM (SFO or Web) to display Accounts in a hierarhical fassion, i.e. just like a folder structure in Windows Explorer? Lat's say I have Defense as an account, and then I have Army, Navy and AirForce. Beneath Army I have Procurement and Training. I want to be able to see these accounts displayed in a hierarchical tree, not just as a flat list as is the default. How do I do this? Thanks, John Balsillie -- John Balsillie You might want to look into a 3rd party add-on. C360 has something that shows relationships in a treeview. http://www.c360.com/Relat...

Organization Hierarchy from Employee Master Table
This is rather a unique question which came up during a discussion with one of our developers, where they want only SQL views and no stored procedure at all. Here is the problem description: I've a table that holds the Employee ID and Supervisor ID columns in it. For every employee, there is a supervisor except for one employee. Every supervisor is an employee and is stored in the same table. There is one to one relationship between employee and supervisor. I am tasked to write a query that provides an output for every employee with their rank in the company. In other words...

BP4 Requisition Approval Hierarchy tables
Hi, My client was on BP2.7 GP8 and has just upgraded to GP10. We did not upgrade BP2.7 but rather BP4.0. The problem we now have is that we do not know what the previous Requisition Approval hierarchy was. Before the install we made backups of all the old BP2.7 tables and so have access to the tables that should hold the old hierarchy. However it seems as if all the users and the approvers are held in a hexadecimal format within the tables - which makes that I cannot figure out what the hierarchy is. Please can you advise how to create a script that will extract and clearly show the ...

table hierarchy inconsistency
I have installed some new reports into my GP implementation and I get the following message when I try to run one of them (after the destination selection form). Table Hierarchy Inconsistency I do NOT get this in development - only after I install the chunk file into my test environment. Any idea where to look for this inconsistency? Don DeVeux This is usually caused by you adding a new relationship to an existing Great Plains table. Because the table is an original table (ID < 22000), it is not extracted into your chunk and neither is the new relationship. So when your report ...

Folder Hierarchy/Running SCANPST.EXE automatically
Hello Everybdy, I am using Outlook 2003 (IMAP) together with an MDaemon Mailserver. I have permanent Problems with the folder hiearchy. At least twice per day, the hierarchy is corrupted and I can see all my Tasks, Calendar items, Contacts as email, or I get a nice calendar view of my contacts. SCANPST.EXE tells me that it has found errors in the folder hierarchy like: **Beginning PST/OST recovery **Attempting to recover all top-level objects **Attempting to walk all folders !!Hierarchy Table for 122, row doesn't match sub-object: irow = 0, RowID = 2223 **Attempt...

XmlSerializer and custom hierarchy
I have a class with "flat" structure which I want to serialize using XmlSerializer. public class Settings { protected string _logFolder = null; public Settings() { } public string LogFolder { get{ return _logFolder; } } } Output XML file is looking like this : <?xml version="1.0" encoding="utf-8"?> <Settings> <LogFolder>xxxxxxxxxxxxxxxxx</LogFolder> </Settings> I want to add another level into XML nodes hierarchy, so output XML should be formatted like this: <?xml version="1.0" encodin...

Dollar based expense approval hierarchy
Hi, We would like to setup a dollar limit based expense approval hierarchy when implementing Project Accounting - Time and expense module. For example, my manager has the authority to approve expenses upto $500. Beyond that, the approval needs to be come from the VP as well. So, if I submit my expense report worth $500 or less, my expense report should be approved by my manager. If my expense report is worth more than $500, my expense report will be approved by my manager as well as his superior (which is the VP in this case). How do I set this up in Project Accounting or PDK. Tha...

How to delete deep directory hierarchy
Hello, I have a deep directory hierarchy (which was created programmatically) having a too long path name such that I cannot delete it from explorer or via DOS. I tried, rmdir /S /Q dir but it does not work. Does someone know how to delete a directory structure with too much deep descendent entries? Best Regards, Mario Can you drag and drop one of the nested folders to a root folder, which will give it a shorter path, then delete it there? Mich "M�rio" <mario.luis.guimaraes@gmail.com> wrote in message news:0be9a762-8eef-4500-ac13-cf177f49259f@g7g20...

approval hierarchy
If a client is relying on roles to determine the hierarchy in requisition management it is double work for they to also input the user ID, so that they can create requisitions. Roles are a much quicker way to maintenace the hierarchy tree, adding users IDs or managing just by user ID requires reqular maintnc. when a user changes roles or is no longer at the institution. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you...

How to manage windows hierarchy of 2 processes? #2
I have 2 processes. One is MFC with main frame, pop-up dialogs and messages. The second is based on a graphical engine (non-MFC) that creates a window with graphical data. I need to combine both displays. The user shouldn't notice that there is a "mix". For example, if the graphical window is a top window, when the user opens a dialog from the MFC app it will be below the graphical window. My first idea was to set the graphical window as a pop-up and the owner is the MFC frame. This is the behavior that I need. But then if the MFC app is not responding, the graphical window is ...

Hierarchy in excel
Hello Friends I am new member here ... right now i am struck with one prob in excel We have 2 structures 1 st product structure & another is locatio structure In product Structure all locations are clustered together eg Prods are : A, B C. & locations are 1,2,3 etc Prod Structure Location structwould be Total Total A 1 2 B 3 2 B A 1 B 2 3 3 C ...

Demand Planner should have more flexible hierarchy.
Usually, both of market and product hierarchies are not fixed and forecast and real sales data is used for several years. A lot of customers might want to use DP's hierarchy more flexible. The current version doesn't have flexible hierarchy structure. Here is a response from an MBS support staff. ---<<Response>>(Begin)--- "Here is some information on changing hierarchies from consulting. The changing the hierarchies after a “good” prototype or a live system has been developed are a straight disaster. Everything in DP is depending on the internal codes assi...

Synchronizing hierarchy error messages when importing .pst file
Am attempting to import a .pst file from work to home PC. Address book transfers OK but email messages do not - multiple "Synchronizing hierarchy....Terminated in error" messages recorded. "RBLonline" <RBLonline@discussions.microsoft.com> wrote in message news:0C0136A8-FD9E-4C79-B371-23ACEA8D94B4@microsoft.com... > Am attempting to import a .pst file from work to home PC. Address book > transfers OK but email messages do not - multiple "Synchronizing > hierarchy....Terminated in error" messages recorded. Never import a PST. Open it ...

Error replicating public folder hierarchy
I'm getting following error replication public folder hierarchy between two Exchange 2003 servers. The source of the replica (in "public folder -> Send Hierarchy") is on windows 2003 and destination on windows 2000. I've enabled diagnostic logging in public folder on both the server. Error -2147221233 reading property 0x30080040 on object type tbtFolder from database "StrGrp1\Public Folder". Entry is logged twice. I tried rebooting AD DCs, Exchange servers. Deleted public folders, recreated new one. Any idea? ...

how do I create supervisor hierarchy with the access table
I am trying to create a supervisor roll up table with the employee data information. My table currently has employee id, employee name, supervisor id and supervisor name. I am trying to create an organizational roll up up to 4 levels. E.g. Employee John Doe is reporting to Jim Smith who reports to Steve Williams who reports to Jack Martin. I am trying to create a table such as below through a query and not having much success. Any help will be much appreciated. Employee Name Supervior 1 Supervisor 2 Supervisor 3 John Doe Jim Smith St...

Hierarchy and Organizational Charts
We'd like to understand the hierarchy of individuals and roles within a given account (actually, ideally we'd have an org chart that could be visually generated). How can this be done? I haven't used their product, but I came across this add-on that provides the ability to make org charts within CRM entities: http://www.salescentric.com/glance.html There are probably others out there as well. -- Matt Wittemann, CRM MVP http://icu-mscrm.blogspot.com "Jasonh" wrote: > We'd like to understand the hierarchy of individuals and roles within a given > account...

hierarchy in one table
I have a single table that has an employee and the manager they report to. This table consists of all employees regardless of management level. Since I have this table with the employee/manager relationship. How can I query to show me the hierarchy of a high level manager. For example a GM has four directors who each have two managers and the managers have 6 employees. Iwould like to get a query that shows that this GM has a total of 48 persons underneath him. Jim, My guess is that you are going to need to write a recursive function that you pass the EmployerID to. In that fun...

Need help for setting up an approval hierarchy for Project Manager
Hi, In our organization, we have Project Managers who reports to Project Coordinator We want to set an approval hierarchy where if Project Manager changes Baseline in any project then once he saves, it should go for approval to Project Coordinator. We have control in EPM for Baselining the projects as, Project Managers can set baseline No: 6 to 10 and Project Coordinator can set baseline No: 0-5 I think this will give you some direction for the solution. Can you please suggest us the best way to do the same? If you need any further details please revert back Th...

Hierarchy in Internet Newsgroup view
Is there a way to have the messages grouped into a hierarchy based on = the threads as it does in Outlook Express? All I can get is by = conversation and the replies to that conversation are not organized into = threads. Thanks. =20 ...

How to change Public Folder hierarchy Server?
Is it possible move public folder "Folder" place holder from one server to another? Here is the situation: when you install first Exchange 2003 server in Ex5.5 org, the first server will hold the PF hierarchy role. How do we change this role to a new server? I still have Ex 5.5 servers in the org. Thanks in advance Not exactly sure I understand your question, but a public folder hierarchy is created and must exist on any Ex server that has a pf database this is by design. Can you explain what you're trying to do or accomplish? Thanks James Chong MCSE + Messaging, MCT...

How to handle hierarchy processing like this
Hello All I have a cost center hierarchy which is like the following: ROOT | |-NODEA | | | |-NODEB | |-NODEC | |-NODED | |-NODEF | |-NODEG | |-NODEH |-NODEI I get the above cost center hierarchy into a spreadsheet in the following format. A B C D E <== Spreadsheet columns ========================================================= ROOT NODEA NODEB NODEC NODED ...

How to construct a hierarchy from a list? (Access 2003)
Greetings, Using Access 2003, my objective is to turn a list like this: Asset ID | Parent Asset ID 1 | NULL 2 | 1 3 | 2 4 | 3 Etc... Into a table like this: Asset ID | Parent 1 | Parent 2 | Parent 3 | Etc... 1 | NULL | NULL | NULL | 2 | 1 | NULL | NULL | 3 | 2 | 1 | NULL | 4 | 3 | 2 | 1 | Any suggestions how to achieve this objective are greatly appreciated. A similar question was posted in the Acces...

hierarchy chart
i'm barely learning the basics for C+ and was asked to develop an online shopping application. I am to develope a hieracry chart containing 3 main variables and then explain if there are rehusable conponents. What I turned in was unapproved, can anyone explain why or what I should do to make it acceptable? Variables Description Type Appl_age /*Applicant’s age*/ integer Appl_cc /*Applicant’s cc*/ boolean Appl_loc /*Applicant’s country*/ boolean Age is definitely a variable in Online Shopping due to finance purposes. Immediately tied in with age is payment method and cre...