Solved

select count(distinct) using access database

Posted on 2003-11-23
15
67,053 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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

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…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now