Trouble with Join Properties

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Here is a database with the tables in question. You can see the behavior in the T_EmploymentDetails table.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote