Thread: Is Null not working! Tried Outer Join and that's not working either...

I've got 2 tables linked on ownerID. I need ALL ownerID's to show up in my query table and if UserID in my sec user marketer table doesnt exist I still want it to show up with the ownerID and ownername from the other table with that information exists. Here's my SQL, using Access 07

SELECT DISTINCT dbo_tbl_CO_GB_detail.OwnerID, dbo_tbl_CO_GB_detail.OwnerName, dbo_tbl_sec_user_marketer.UserID, dbo_tbl_sec_user_marketer.MarketID
FROM dbo_tbl_sec_user_marketer RIGHT JOIN dbo_tbl_CO_GB_detail ON dbo_tbl_sec_user_marketer.OwnerID = dbo_tbl_CO_GB_detail.OwnerID
GROUP BY dbo_tbl_CO_GB_detail.OwnerID, dbo_tbl_CO_GB_detail.OwnerName, dbo_tbl_sec_user_marketer.UserID, dbo_tbl_sec_user_marketer.MarketID
HAVING (((dbo_tbl_CO_GB_detail.OwnerID)=2327) AND ((dbo_tbl_sec_user_marketer.MarketID)=1070));

In the particular case above of ownerID 2327, the ownerid and ownername exists in CO GB detail but does NOT exist in sec user marketer so I'd expect to see everything populate except the UserID


FYI - I created the query in Design view

You are filtering (HAVING) on either side of your join in the same query. This is likely causing the problem. Try using two queries and separate the filter. i.e. filter "marketer" in one, then join that query to the original "detail" table for the second.

If you've written the criteria into the first query (not a parameter query), you shouldn't have to open it again.

Yup.

Cheers,