Problem with my SQL query in Access

[tyleri]

Hi , I am having problems with my SQL query in MS Access - I am running pass-through queries to an SQL Server... so I figured this is the best forum to post this question since I'm running SQL directly off the sql server.

Can you guys figure out what is wrong with my query - I am trying to omit duplicates from the table vw_olp_userdetail but I get error messages regarding the Tmp.login_id field when I run the queries... here is my code:

SELECT DISTINCT vw_olp_userdetail.login_id, vw_olp_userdetail.first_name, vw_olp_userdetail.last_name, Business_Services_Provider.uid_id, Business_Services_Provider.login_id, DPS_USER.login_id, DPS_USER.user_last_nm

FROM (vw_olp_userdetail LEFT JOIN Business_Services_Provider ON vw_olp_userdetail.login_id = Business_Services_Provider.uid_id) LEFT JOIN [DPS_USER] ON bus_services_provider.login_id= [DPS_USER].login_id

WHERE [vw_olp_userdetail].[login_id] In (SELECT [login_id] FROM [vw_olp_userdetail] As Tmp

GROUP BY [vw_olp_userdetail].[login_id] HAVING Count(*) = 1);

Thanks all for the help!

 

[tyleri]

Here is updated version of my code there was an error that I fixed, but still same error message...

SELECT DISTINCT vw_olp_userdetail.login_id, vw_olp_userdetail.first_name, vw_olp_userdetail.last_name, Business_Services_Provider.uid_id, Business_Services_Provider.login_id, DPS_USER.login_id, DPS_USER.user_last_nm

FROM (vw_olp_userdetail LEFT JOIN Business_Services_Provider ON vw_olp_userdetail.login_id = Business_Services_Provider.uid_id) LEFT JOIN [DPS_USER] ON business_services_provider.login_id= [DPS_USER].login_id

WHERE [vw_olp_userdetail].[login_id] In ((SELECT [login_id] FROM [vw_olp_userdetail] As Tmp

GROUP BY [vw_olp_userdetail].[login_id] HAVING Count(*) = 1));

 

[tyleri]

Forget it, I need to approach the problem a different way, I solved my question here

 

[jballum]

tyleri,

Change the Select statement in your where clause to the following:

WHERE [vw_olp_userdetail].[login_id] In
((SELECT
Tmp.[login_id] /* added qualifier to Tmp */
FROM
[vw_olp_userdetail] As Tmp
GROUP BY
Tmp.[login_id] /* Change qualifier to Tmp */
HAVING
Count(*) = 1))

I hope this solves your problem.

JB