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

    Unhappy Create a query that shows the percent of an amount (Remake of another thread)

    Making a new one because I think I explained things wrong.
    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%.
    What I mean is, the total of incidents would be 100 yes? The first one would be 25% of all, while the second would be 75%. I'm showing this in a form, not a report.

    The last part of the query, where it says "Total". Don't worry about it, ignore it, that's just the type of incident

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you need a second query to calculate the total - we'll call it qryTTL
    Code:
    SELECT Count(TableMonth.[IncidentID]) AS Ttl
    FROM TableMonth
    WHERE (((TableMonth.Provider)="CANTV" Or (TableMonth.Provider)="MOVISTAR");
    which will return a single value

    then you need a third to combine the two to do the calculation. Assume your first query is called qry1

    Code:
    SELECT Provider, Amount, Amount/Ttl as pcent
    FROM qr1, qryTTL
    Note your first query, although works is inefficient because you are apply the criteria after you have summarised all the data. You are also grouping by Type (which is a reserved word and should not be used for a field name) which I don't believe you intended. HAVING should only be applied to columns which has been summed, counted, whatever. e.g. HAVING Count(IncidentID)>10

    It should be

    Code:
    SELECT TableMonth.Provider, Count(TableMonth.[IncidentID]) AS Amount
    FROM TableMonth
    WHERE (((TableMonth.Provider)="CANTV" Or (TableMonth.Provider)="MOVISTAR") AND ((TableMonth.[Type])="Total"));
    GROUP BY TableMonth.Provider

  3. #3
    TrulyVisceral is offline Total N00B
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Deimos
    Posts
    102
    Quote Originally Posted by Ajax View Post
    CODE UPON CODE
    Thanks very much .
    Sorry, I was not only tired, I was on like 7 different medicines, which I'm pretty sure I took at the wrong time, but oh well, too late.

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

Similar Threads

  1. Replies: 16
    Last Post: 04-13-2018, 09:36 AM
  2. Replies: 10
    Last Post: 04-13-2017, 06:37 PM
  3. Trying to Create a Query that shows Percentages
    By KingOf206 in forum Queries
    Replies: 4
    Last Post: 10-07-2016, 09:10 AM
  4. Replies: 1
    Last Post: 07-17-2013, 02:34 PM
  5. Replies: 12
    Last Post: 12-17-2010, 05:35 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