[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Access query to count repeating values only once

Posted on 2012-09-19
Medium Priority
Last Modified: 2012-09-19
Quick question about counting repeating values only once in a query.

I have a table with the following fields: ArtifactID, ArtifactType, Location. Multiple artifacts of the same or different type (e.g., stone wall), may be observed at any given location, meaning I have several records that have the same location and the same artifact type.

In my query, I want to generate (1) a total count of the artifacts by type AND (2) a total count of the locations at which a artifact type is observed. In other words, if 5 stone walls are observed at location A and 8 stone walls are observed at location B, I want a query that will spit out 13 (total number of stone walls observved) and 2 (total number of locations at which stone walls were observed).

I know I want to group by ArtifactType, but how do I count each location only once per artifact type? Here's my starting point:

SELECT tblHistSites.ArtifactType, Count(tblHistSites.ArtifactType) AS NumObs
FROM tblHistSites
GROUP BY tblHistSites.ArtifactType;

Open in new window

Question by:RJT_VT
LVL 40

Accepted Solution

als315 earned 2000 total points
ID: 38414574
Test this query:
SELECT tblHistSites.ArtifactType, Count(tblHistSites.ArtifactType) AS CountOfArtifactType, Q3.CountOfLocations
FROM tblHistSites INNER JOIN (SELECT Q2.ArtifactType, Count(Q2.CountLocations) AS CountOfLocations FROM (SELECT ArtifactType, Location AS CountLocations
FROM tblHistSites
GROUP BY ArtifactType, Location) as Q2
GROUP BY Q2.ArtifactType) as Q3 ON tblHistSites.ArtifactType = Q3.ArtifactType
GROUP BY tblHistSites.ArtifactType, Q3.CountOfLocations;

Open in new window

Really you need 3 queries
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38414586
try this query

select t2.ArtifactType, Count(t2.Location)
    (select t.ArtifactType, t.Location
    from tblHistSites t
    group by t.ArtifactType, t.Location) as t2
group by t2.ArtifactType

Author Closing Comment

ID: 38414808
Als315 -- thanks a bunch. I just had to add 'of' into two CountLocations, but otherwise it worked perfectly!

Capricorn1, I'm afraid I couldn't get your query to generate the sum of locations. But thanks for the help.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
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 the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

872 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