Solved

select count(distinct) using access database

Posted on 2003-11-23
15
67,065 Views
Last Modified: 2013-12-25
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
Comment
Question by:daniellyh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
15 Comments
 
LVL 1

Author Comment

by:daniellyh
ID: 9808078
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
 
LVL 8

Expert Comment

by:MYLim
ID: 9808496
it seem this is merely your syntax error : try below :
objrec1.open "SELECT count(*) as distinctcounter from tableA WHERE fieldA=Condition;",gldbobjconn,,,adcmdtext
0
 
LVL 1

Author Comment

by:daniellyh
ID: 9808870
hi MYLIM,

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

daniel
0
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!

 
LVL 8

Expert Comment

by:MYLim
ID: 9808881
Do you means this :
objrec1.open "SELECT count(fieldA) as distinctcounter from tableA WHERE fieldA=Condition;",gldbobjconn,,,adcmdtext
0
 
LVL 8

Accepted Solution

by:
MYLim earned 80 total points
ID: 9808905
i think you means this:
select fieldA,count(*) from TableA
group by fieldA
0
 
LVL 1

Author Comment

by:daniellyh
ID: 9808935
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
 
LVL 8

Expert Comment

by:MYLim
ID: 9808966
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
 
LVL 8

Expert Comment

by:MYLim
ID: 9808976
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
 
LVL 8

Expert Comment

by:MYLim
ID: 9808993
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
 
LVL 1

Author Comment

by:daniellyh
ID: 9809453
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
 
LVL 8

Expert Comment

by:MYLim
ID: 9829152
OK :)
0
 

Expert Comment

by:serutan
ID: 13456005
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
 
LVL 1

Author Comment

by:daniellyh
ID: 13456085
hi,

yup. anyway i appreciate his initiative to help.

daniel
0
 

Expert Comment

by:tenriquez39
ID: 23488045
i have this it work

select rzicu, count(rzicu)   from (    PRODDTA_F03B14) group by rzicu
0
 
LVL 4

Expert Comment

by:rickybsb
ID: 24366048
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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question