Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating a 'self-join' (2000)

    I have a staff table and want a field to indicate their manager, who is someone else in the same table. The Lookup Wizard doesn't show me the staff table as an option. However, if I manually create the lookup details, building a Row Source, then this works. Why doesn't the Wizard give me this option? Is there something wrong with the way I am creating this? Should I, instead, build a query with the Staff table occurring twice in the table area? Andy.

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

    Re: Creating a 'self-join' (2000)

    The Wizard is not omnipotent; I guess this is a deliberate limitation. You don't really need the lookup in the table itself, you can create a combo box on a form to look up the manager.

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a 'self-join' (2000)

    Wizard.. Omnipotent.. I like that.?! I realised that I could create a combo-box on a form, but books tend to create a self-join by creating a query and then adding a second copy of the original table and creating a join between them. I'm curious why they do this and whether there are any pitfalls in the way I am doing it?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Creating a 'self-join' (2000)

    Putting another instance of a table in the query is the standard way to create a self join. In the query engine/Jet SQL, as it exists through version 2002, it's the only way to create a self-join in the grid, by using an alias of the table for the other side of the join.
    Charlotte

Posting Permissions

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