Link to home
Start Free TrialLog in
Avatar of bfuchs
bfuchsFlag 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
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

can you provide sample data and expected output?
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.
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

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

Avatar of bfuchs

ASKER

Hi Experts,
I'm not in today, will test it on Sun.
Have a nice weekend!
Thanks,
Ben
Avatar of 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
Avatar of 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
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
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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
Avatar of 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?
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.