By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,997 Members | 1,270 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,997 IT Pros & Developers. It's quick & easy.

How to count using distinct

100+
P: 134
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
Oct 7 '08 #1
Share this Question
Share on Google+
2 Replies


P: 10
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT(Failure) FROM FailureTable
Then loop all Failure codes
Expand|Select|Wrap|Line Numbers
  1. For Each Failure In Failures
  2.   SELECT COUNT(DISTINCT BoardID) FROM Failures WHERE Failure='" & Failure & "'"
  3. Next
  4.  
Teme64
Oct 9 '08 #2

100+
P: 134
Thank you I will try
Oct 10 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.