Solved

select count(distinct) using access database

Posted on 2003-11-23
15
67,060 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
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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

777 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