Avatar of bfuchs
bfuchs
Flag for United States of America asked on

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
DatabasesMicrosoft AccessMicrosoft SQL ServerSQL

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
Ryan Chong

can you provide sample data and expected output?
Gustav Brock

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 Fye

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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
D B

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

bfuchs

ASKER
Hi Experts,
I'm not in today, will test it on Sun.
Have a nice weekend!
Thanks,
Ben
bfuchs

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
bfuchs

ASKER
@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 Brock

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

ASKER CERTIFIED SOLUTION
Dale Fye

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
D B

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Dale Fye

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
bfuchs

ASKER
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 Fye

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.