Count DISTINCT

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.

I really lost, thanks for help


.

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: Count DISTINCT
    ... SQL = "SELECT Count(DISTINCT MemberID) As DDD From Trans" ... Please reply to the newsgroup. ...
    (microsoft.public.inetserver.asp.db)
  • Invalid object name dbo.MSreplication_queue.
    ... I am attemption to update a record in a SQL 2005 table that is trans. ... replicated with updates and I am receiving the following message.... ... Invalid object name 'dbo.MSreplication_queue'. ...
    (microsoft.public.sqlserver.replication)
  • Whats "index scans kdiixs1" on Statspack mean?
    ... per Trans ... Any idea what "index scans kdiixs1" is? ... My belief (from comparing the top SQL and it's plan) is this is related ...
    (comp.databases.oracle.server)
Loading