Query needed to find same initials but different names.

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))));

Open in new window


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

Thanks
LVL 6
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongSoftware Team LeadCommented:
can you provide sample data and expected output?
Gustav BrockCIOCommented:
First, create a query to list those initials that are not unique:

SELECT 
    Trim(Mid([Skilled_Nursing_Visit_Note].[Client_First_Name],1,1)) & Trim(Mid([Skilled_Nursing_Visit_Note].[Client_Last_Name],1,1)) AS Initials
FROM 
    Skilled_Nursing_Visit_Note
GROUP BY
    Trim(Mid([Skilled_Nursing_Visit_Note].[Client_First_Name],1,1)) & Trim(Mid([Skilled_Nursing_Visit_Note].[Client_Last_Name],1,1)) AS Initials
HAVING Count(*) > 1

Open in new window

Save it and create another query:

SELECT 
    Trim(Mid([Skilled_Nursing_Visit_Note].[Client_First_Name],1,1)) & Trim(Mid([Skilled_Nursing_Visit_Note].[Client_Last_Name],1,1)) AS Initials,
    Skilled_Nursing_Visit_Note.Client_Last_Name, 
    Skilled_Nursing_Visit_Note.Client_First_Name
FROM 
    Skilled_Nursing_Visit_Note

Open in new window

Also save this.

Finally, in a third query, inner join these queries on Initials and select the full names to be listed.
Dale FyeOwner, Dev-Soln LLCCommented:
Try this:

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,
FROM Skilled_Nursing_Visit_Note, Skilled_Nursing_Visit_Note AS Skilled_Nursing_Visit_Note_1
WHERE LEFT(Skilled_Nursing_Visit_Note.Client_Last_Name,1) = LEFT([Skilled_Nursing_Visit_Note_1].[client_last_name], 1)
AND LEFT(Skilled_Nursing_Visit_Note.Client_First_Name,1) = LEFT([Skilled_Nursing_Visit_Note_1].[client_First_name], 1)
AND (Skilled_Nursing_Visit_Note.Client_Last_Name <> [Skilled_Nursing_Visit_Note_1].[client_last_name])
AND (Skilled_Nursing_Visit_Note.Client_First_Name <> [Skilled_Nursing_Visit_Note_1].[client_First_name])

Open in new window

Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Doug BishopDatabase DeveloperCommented:
Start using aliases. It will make you code a lot more readable and it will take a lot less typing (unless you just live to type code) :-)
SQL Server--note, Use an INNER JOIN. You are using a CROSS JOIN which is going to be less than optimal.:
SELECT DISTINCT
       vn1.Client_Last_Name,
       vn1.Client_First_Name,
       vn2.Client_Last_Name,
       vn2.Client_First_Name,
       LEFT(vn1.[Client_Last_Name], 1) AS Expr1,
       LEFT(vn1.[Client_First_Name], 1) AS Expr2
FROM dbo.Skilled_Nursing_Visit_Note vn1
    INNER JOIN dbo.Skilled_Nursing_Visit_Note vn2
        ON vn1.Client_Last_Name <> vn2.Client_Last_Name
           AND vn1.Client_First_Name <> vn2.Client_First_Name
           AND LEFT(vn1.Client_First_Name, 1) = LEFT(vn2.Client_First_Name, 1)
           AND LEFT(vn1.Client_Last_Name, 1) = LEFT(vn2.Client_Last_Name, 1);

Open in new window

bfuchsAuthor Commented:
Hi Experts,
I'm not in today, will test it on Sun.
Have a nice weekend!
Thanks,
Ben
bfuchsAuthor Commented:
Hi Experts,
@Dale, @Doug,
Both of your suggestions are giving me same results as i was originally getting, which didn't make sense to me, so perhaps I need an explanation why are there appearing...see attached.

@Gustav,
i gave priority to their suggestions as it would not require multiple queries, however if theirs don't work, guess its time to test yours...
Thanks,
Ben
Capture.PNG
bfuchsAuthor Commented:
@Gustav,
Your query besides of also having that issue above (with the sample attached), its not listing both names, I need to see them side by side.
Thanks,
Ben
Gustav BrockCIOCommented:
These will do that.

Save this query as Initials:

SELECT 
    Trim(Mid([Skilled_Nursing_Visit_Note].[Client_First_Name],1,1)) & Trim(Mid([Skilled_Nursing_Visit_Note].[Client_Last_Name],1,1)) AS Initials
FROM 
    Skilled_Nursing_Visit_Note
GROUP BY 
    Trim(Mid([Skilled_Nursing_Visit_Note].[Client_First_Name],1,1)) & Trim(Mid([Skilled_Nursing_Visit_Note].[Client_Last_Name],1,1))
HAVING 
    Count(*) > 1;

Open in new window

and this as InitialsNames:

SELECT 
    Trim(Mid([Skilled_Nursing_Visit_Note].[Client_First_Name],1,1)) & Trim(Mid([Skilled_Nursing_Visit_Note].[Client_Last_Name],1,1)) AS Initials,
    Skilled_Nursing_Visit_Note.Client_Last_Name, 
    Skilled_Nursing_Visit_Note.Client_First_Name
FROM  
    Skilled_Nursing_Visit_Note;

Open in new window

and, finally, this query as, say, InitialsDupes:

SELECT DISTINCT 
    Initials.Initials, 
    InitialsNames.Client_First_Name, 
    InitialsNames.Client_Last_Name, 
    InitialsNames_1.Client_First_Name, 
    InitialsNames_1.Client_Last_Name
FROM 
    (Initials 
INNER JOIN 
    InitialsNames 
    ON Initials.Initials = InitialsNames.Initials) 
INNER JOIN 
    InitialsNames AS InitialsNames_1 
    ON Initials.Initials = InitialsNames_1.Initials
WHERE 
    (InitialsNames_1.Client_First_Name <> [InitialsNames]![Client_First_Name]) 
    AND 
    (InitialsNames_1.Client_Last_Name <> [InitialsNames]![Client_Last_Name])
ORDER BY 
    Initials.Initials;

Open in new window

Dale FyeOwner, Dev-Soln LLCCommented:
Well, it looks like your names have spaces at the head of the text, so that might be a big part of the problem.  So, you might try:

SELECT DISTINCT Trim(SNVN.Client_Last_Name)
, Trim(SNVN.Client_First_Name)
, Trim(SNVN1.Client_Last_Name)
, Trim(SNVN1.Client_First_Name)
FROM Skilled_Nursing_Visit_Note as SNVN, Skilled_Nursing_Visit_Note AS SNVN1
WHERE LEFT(Trim(SNVN.Client_Last_Name),1) = LEFT(Trim([SNVN1].[client_last_name]), 1)
AND LEFT(Trim(SNVN.Client_First_Name),1) = LEFT(Trim([SNVN1].[client_First_name]), 1)
AND (Trim(SNVN.Client_Last_Name) <> Trim([SNVN1].[client_last_name]))
AND (Trim(SNVNote.Client_First_Name) <> Trim([SNVN1].[client_First_name]))

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Doug BishopDatabase DeveloperCommented:
Note that if you are using SQL Server, unless you have 2017, you will need to change the TRIM() function to LTRIM(). If using Access, TRIM() will work. However, I STRONGLY recommend you run the query as a pass-through query in SQL Server if that is where the data is stored. Your network traffic will be greatly reduced, and your queries will run considerably faster.
Dale FyeOwner, Dev-Soln LLCCommented:
I would agree with Doug that if your data is in SQL Server, run the query as a pass-through, let the server do the work.

Dale
bfuchsAuthor Commented:
Thank you!

btw, still wondering why didn't my original query work as I was also using trim function to get rid of the preceding space characters?
Dale FyeOwner, Dev-Soln LLCCommented:
Because the syntax:

(Trim(Mid([Skilled_Nursing_Visit_Note].[Client_Last_Name],1,1)))

takes the first character of the field before doing anything else (the Mid statement), and then trims it.  
Since the first character in many of those records was a space, Trim(" ") = "" = vbemptystring

if you had used Mid(Trim([Skilled_Nursing_Visit_Note].[Client_Last_Name]), 1, 1) it probably would have worked.

But I probably would have modified the first test in the WHERE clause as well, to trim the spaces from both Last_Name fields.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.