Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2077
  • Last Modified:

MS Access Count Distinct Query

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

0
Chris
Asked:
Chris
1 Solution
 
Surone1Commented:
select distinct fieldname, count (fieldname) from table
0
 
peter57rCommented:
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]
0
 
Eric ShermanAccountant/DeveloperCommented:
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Eric ShermanAccountant/DeveloperCommented:
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
0
 
Surone1Commented:
select DISTINCT [Insurance_ID] , count( [Insurance_ID]) AS CountOfInsurance_ID ,[Product Carrier] sum([AnnPremium]) As AnnPremium1 from [qryInsAnnPremByCarrierReport-AllCommission-Union]
0
 
ChrisIT ManagerAuthor 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?
0
 
Eric ShermanAccountant/DeveloperCommented:
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
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now