Avatar of Beeyen
Beeyen
 asked on

Preventing duplicates in my expression statement in MS Access query

Good Day Expert,

I have a Access query in which I have a column with first and last name but I would like to prevent counting duplicate (same first and last).  Could you help me from the expression statement provided below.  I tried DCount but can not get it to work.  Let me know.  Thanks


#ofUtilizers: Count(Left([MemberFirstName],2) & Right([MemberLastName],3))
Microsoft AccessMicrosoft SQL Server

Avatar of undefined
Last Comment
Beeyen

8/22/2022 - Mon
Rey Obrero (Capricorn1)

try

select a.[users],count(a.[users]) as [#ofUtilizers]
from
(select Left([MemberFirstName],2) & Right([MemberLastName],3) as [users]
from tableName
group by Left([MemberFirstName],2) & Right([MemberLastName],3)
) as a
Beeyen

ASKER
Thanks.  Can I enter this into the design view for Access?
Beeyen

ASKER
Good Day I should have included the entire query.  It is included below but I would like only to prevent duplicates for #ofUtilizers: Count(Left([MemberFirstName],2) & Right([MemberLastName],3))


SELECT NTI_050114.GPI14, NTI_050114.MSI, First(NTI_050114.GPI14Description) AS FirstOfGPI14Description, First(NTI_050114.GenericIndicatorOverride) AS FirstOfGenericIndicatorOverride, First(NTI_050114.FormularyTier) AS FirstOfFormularyTier, Count(Left([MemberFirstName],2) & Right([MemberLastName],3)) AS [#ofUtilizers], Count(NTI_050114.ClaimIdentifier) AS CountOfClaimIdentifier, Sum(NTI_050114.MemberPaidAmount) AS SumOfMemberPaidAmount, Sum(NTI_050114.AccountPaid) AS SumOfAccountPaid, Sum(NTI_050114.TotalAllowedAmount) AS SumOfTotalAllowedAmount
FROM NTI_050114
GROUP BY NTI_050114.GPI14, NTI_050114.MSI
ORDER BY NTI_050114.GPI14, NTI_050114.MSI;
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Sharath S

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
Beeyen

ASKER
I copied the sql statement but when running the query it just hang and does not complet.  Any ideas?