Solved

Count UNIQUE results in a field from an MS Access database

Posted on 2016-10-22
6
57 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 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
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.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

831 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