Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Count UNIQUE results in a field from an MS Access database

Posted on 2016-10-22
6
Medium Priority
?
140 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
Technology Partners: 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!

 

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 2000 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

722 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