Solved

Count UNIQUE results in a field from an MS Access database

Posted on 2016-10-22
6
44 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
  • 3
  • 2
6 Comments
 
LVL 45

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 119

Expert Comment

by:Rey Obrero
ID: 41855645
try this

select Count(A.[StandardTitle]) as DistinctCount
from
(select StandardTitle
from tblContractJournals
group by StandardTitle) as A
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 119

Accepted Solution

by:
Rey Obrero 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

706 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

21 Experts available now in Live!

Get 1:1 Help Now