We help IT Professionals succeed at work.

Preventing duplicates in my expression statement in MS Access query

79 Views
Last Modified: 2015-04-06
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))
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

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

Author

Commented:
Thanks.  Can I enter this into the design view for Access?

Author

Commented:
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;
Data Engineer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I copied the sql statement but when running the query it just hang and does not complet.  Any ideas?
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.