Re: Count DISTINCT

Mary wrote:
Hi,
I am using Access DB, and want to calculate how many member in my db.

MemberID
Alan
Peter
Carlie
Alan
Tam

SQL = "SELECT Count(MemberID) As DDD From Trans"
It returns 5

But I want the answer returns 4 because "Alan" is duplicate, then I
use DISINCT :
SQL = "SELECT Count(DISTINCT MemberID) As DDD From Trans"
it returns rs error

I try the following :
SQL = "SELECT DISTINCT MemberID From Trans" ---- its ok

I also try below :

SQL = "SELECT DISTINCT, Count(MemberID) As DDD From Trans"
SQL = "SELECT Count(MemberID, DISTINCT) As DDD From Trans"
SQL = "SELECT Count(DISTINCT, MemberID) As DDD From
Trans".............over hundred SQL or change the Db to digit, let
say, 1;2;3;4;2;5 but no use.

Everything go wrong, I search through the web, many peoples said that
it is possible to use Count DISTINCT, but I really try many times, it
is a very simple DB, what wrong with me, can anyone tell me the that
he can.

It is possible to use Count(Distinct), but not in Access (unless they've
added it to A2007).

What you have to do is use a subquery to retrieve the unique values:

Select Count(*) from (SELECT DISTINCT MemberID From Trans) As q

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.

Relevant Pages

  • Re: looping
    ... Oracle - this is a problem requiring an answer that works on SQL Server. ... id), id, salesperson_id, tran_date, clear_date, amount, ... from Trans t2 ... salesperson_id int not null, ...
    (microsoft.public.sqlserver.programming)
  • Re: Real world issue: How to "split" queries in presence of record replication and replication sensi
    ... So functions computed on TRANS consider the ... It seems that you know what kind of result you want to get and the only difficulty is in writing the corresponding SQL query. ... I see several alternatives how it can be encoded in SQL including nested queries and conventional WHERE restrictions. ...
    (comp.databases.theory)
  • Re: Exists/Joining query
    ... Being new to it all (sql etc) I was just wondering if I was using the ... NOLOCk is something new to me, so I'm not really sure when to ... I thought the above nolock wouldn't matter because its one memberID ... topic he's viewing) and I didn't want to wait for another user to ...
    (microsoft.public.sqlserver.programming)
  • Re: Mebership DB Question
    ... I made a test DB intended to exactly match that implied by your SQL: ... PK Autonumber field: MemberID ... Table: Payments ... "John W. Vinson" wrote: ...
    (microsoft.public.access.tablesdbdesign)
  • Count DISTINCT
    ... and want to calculate how many member in my db. ... SQL = "SELECT CountAs DDD From Trans" ... I really lost, thanks for help ...
    (microsoft.public.inetserver.asp.db)
Loading