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
Solved

Count UNIQUE results in a field from an MS Access database

Posted on 2016-10-22
6
64 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

828 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