Results 1 to 5 of 5
  1. #1
    lburch is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    6

    counting number of values in a range

    hi.
    I have an id column and a value column in a table
    eg


    1 - 5
    1 - 10
    1 - 20
    1 - 50
    2 - 8
    2 - 20
    2 - 30
    2 - 21

    How do i do a a query that says

    id column- 1-5, 5-10, 10-50, 50+ (range)

    1 - 1, 1, 2, 0
    2 - 0, 1, 3, 0

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,901
    you can use the partition function

    https://docs.microsoft.com/en-us/off...ition-function

    then change to a crosstab query with the partition field as the column heade
    edit: just realised your grouping are not all the same size so having used the partition function, you'll need to add 10-20,20-30 etc for 10-50

    Also, your ranges need to be clearer - where should

    1 - 10

    be counted? in 5-10 or 10-50?

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,695
    Select fld1,count(fld1) from table,
    Select fld2, count(fld2) from table,
    Etc.

  4. #4
    lburch is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    6
    Quote Originally Posted by ranman256 View Post
    Select fld1,count(fld1) from table,
    Select fld2, count(fld2) from table,
    Etc.
    Can you elaborate please. I am new to access
    in first column of query i add the id field.
    In the second column i add the field that has all the values
    Where do i put >1 and <=5, is it in the criteria?
    Where do i put count?
    Willl that then say in that id there is 1 count of values in the value range between 1 and 5
    That is what i am trying and its not working for me.

  5. #5
    accesstos's Avatar
    accesstos is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    427
    Quote Originally Posted by lburch View Post
    Willl that then say in that id there is 1 count of values in the value range between 1 and 5
    That is what i am trying and its not working for me.
    Give a try to this:
    Code:
    SELECT id, 
    Sum(-([num]>0 And [num]<6)) AS [1-5], 
    Sum(-([num]>5 And [num]<11)) AS [6-10], 
    Sum(-([num]>10 And [num]<51)) AS [11-50], 
    Sum(-([num]>50)) AS [50+] 
    FROM tblNums 
    GROUP BY id;
    Last edited by accesstos; 01-24-2021 at 07:30 AM.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Counting in a given date range
    By QuantifyRisk in forum Reports
    Replies: 1
    Last Post: 11-25-2014, 01:48 PM
  2. Replies: 3
    Last Post: 07-18-2013, 04:21 AM
  3. Replies: 4
    Last Post: 07-18-2013, 03:14 AM
  4. Replies: 5
    Last Post: 07-05-2013, 11:16 AM
  5. Replies: 21
    Last Post: 11-07-2012, 02:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums