COUNT (DISTINCT (myfield)) PROBLEM

it works well
SELECT DISTINCT ([cinsiyet]) FROM URUNLER
WHERE cinsiyet<>'Bileklik' AND cinsiyet<>'Yuzuk' AND cinsiyet<>'Set' AND
cinsiyet<> 'Kupe' AND cinsiyet<>'Bilezik' AND cinsiyet<> 'Kolye';
but i need to get recordcount of this query whis oledb.datareader doesnt say
it to me...
SELECT COUNT ( DISTINCT ([cinsiyet]) ) FROM URUNLER
WHERE cinsiyet<>'Bileklik' AND cinsiyet<>'Yuzuk' AND cinsiyet<>'Set' AND
cinsiyet<> 'Kupe' AND cinsiyet<>'Bilezik' AND cinsiyet<> 'Kolye';
it returns error ? how can i get this query resultset ?> it returns error ? how can i get this query resultset ?
What error? Are you referring to the column name in your .NET code? If so,
you'll need to add an alias, and refer to that.
SELECT Count_cinsiyet = COUNT(DISTINCT cinsiyet)
FROM URUNLER
WHERE cinsiyet NOT IN
('Bileklik' , 'Yuzuk' , 'Set' , 'Kupe' , 'Bilezik' , 'Kolye');
Now, refer to reader["Count_cinsiyet"];
Or, refer to the ordinal number instead of the name.|||it is an query error.. my db is an access database.. I tried it as an access
query in access it returns error ?
"Savas Ates" <savas@.indexinteractive.com>, haber iletisinde unlar
yazd:%23cB1tMO$FHA.1568@.TK2MSFTNGP10.phx.gbl...
> it works well
> SELECT DISTINCT ([cinsiyet]) FROM URUNLER
> WHERE cinsiyet<>'Bileklik' AND cinsiyet<>'Yuzuk' AND cinsiyet<>'Set' AND
> cinsiyet<> 'Kupe' AND cinsiyet<>'Bilezik' AND cinsiyet<> 'Kolye';
> but i need to get recordcount of this query whis oledb.datareader doesnt
> say it to me...
> SELECT COUNT ( DISTINCT ([cinsiyet]) ) FROM URUNLER
> WHERE cinsiyet<>'Bileklik' AND cinsiyet<>'Yuzuk' AND cinsiyet<>'Set' AND
> cinsiyet<> 'Kupe' AND cinsiyet<>'Bilezik' AND cinsiyet<> 'Kolye';
> it returns error ? how can i get this query resultset ?
>|||> it is an query error.
Can you be MORE SPECIFIC?

> query in access it returns error ?
Can you be MORE SPECIFIC?|||it works well in SQL Database.. But when i try it in access it returns
error. it says there is missing operator in this query ?
how can i solve it ?
"Savas Ates" <savas@.indexinteractive.com>, haber iletisinde unlar
yazd:%23cB1tMO$FHA.1568@.TK2MSFTNGP10.phx.gbl...
> it works well
> SELECT DISTINCT ([cinsiyet]) FROM URUNLER
> WHERE cinsiyet<>'Bileklik' AND cinsiyet<>'Yuzuk' AND cinsiyet<>'Set' AND
> cinsiyet<> 'Kupe' AND cinsiyet<>'Bilezik' AND cinsiyet<> 'Kolye';
> but i need to get recordcount of this query whis oledb.datareader doesnt
> say it to me...
> SELECT COUNT ( DISTINCT ([cinsiyet]) ) FROM URUNLER
> WHERE cinsiyet<>'Bileklik' AND cinsiyet<>'Yuzuk' AND cinsiyet<>'Set' AND
> cinsiyet<> 'Kupe' AND cinsiyet<>'Bilezik' AND cinsiyet<> 'Kolye';
> it returns error ? how can i get this query resultset ?
>|||> it works well in SQL Database.. But when i try it in access it returns
> error.
Egads. WHAT ERROR?|||I'm not 100% sure, but I don't think Access supports Count(Distinct())
HTH
Dan Artuso
"Savas Ates" <savas@.indexinteractive.com> wrote in message
news:O4wbFiO$FHA.208@.tk2msftngp13.phx.gbl...
> it works well in SQL Database.. But when i try it in access it returns
> error. it says there is missing operator in this query ?
> how can i solve it ?
>
> "Savas Ates" <savas@.indexinteractive.com>, haber iletisinde unlar
> yazd:%23cB1tMO$FHA.1568@.TK2MSFTNGP10.phx.gbl...
>|||> I'm not 100% sure, but I don't think Access supports Count(Distinct())
It sure would be nice to see the actual error message. I'm not holding my
breath...|||Aaron Bertrand [SQL Server MVP] wrote:
> It sure would be nice to see the actual error message. I'm not
> holding my breath...
He did say "missing operator" ... :-)
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Dan is correct: JetSQL does not support COUNT(DISTINCT ...)
What you have to do is use a subquery (aka derived table):
Select Count(*) From
(SELECT DISTINCT ([cinsiyet]) FROM URUNLER
WHERE cinsiyet<>'Bileklik' AND cinsiyet<>'Yuzuk' AND cinsiyet<>'Set' AND
cinsiyet<> 'Kupe' AND cinsiyet<>'Bilezik' AND cinsiyet<> 'Kolye') As q
Bob Barrows
Savas Ates wrote:
> it works well in SQL Database.. But when i try it in access it returns
> error. it says there is missing operator in this query ?
> how can i solve it ?
>
> "Savas Ates" <savas@.indexinteractive.com>, haber iletisinde unlar
> yazd:%23cB1tMO$FHA.1568@.TK2MSFTNGP10.phx.gbl...
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"sql