Query needed to find same initials but different names.

bfuchs
bfuchs used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
can you provide sample data and expected output?
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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 LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Doug BishopDatabase Developer

Commented:
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

Hi Experts,
I'm not in today, will test it on Sun.
Have a nice weekend!
Thanks,
Ben
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
@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
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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

Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
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

Doug BishopDatabase Developer

Commented:
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 LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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
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 LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial