1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access - Summary by observation

Discussion in 'Business Applications' started by flovettsr, Jun 24, 2019.

Thread Status:
Not open for further replies.
Advertisement
  1. flovettsr

    flovettsr Thread Starter

    Joined:
    Apr 1, 2011
    Messages:
    25
    Hello,

    I have a data set of Sanitation, Structural Observations. A location may appear more than one time within the set, actually, over a years time - the location can be upward of 30 times. I am having difficulty with a query to count the number of locations that meet a particular observations. For example Loc #1 appears 10 times, #2 appears 4 times and #3 appears 6 times with the same observation. I would just need to have the query return 3, which represents the number of locations with a particular observation and not20, which represents the number of times the observation appears. I was thinking of a crosstab query, but I am unable to figure it out. Any help would be appreciated.
     

    Attached Files:

  2. Fireflycph

    Fireflycph

    Joined:
    Apr 1, 2016
    Messages:
    999
    First Name:
    Morten
    Your thread header says "Access" but you're posting an Excel sheet. Do you want this result in Access or Excel?
     
  3. flovettsr

    flovettsr Thread Starter

    Joined:
    Apr 1, 2011
    Messages:
    25
    I'm migrating, somewhat, from Excel to Access. The same information is also in an access database
     
    Last edited: Jun 25, 2019
  4. Fireflycph

    Fireflycph

    Joined:
    Apr 1, 2016
    Messages:
    999
    First Name:
    Morten
    OK. Give me a day or two and I'll look at it.
    But, just for clarification, you just want a report/form telling you how many times the same ID has had the same observation issue.
     
  5. flovettsr

    flovettsr Thread Starter

    Joined:
    Apr 1, 2011
    Messages:
    25
    Not really, if 10 distinct locations had the same observation at total of, lets say, 90 times, I only want the results to show 10 (number of distinct locations with that observation). I have a query that counts the number of times a location shows up. [I can do this in Excel by removing some duplicate columns or doing a pivot table and counting the number of locations. But I am trying to migrate from this as I have multiple customers and the amount of time it is taking is just too much. Hope this helps.
     
  6. Fireflycph

    Fireflycph

    Joined:
    Apr 1, 2016
    Messages:
    999
    First Name:
    Morten
    Are there any lower limit to the number. I'd assume that you'd get some/many where there only is one distinct observation.

    But, again, to clarify, you want the number (x) times the same unique location has a specific observation (Y)? And the description of that observation.

    So if you do a cross-tab query/report you'll have a lot, I assume, where a location appears once with a single observation. And some not at all.
     
  7. Chawbacon

    Chawbacon

    Joined:
    Jul 9, 2018
    Messages:
    462
    First Name:
    Jack
    Hello Flovettsr,

    I found it easier to just create a small DB to accomplish the task you desired via a report (if I am understanding the requirements correctly).
    Look over the Query and Report within the DB and feel free to ask questions as needed.

    Good Luck!
     

    Attached Files:

  8. Chawbacon

    Chawbacon

    Joined:
    Jul 9, 2018
    Messages:
    462
    First Name:
    Jack
    My apologies. I completely looked over this. Back to the drawing board.
     
  9. Fireflycph

    Fireflycph

    Joined:
    Apr 1, 2016
    Messages:
    999
    First Name:
    Morten
    Well, I think I'll leave this in @Chawbacon 's, I'm sure, more than capable hands.
    Especially since I'm not really sure exactly what you're looking for. Which, I'm sure, is due to my limited understanding.

    Good luck to you, @flovettsr .
     
  10. Chawbacon

    Chawbacon

    Joined:
    Jul 9, 2018
    Messages:
    462
    First Name:
    Jack
    OK. This is a functional, not a pretty, solution. If you are looking for something that can be format adjusted on a report, or form, that will take a bit more work.
     

    Attached Files:

  11. flovettsr

    flovettsr Thread Starter

    Joined:
    Apr 1, 2011
    Messages:
    25
    Thanks, this will do nicely.
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1229073

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice