Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am using Access 2007 on Windows Vista.
    I have a query with two tables in it: T_Employers and T_Worksites.
    The tables are joined by the field EmployerID.
    In the data, some employers are linked to worksites and some are not.
    No matter which join property I select (1, 2 or 3), only the employers that are linked to worksites show in the query results. It seems that in earlier versions of Access if I selected Join Property 2 "Include ALL records from 'T_Employer' and only those records from 'T_Worksites' where the joined fields are equal" that all the employers would show, regardless of whether or not they had worksites linked to them. How can I get this to occur in Access 2007?

    Thank you,
    -cynthia

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    The situation you describe should work the same in 2007 as it did in earlier versions. In order to help, we really need to see the SQL String so we can see what the query looks like. You should be able to view your query as a SQL String by choosing that view, and the paste it into a new post in this thread.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here is the string from the sequel view

    SELECT T_Employer.Employer, T_Worksite.Worksite
    FROM T_Employer RIGHT JOIN T_Worksite ON T_Employer.EmployerID = T_Worksite.EmployerID;

    I selected the join properties by right clicking on the join in design view. But even if I save it, it doesn't seem to change anything that shows in Squl view.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Oops. I misspoke. It changes from Right Join to Left Join, if I change from Property 1 to Property 2, but the data that shows in the query results doesn't change.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I think I have solved the problem. It turns out that the problem was not with the query, but was with the form that the query was embedded in. I need to work out a requery function for when something gets added. Thank you for your assistance - working the problem through to post, and responding to Wendell's question helped me see more of the details.
    NOT SOLVED - SEE ADDTIONAL POSTS

  6. #6
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts
    However - it still does not work in the table lookup field that I need it in. The choices all appear, but as soon as the curser leaves the lookup field, if the option selected was a company without a worksite linked to it, the data disappears from the field. Perhaps I need a totally different approach? I have attached the three relevant tables. I will try to upload a little database with the tables in question in a few minutes.

  7. #7
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here is a database with the tables in question. You can see the behavior in the T_EmploymentDetails table.
    [attachment=89431:JoinProperties.zip]
    Attached Files Attached Files

  8. #8
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    It is never going to work like this.
    The combo box is trying to store as far as I can see the Worksite ID.
    It is bound to Column 1 of the query which is worksite ID
    But since the query is an outer join, then Employee B has NO associated worksites, so the Column 1 worksite ID is NULL.
    When you choose the Employee B entry in the current list, it puts the null from column 1 into the worksite ID in the associated Employment Details table, and this is what it shows.
    So it can never look up the associated Employer for that site because there isn't one, it will always show Blank.
    You need to make the Combo box show a list of actual Worksites so it can pick up a valid ID.
    However, you cannot get it to show a list of worksites and Employees with No Worksites, but still put a valid worksite ID in.
    Hope that makes some sense.
    Andrew

  9. #9
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have been off this project for a month - just now getting back to it.

    Thank you Andrew, for your explanation. That makes perfect sense.
    I will solve the problem by requiring that each employer have at least one worsite entered, even if that worksite is "unknown". We have a lot of members where we know only the employer, not the worksite, anyway, so that will solve that problem as well.

    Thank you again.
    -cynthia

Posting Permissions

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