How to count using distinct

[jamesnkk]

Hi, I have a ms-access table and i want to count the failure occurance.

BOARD-ID....FAILURE

A-123.......... FAIL-A
A-123.......... FAIL-A
A-125..........FAIL-A
A-126.......... FAIL-A

A-123.......... FAIL-B
A-123.......... FAIL-B

as you can see, the board id may repeat more than 1 row, since it the same
board id with the same faulure, I would count as 1.

So the result should be for failure occurance - -

FAIL-A =3
FAILl-B =1


But i cant think of a solution to solve, please advise

[Teme64]

This works with SQL Server, but don't see why it wouldn't work with Access. And this is pseudo code, not actual VB.NET.

First get all Failure codes:

SELECT DISTINCT(Failure) FROM FailureTable

Then loop all Failure codes

For Each Failure In Failures
  SELECT COUNT(DISTINCT BoardID) FROM Failures WHERE Failure='" & Failure & "'"
Next
 

Teme64

[jamesnkk]

Thank you I will try