Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Oct 2001
    Location
    Upper NY State, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Self Join Northwind (Access 2002)

    I have a question regarding the self join on the employees table in Northwind where the employeeID is joined to the ReportsTo field to show who the employee reports to. I need some ideas on how to take it one or more steps further since the self join only works one level up or down.

    If I select a upper level manager, I need to see how many supervisors report to him and then list the employees who work for the supervisors. Same thing with the Manager's boss to take it even one level higher.

    I think this is kind of a recursive scenario. I am pretty good at vba and entertain any ideas you could pass my way.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Self Join Northwind (Access 2002)

    If it stays limited to three or four levels, you could get by with two or three self-joins: add one or two extra copies of the table to the query.

    If you want to be flexible, the standard Access ways of presenting data are too limited. The treeview control (the left hand part of Windows Explorer) is ideal for displaying a structure with varying levels. There are some examples of using the treeview control in this forum, you can search for them. Feel free to post back if you have questions.

  3. #3
    Lounger
    Join Date
    Oct 2001
    Location
    Upper NY State, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Self Join Northwind (Access 2002)

    Hans,
    Thank you for an idea which never crossed my limited mind. I see Access in this instance to be limited. I also have SQL Server 2000 available to offer as a solution. I am very new to Transact SQL, but know I may be able to do something recursive or to call a sub-procedure.

    I did not see any support for SQL Server on this site. Do you have any recommendations?

    I think I could do this in VBA, but I really want to try to develop a solution using best practices instead of my traditional "hack until I get something that works."

    Thank you and CHEERS!

    <img src=/S/coffeetime.gif border=0 alt=coffeetime width=32 height=48>

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Self Join Northwind (Access 2002)

    We do some SQL Server support, especially as it relates to using Access as a front-end either with ODBC linked tables or when using an ADP format. Unfortunately, SQL Server has most of the same restrictions as Access in this situation - the difference is in the programming capability, and Transact SQL tends to drive VB/VBA and C programers up the wall with it's limited flow control syntax. I work in both, and I would choose VBA in a heart-beat for this kind of project. Perhaps however, it should also depend on how many records are involved, and how deep you want to go. Up to 3 or 4 levels can be handled by a query with multiple self-joins, or queries based on queries that use self-joins. Beyond that it can hairy in any event - one of the major challenges is figuring out how you display that sort of data - and Hans' suggestion of a treeview is a good one, though the MS one runs out of gas pretty quickly with lots of records or lots of subnodes. If you have either of those situations, look at some of the ActiveX third-party treeview controls.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •