Results 1 to 6 of 6
  1. #1
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    252

    Best way to identify an percent from data

    So I have a table with a column full of either 'yes' or 'no' responses. I'm trying to figure out the cleanest way for a query to simply show 'here's the percentage of fields in this column that are yes'

    I know it can be done, but again, I'm trying to find the simplest way to do this with the smallest amount of queries to get to the desired outcome.

    Any suggestions?

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,029
    Maybe something like
    Code:
    SELECT count(*) / (SELECT count(*) FROM some_table) FROM some_table WHERE some_column='yes'

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,303
    Q1 to sum grand totals,

    Q2 to do the groups and use Q1 to calculate the %.
    in the Q2 query, pull in your data table/qry and Q1 qry.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    10,315
    Since no real example provide I'm assuming you field is a yes/no field with a format property of yes/no. So not tested but perhaps

    SELECT -sum(myfield) / count(*) FROM some_table

  5. #5
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,029
    Quote Originally Posted by CJ_London View Post
    Since no real example provide I'm assuming you field is a yes/no field with a format property of yes/no. So not tested but perhaps

    SELECT -sum(myfield) / count(*) FROM some_table
    Clever! I don't know why but little tricks like this get me excited

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    10,315
    there is a lot than can be done with SQL. For example use of non standard (non-equi) joins are often not investigated because they can't be displayed in the query GUI, only in the sql window. Can often be done using a cartesian query in the query GUI, but much slower for large datasets.

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

Similar Threads

  1. Replies: 2
    Last Post: 03-06-2016, 03:13 PM
  2. Replies: 2
    Last Post: 07-19-2015, 03:51 PM
  3. Replies: 3
    Last Post: 05-01-2014, 02:27 PM
  4. Replies: 3
    Last Post: 01-08-2011, 05:40 PM
  5. Query to identify sequences of data
    By TheWolfster in forum Queries
    Replies: 13
    Last Post: 05-25-2010, 12:55 AM

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