Solved

Count UNIQUE results in a field from an MS Access database

Posted on 2016-10-22
6
115 Views
Last Modified: 2016-10-22
I have an MS Access database. I need to get a count of the number of records which have unique results.

There are over 5000 records in the DB. The field is StandardTitle from tblContractJournals,
I need the number of unique titles.

DB example
Redbook
Life
Time
New Yorker
Newsweek
Redbook
Life

Report:
Number of records: 7
Unique Titles: 5

My sql statement is:

sql2 = "Select COUNT(DISTINCT StandardTitle) AS DistinctCount FROM tblContractJournals "

      Set objRS2 = Server.CreateObject("ADODB.Recordset")
      objRS2.Open sql2, objConn

SQL statement generates a PAGE cannot display.
0
Comment
Question by:Malloy1446
[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
  • 3
  • 2
6 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 41855615
sql2 = "Select StandardTitle, COUNT(StandardTitle) AS DistinctCount FROM tblContractJournals Group By StandardTitle"

Open in new window

0
 

Author Comment

by:Malloy1446
ID: 41855634
That gives a count of 0.
I do not need to print the unique titles, only need a count.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41855645
try this

select Count(A.[StandardTitle]) as DistinctCount
from
(select StandardTitle
from tblContractJournals
group by StandardTitle) as A
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:Malloy1446
ID: 41855704
I tried your code and am receiving Website cannot display page message.

I used the following with and without the brackets:

sqlUnique = "Select COUNT(A.StandardTitle) AS Unique " & _
	"FROM (Select tblContractJournals Group By StandardTitle) as A "

	Set objRSUnique = Server.CreateObject("ADODB.Recordset")
	objRSUnique.Open sqlUnique, objConn

Open in new window

0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 41855709
why don't you copy the sql statement verbatim,  and run the query in Access to see the result?

try this

sqlUnique = "Select COUNT(A.[StandardTitle]) AS [Unique] " & _
      "FROM (Select StandardTitle from tblContractJournals Group By StandardTitle) as A"

      Set objRSUnique = Server.CreateObject("ADODB.Recordset")
      objRSUnique.Open sqlUnique, objConn
0
 

Author Closing Comment

by:Malloy1446
ID: 41855712
Success!  Thank you.
0

Featured Post

Industry Leaders: 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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

631 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