Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 67106
  • Last Modified:

select count(distinct) using access database

hi all,

i try to use a recordset in vb to count a distinct records in a table with statement like below:

objrec1.open "SELECT count(distinct fieldA) as distinctcounter from tableA;",gldbobjconn,,,adcmdtext

but it didnt work, even i run the statement in access's queries it return "Syntax Error (missing operator) in query expression 'count(distinct fieldA)'". so i think may b cannot do a counting of the distinct records in same statement in access. but i think in SQL server is no problem. anyone can help?

thx.

daniel
0
daniellyh
Asked:
daniellyh
1 Solution
 
daniellyhAuthor Commented:
hi,
forgot to mention, i dont want to use objrec1.recordcount because sometimes it wont return correct result. i want to do counting using SQL statement on that distinct records in access.

daniel
0
 
MYLimCommented:
it seem this is merely your syntax error : try below :
objrec1.open "SELECT count(*) as distinctcounter from tableA WHERE fieldA=Condition;",gldbobjconn,,,adcmdtext
0
 
daniellyhAuthor Commented:
hi MYLIM,

but ur statement doent allow me to select distinct from particular table?

daniel
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
MYLimCommented:
Do you means this :
objrec1.open "SELECT count(fieldA) as distinctcounter from tableA WHERE fieldA=Condition;",gldbobjconn,,,adcmdtext
0
 
MYLimCommented:
i think you means this:
select fieldA,count(*) from TableA
group by fieldA
0
 
daniellyhAuthor Commented:
hi mylim,
it doen't correct, but i just get the solution after i discuss with my friend, it should be:

select count(*) from (select distinct fieldA from tableA where conditionA);

it work.

anyway thx.

daniel
0
 
MYLimCommented:
Bad new ! Access not support ! i run your code on interbase server with no problem !
but Access 2000 prompt Syntax Error....
and here are the proof :
http://beta.experts-exchange.com/Databases/MS_Access/Q_20703207.html
0
 
MYLimCommented:
and Microsoft Say only support by SQL Server...view the link and see the:
SQL Server and Access Query Syntax
The following table shows the corresponding differences between SQL Server and Access query syntax.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/access2sql.asp

0
 
MYLimCommented:
Sorry! Dare i ask Why Grade 'C' ?
Grade really do count for the Experts here.  We are not paid staff.  It's the only thing we get for our effort.
q(^ _ ^)p
0
 
daniellyhAuthor Commented:
hi MYLim,
sorry if my grade make u feel hesitate, my reason is ur comment before i said i have found the solution doesn't really solve my problem. frankly speaking it shouldn't counted as an "answer".

if in SQL Server, "select count(distinct fieldA) from tableA where condition;" will work, but it doesn't work in access, that's y i wonder any solution for this. after i discuss with friend we can do it by "select count(*) from (select distinct fieldA from tableA where conditionA);" like i mention above does work. thus i really appreciate ur effort for trying out for me so i accept ur answer before ur further posting (Date: 11/24/2003 03:44PM MYT) but i couldn't accept ur answer as grade A or B because it doesn't help me to solve the problem.

anyway thanks for ur hard work n sorry if u not satisfied with it. anyway i can't reverse the grading right?

:-)

daniel



0
 
MYLimCommented:
OK :)
0
 
serutanCommented:
MYLim,

Your solution:
select fieldA,count(*) from TableA
group by fieldA

counts the occurrences of each distinct value of fieldA.
The problem was to count how many distinct values there are, not how many occurrences of each distinct value. That is probably why grade C.

The real solution
select count(*) from (select distinct FieldA from TableA where conditionA)

selects all distinct values and then counts them. That was what daniellyh wanted.
0
 
daniellyhAuthor Commented:
hi,

yup. anyway i appreciate his initiative to help.

daniel
0
 
tenriquez39Commented:
i have this it work

select rzicu, count(rzicu)   from (    PRODDTA_F03B14) group by rzicu
0
 
rickybsbCommented:
hi tenriquez39, this was the best solution. i have it implemented, and its working like a champ!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now