Join with extra conditions - TechHelp - Computer Learning Zone


	Back to TechHelp Comments List

Upload Images @Reply Bookmark Link Email

Join with extra conditions

James Swanson

2 years ago

The following appears to be correct with generic SQL syntax, however, I get an error "JOIN expression not supported" when I try to use it in Access:

SELECT Contact.*, Properties.HouseNumber & " " & Trim(Properties.DirectionPrefix & " " & Properties.Street & " " & Properties.StreetType & " " & Properties.DirectionSuffix) & Trim(" " & Properties.SuiteNo) AS Addr1, Trim(Properties.City & ", " & Properties.State & " " & Properties.Zip) AS Addr2
FROM Contact LEFT JOIN Properties
ON Contact.Key = Properties.Contact_Key
AND "primary"=PropertyType

Any suggestions?? I only want the client's PRIMARY property to show up.

Kevin Robertson @Reply

2 years ago

The PropertyType needs to be a WHERE condition.

SELECT Contact.*, Properties.HouseNumber & " " & Trim(Properties.DirectionPrefix & " " & Properties.Street & " " & Properties.StreetType & " " & Properties.DirectionSuffix) & Trim(" " & Properties.SuiteNo) AS Addr1, Trim(Properties.City & ", " & Properties.State & "  " & Properties.Zip) AS Addr2, Properties.PropertyType

FROM Contact LEFT JOIN Properties ON Contact.Key = Properties.Contact_Key

WHERE Properties.PropertyType="Primary";

In the Query designer you can simply add "Primary" to the criteria of the PropertyType column.

James Swanson OP @Reply

2 years ago

That is correct and how I originally wrote it. However, this way I do not get any of the cases where a contact does not have a "primary" property. I don't always have an address for every contact. This is standard SQL and should work.

I tried something that fixed it. Access needs to group the condition with additional syntax: "FROM Contact LEFT JOIN Properties
ON (Contact.Key = Properties.Contact_Key
AND "primary"=properties.PropertyType)"

Adding the "()" helps the parser to interpret both conditions as part of the JOIN not part of the SELECT.

Thanks for your help.

James Swanson OP @Reply

2 years ago

OK. That's weird. When I make the change in my form and test it by executing it I get all the records as expected with NULL address info on a few records. Then, when I save it, close it, and reopen it, I get the old error message. Access won't even let me edit by clicking on the three dots in Record Source. I can review and edit within that one line and discover that Access has removed the "()" so it looks like this: "FROM Contact LEFT JOIN Properties ON Contact.Key = Properties.Contact_Key
AND "primary"=properties.PropertyType".

DO you have any suggestions how I might get this reported to Microsoft as a bug?

	James Swanson OP @Reply 2 years ago
I fixed it by creating a query with the correct syntax and then using that query as the record source. WHEW!

This thread is now CLOSED. If you wish to comment, start a NEW discussion in TechHelp.