We help IT Professionals succeed at work.

MS Access Count Distinct Query

Chris
Chris asked
on
Medium Priority
2,099 Views
Last Modified: 2012-05-06
Hello,

Apparently MS Access does not support the Count(DISTINCT) function as SQL Server does.  There are work-arounds, but I don't understand them.  The code below is my current query which does not run in MS Access.  Can someone please provide the Access compatible equivalent?

Thank you!
SELECT [qryInsAnnPremByCarrierReport-AllCommission-Union].[Product Carrier], Sum([qryInsAnnPremByCarrierReport-AllCommission-Union].AnnPremium) AS AnnPremium, Count(DISTINCT [qryInsAnnPremByCarrierReport-AllCommission-Union].Insurance_ID) AS CountOfInsurance_ID
FROM [qryInsAnnPremByCarrierReport-AllCommission-Union]
GROUP BY [qryInsAnnPremByCarrierReport-AllCommission-Union].[Product Carrier]
ORDER BY [qryInsAnnPremByCarrierReport-AllCommission-Union].[Product Carrier];

Open in new window

Comment
Watch Question

Commented:
select distinct fieldname, count (fieldname) from table
CERTIFIED EXPERT

Commented:
I think you would have...

SELECT [A].[Product Carrier], Sum([A].AnnPremium) AS AnnPremium,
Count(Select DISTINCT Insurance_ID from  [qryInsAnnPremByCarrierReport-AllCommission-Union]
where [Product carrier] = a.[product carrier]) AS CountOfInsurance_ID
FROM [qryInsAnnPremByCarrierReport-AllCommission-Union] as A
GROUP BY [A].[Product Carrier]
Eric ShermanAccountant/Developer
CERTIFIED EXPERT

Commented:
Probably need to use a sub-query to generate the CountOfInsurance_ID field.

SELECT [qryInsAnnPremByCarrierReport-AllCommission-Union].[Product Carrier], Sum([qryInsAnnPremByCarrierReport-AllCommission-Union].AnnPremium) AS  
(SELECT DISTINCT Count([qryInsAnnPremByCarrierReport-AllCommission-Union].Insurance_ID) AS CountOfInsurance_ID
FROM [qryInsAnnPremByCarrierReport-AllCommission-Union]
GROUP BY [qryInsAnnPremByCarrierReport-AllCommission-Union].[Product Carrier])


ET
Eric ShermanAccountant/Developer
CERTIFIED EXPERT

Commented:
OOPS ... Here's just the sub-query portion ....

(SELECT DISTINCT Count([qryInsAnnPremByCarrierReport-AllCommission-Union].Insurance_ID) AS CountOfInsurance_ID
FROM [qryInsAnnPremByCarrierReport-AllCommission-Union]
GROUP BY [qryInsAnnPremByCarrierReport-AllCommission-Union].[Product Carrier])


ET

Commented:
select DISTINCT [Insurance_ID] , count( [Insurance_ID]) AS CountOfInsurance_ID ,[Product Carrier] sum([AnnPremium]) As AnnPremium1 from [qryInsAnnPremByCarrierReport-AllCommission-Union]
ChrisIT Manager

Author

Commented:
Wow - that's a lot of SQL.  None of the statements work.  Etsherman - it looks like you're on the right track - can you give me the full SQL statement?
Accountant/Developer
CERTIFIED EXPERT
Commented:
Try this ...

SELECT [qryInsAnnPremByCarrierReport-AllCommission-Union].[Product Carrier], Sum([qryInsAnnPremByCarrierReport-AllCommission-Union].AnnPremium) AS AnnPremium, (SELECT DISTINCT Count([qryInsAnnPremByCarrierReport-AllCommission-Union].Insurance_ID) AS CountOfInsurance_ID
FROM [qryInsAnnPremByCarrierReport-AllCommission-Union]
WHERE [Product Carrier] = A.[Product Carrier])
FROM [qryInsAnnPremByCarrierReport-AllCommission-Union] As A
GROUP BY [qryInsAnnPremByCarrierReport-AllCommission-Union].[Product Carrier]
ORDER BY [qryInsAnnPremByCarrierReport-AllCommission-Union].[Product Carrier];

ET

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*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.