Results 1 to 5 of 5
  1. #1
    DaveT99 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    76

    Frequency Distribution

    I have used the Union query to count the number of values in a range, is there an alternative option??

    This is what I created for unit cost (AUP), and it works fine. But I have a lot of 0 values, I tried to modify the query to count the zero values without success. Can anyone help?

    This is the first two lines of the SQL to count 0-50 and 50-100

    SELECT Count(SPD1.AUP) AS CountOfAUP, "0 to 50" AS Rng


    FROM SPD1
    WHERE (((SPD1.AUP)>0 And (SPD1.AUP)<50));


    Union
    SELECT Count(SPD1.AUP) AS CountOfAUP, "050.01 to 100" AS Rng
    FROM SPD1
    WHERE (((SPD1.AUP)>050.01 And (SPD1.AUP)<100));

    Many Thanks Dave

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I tried to modify the query to count the zero values without success
    because you're asking for greater than zero, not greater than or equal to?
    WHERE (((SPD1.AUP)>0 And (SPD1.AUP)<50));

    You can't get 50 with your sql either.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    DaveT99 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    76
    Quote Originally Posted by orange View Post
    Dave, Research the Partition function in Access.

    Thanks not heard of that one
    Just found a great article that covered this example by creating a new table that contains the ranges and then a query that picks up the data from the new table and the table with the data
    https://www.fmsinc.com/microsoftaccess/query/index.html

    Dave

  5. #5
    DaveT99 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    76
    The query was my original query where I was counting from 0 to 50. See my answer to the 1st reply, I found a good article that simplified the outcome
    Thanks

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

Similar Threads

  1. Query/Reports that illustrate frequency
    By chloe in forum Forms
    Replies: 6
    Last Post: 01-28-2015, 01:42 PM
  2. Replies: 5
    Last Post: 10-29-2014, 12:12 PM
  3. Report Based on Field Frequency
    By thegnome in forum Reports
    Replies: 1
    Last Post: 03-12-2013, 12:28 PM
  4. Frequency of Words in Memo Fields
    By Angrybox in forum Queries
    Replies: 1
    Last Post: 05-07-2012, 03:54 PM
  5. Generate reports by frequency
    By MFS in forum Programming
    Replies: 2
    Last Post: 11-18-2010, 08:09 AM

Tags for this Thread

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