Count Unique

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a column in a table that contains References. Some are duplicated (this is unavoidable). Is there a method to COUNT each unique reference once whilst ignoring duplicates?
eg: a count of A,B,C,C,D,E,E = 5 (not 7)
 
Use a SubQuery to select DISTINCT Ref and then the Main Query can count
what's selected. Something like:

SELECT Count([Ref])
FROM
(
SELECT DISTINCT [Ref]
FROM YourTable
)

--
HTH
Van T. Dinh
MVP (Access)



Wesley said:
I have a column in a table that contains References. Some are duplicated
Click to expand...
(this is unavoidable). Is there a method to COUNT each unique reference
once whilst ignoring duplicates?
 
SELECT Count([RiskReference]) AS NoOfRisks FROM (select distinct [RiskReference]
FROM AFB

??

can you tell me where my code is going wrong?
 
Wesley said:
SELECT Count([RiskReference]) AS NoOfRisks FROM (select distinct
Click to expand...
[RiskReference])

put "FROM AFB" before the closing bracket to form a complete subquery.
 
Is there away to insert this SQL code:
SELECT Count([AFB.RiskReference]) AS NoOfRisks FROM (SELECT DISTINCT [AFB.RiskReference] from AFB);

into this:

SELECT Synds.SyndicateName, Users.UserName, AFB.COB, Sum(AFB.Time) AS TotalTime, AFB.DocType, AFB.Task, Sum(AFBBenchmarks.Benchmark) AS TotalBMTime, [TotalTime]-[TotalBMTime] AS [+/-BMT], ([TotalTime]/[TotalBMTime]) AS [TimeTakenAs%OfBMT]
FROM ((AFB INNER JOIN Users ON AFB.User = Users.UserInitials) INNER JOIN AFBBenchmarks ON (AFB.DocType = AFBBenchmarks.DocType) AND (AFB.COB = AFBBenchmarks.COB) AND (AFB.Task = AFBBenchmarks.Task)) INNER JOIN Synds ON AFB.Syndicate = Synds.Psuedonym
WHERE (((AFB.Date)>=[forms]![Reports]![txtFromDate] And (AFB.Date)<=[forms]![Reports]![txtToDate]))
GROUP BY Synds.SyndicateName, Users.UserName, AFB.COB, AFB.DocType, AFB.Task
HAVING (((Users.UserName)=IIf([forms]![Reports]![cboUsers]="ALL",[UserName],[forms]![Reports]![cboUsers])));

Thanks.
 
Back
Top