Link to home
Get AccessLog in
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))
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

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
Avatar of Beeyen
Beeyen

ASKER

Thanks.  Can I enter this into the design view for Access?
Avatar of 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;
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of Beeyen

ASKER

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