Results 1 to 5 of 5
  1. #1
    TrulyVisceral is offline Total N00B
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Deimos
    Posts
    102

    Unhappy Have a query show the percentage of an amount

    It's ya boi.


    Thought I was done, not so. Not even close (if anyone remembers anyways).
    Thankfully I got most of the work done myself, not that complex.

    Anyways, I got this query:
    Code:
    SELECT TableMonth.Provider, Count(TableMonth.[IncidentID]) AS Amount
    FROM TableMonth
    GROUP BY TableMonth.Provider, TableMonth.[Type]
    HAVING (((TableMonth.Provider)="CANTV" Or (TableMonth.Provider)="MOVISTAR") AND ((TableMonth.[Type])="Total"));
    It's not the actual query, had to translate from spanish to keep things consistent. But what that displays is something like this:
    Provider Amount
    CANTV 25
    MOVISTAR 75
    What I want is to modify the query so it shows the percentage of amounts. Like, in the example, then the first one would be 25% of all incidents, and the second would be 75%.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make Q1 to get the totals:
    select Provider, Count(provider) as TotalCount from table

    then make Q2 to get % from Q1 ,sorta like:
    select Q1.Provider ,Q1.TotalCount, Count(Incedent) as ItemCount, (Count(Incident) / Q1.TotalCount) as Pct from table, Q1

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Or build a report and do calcs in textboxes.

    Textbox named tbxSum in report footer: =Sum([Amount])

    Then textbox in detail section: =[Amount]/[tbxSum]
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    TrulyVisceral is offline Total N00B
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Deimos
    Posts
    102
    Quote Originally Posted by June7 View Post
    Or build a report and do calcs in textboxes.

    Textbox named tbxSum in report footer: =Sum([Amount])

    Then textbox in detail section: =[Amount]/[tbxSum]
    Not a report, a form.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, try it. But be aware, applying filter to form will change the calcs.

    So try both the query and the form calcs and pick the method you prefer.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 7
    Last Post: 06-15-2017, 02:36 AM
  2. Replies: 12
    Last Post: 07-18-2014, 01:22 PM
  3. Replies: 3
    Last Post: 03-20-2013, 01:31 AM
  4. Replies: 1
    Last Post: 01-11-2013, 06:01 PM
  5. Replies: 10
    Last Post: 10-19-2012, 05:10 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