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!
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];
Surone1
select distinct fieldname, count (fieldname) from table
I think you would have...
SELECT [A].[Product Carrier], Sum([A].AnnPremium) AS AnnPremium,
Count(Select DISTINCT Insurance_ID from [qryInsAnnPremByCarrierRep
where [Product carrier] = a.[product carrier]) AS CountOfInsurance_ID
FROM [qryInsAnnPremByCarrierRep
GROUP BY [A].[Product Carrier]
SELECT [A].[Product Carrier], Sum([A].AnnPremium) AS AnnPremium,
Count(Select DISTINCT Insurance_ID from [qryInsAnnPremByCarrierRep
where [Product carrier] = a.[product carrier]) AS CountOfInsurance_ID
FROM [qryInsAnnPremByCarrierRep
GROUP BY [A].[Product Carrier]
Probably need to use a sub-query to generate the CountOfInsurance_ID field.
SELECT [qryInsAnnPremByCarrierRep
(SELECT DISTINCT Count([qryInsAnnPremByCarr
FROM [qryInsAnnPremByCarrierRep
GROUP BY [qryInsAnnPremByCarrierRep
ET
SELECT [qryInsAnnPremByCarrierRep
(SELECT DISTINCT Count([qryInsAnnPremByCarr
FROM [qryInsAnnPremByCarrierRep
GROUP BY [qryInsAnnPremByCarrierRep
ET
OOPS ... Here's just the sub-query portion ....
(SELECT DISTINCT Count([qryInsAnnPremByCarr
FROM [qryInsAnnPremByCarrierRep
GROUP BY [qryInsAnnPremByCarrierRep
ET
(SELECT DISTINCT Count([qryInsAnnPremByCarr
FROM [qryInsAnnPremByCarrierRep
GROUP BY [qryInsAnnPremByCarrierRep
ET
select DISTINCT [Insurance_ID] , count( [Insurance_ID]) AS CountOfInsurance_ID ,[Product Carrier] sum([AnnPremium]) As AnnPremium1 from [qryInsAnnPremByCarrierRep
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.