troubleshooting Question

Query needed to find same initials but different names.

Avatar of bfuchs
bfuchsFlag for United States of America asked on
DatabasesMicrosoft AccessMicrosoft SQL ServerSQL
13 Comments1 Solution116 ViewsLast Modified:
Hi Experts,

I have a table named Skilled_Nursing_Visit_Note having following fields

Client_Last_Name
Client_First_Name

Would like to have a query that will display all unique records that initials are the same but names are different.
Initials are first letter of each field.

Tried the following (In Access) but does not show expected results.

SELECT DISTINCT Skilled_Nursing_Visit_Note.Client_Last_Name, Skilled_Nursing_Visit_Note.Client_First_Name, Skilled_Nursing_Visit_Note_1.Client_Last_Name, Skilled_Nursing_Visit_Note_1.Client_First_Name, Trim(Mid([Skilled_Nursing_Visit_Note].[Client_Last_Name],1,1)) AS Expr1, Trim(Mid([Skilled_Nursing_Visit_Note].[Client_First_Name],1,1)) AS Expr2
FROM Skilled_Nursing_Visit_Note, Skilled_Nursing_Visit_Note AS Skilled_Nursing_Visit_Note_1
WHERE (((Skilled_Nursing_Visit_Note.Client_Last_Name)<>[Skilled_Nursing_Visit_Note_1].[client_last_name]) AND ((Trim(Mid([Skilled_Nursing_Visit_Note].[Client_Last_Name],1,1)))=Trim(Mid([Skilled_Nursing_Visit_Note_1].[Client_Last_Name],1,1))) AND ((Trim(Mid([Skilled_Nursing_Visit_Note].[Client_First_Name],1,1)))=Trim(Mid([Skilled_Nursing_Visit_Note_1].[Client_First_Name],1,1)))) OR (((Skilled_Nursing_Visit_Note.Client_First_Name)<>[Skilled_Nursing_Visit_Note_1].[client_first_name]) AND ((Trim(Mid([Skilled_Nursing_Visit_Note].[Client_Last_Name],1,1)))=Trim(Mid([Skilled_Nursing_Visit_Note_1].[Client_Last_Name],1,1))) AND ((Trim(Mid([Skilled_Nursing_Visit_Note].[Client_First_Name],1,1)))=Trim(Mid([Skilled_Nursing_Visit_Note_1].[Client_First_Name],1,1))));

PS- Query can be done either in Access or SQL, as tables are linked to SQL (2008)

Thanks
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros