Access query to count repeating values only once

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

Who is Participating?
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
Rey Obrero (Capricorn1)Commented:
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
RJT_VTAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.