Join with extra conditionsUpload ImagesLink James Swanson 21 days 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 21 days 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 20 days 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 20 days 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 20 days ago
I fixed it by creating a query with the correct syntax and then using that query as the record source.
WHEW!
Sorry, only students may add comments.
Click here for more
information on how you can set up an account.
