Solved

select count(distinct) using access database

Posted on 2003-11-23
15
67,056 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Pull multiple cvs files into one access table 28 65
VBA color chart bars 12 78
Problem to skip loop 6 55
How to measure sizes and angles in scanned images ? 3 54
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

896 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

14 Experts available now in Live!

Get 1:1 Help Now