Access query to count repeating values only once

Posted on 2012-09-19
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 39

    Accepted Solution

    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 119

    Expert Comment

    by:Rey Obrero
    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

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now