Results 1 to 7 of 7
  1. #1
    albaker is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    14

    Unsure how to aggregate query results

    Hello, I receive a spreadsheet of vulnerabilities for systems within a csv file. I'm able to import them, but I'm attempting to extract the number of vulnerabilities remediated with the percentage of types of risks. I'm using the query below, which provides the values for the vulnerabilities that have been remediated in 30 to 90 days along with the number of vulnerabilities of the types discovered in the past year.

    SELECT COUNT([Vulnerability Info].VulnPresentFor) AS [Remediated in 30 to 90 days], System.Risk


    FROM [Vulnerability Info]
    WHERE VulnPresentFor BETWEEN 30 AND 90
    GROUP BY System.Risk


    UNION


    SELECT COUNT([Vulnerability Info].VulnPresentFor) AS [Total], System.Risk
    FROM [Vulnerability Info]
    WHERE VulnPresentFor <= 365
    GROUP BY System.Risk;

    Instead of having a table that looks like the following:

    Remediated in 30 days Risk
    5 Low
    10 Medium
    15 Medium
    12 Critical
    14 High
    24 Critical
    19 High


    I'd like one that looks like this:

    Remediated in 30 days Percentage Risk
    5 0% Low
    10 67% Medium
    12 74% High
    14 50% Critical

    All the data is available in the top table to create the second table, but I can't figure out how to combine the information to generate the lower table. Can anyone please give me some direction? Thanks

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    1. need a query with the total count of vulnerabilities

    2. query with the Risk aggregation (you have that)

    3. build another query that includes first 2 (or use DCount() in place of Total query but domain aggregate functions can slow query performance although Cartesian can be slow as well) in a Cartesian relationship (no JOIN clause) so each record will have total associated and calculate percentage using regular old arithmetic.

    Otherwise, build a report that has Risk aggregate query as RecordSource and do calcs in textboxes on report. Sum Risk count in report footer and reference that texbox in detail section textbox:

    =tbxRiskCount / tbxTotal * 100
    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.

  3. #3
    albaker is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    14
    I'll have to think about this for a while. This is relatively new to me, so I have to digest it. In the meantime, thanks for the response.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I just noticed you have a total vulnerabilities query. You should not be doing UNION. Use those two queries in a Cartesian relationship query as described and see if that gets what you are looking for.
    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.

  5. #5
    albaker is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    14
    June7, again thanks. I'm very, very close. I probably should start a new post for this question, but maybe you know the fix. I've looked at Google for a couple hours so far.

    First, I'm able to create a 3rd query and calculate the values [yeah!], but in a few cases no vulnerabilities were discovered within a 30 day time period. This results in, for example, no LOW vulnerabilities for less than 30 days, but I have multiple vulnerabilities discovered over the past year. For the results of the 3 query, there is simply no entry for LOW. It ignores LOW counter in the annual count. When I use the COUNT function and there are no hits, no row is created. Is there a way to use COUNT to return a 0 if there are no hits?

    Thank you.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Not with a regular aggregate (GROUP BY) query - can only aggregate data that exists. If you want to show all vulnerabilities regardless of count and period, have to build another query with the 30-day query JOIN or UNION to dataset of all vulnerabilities.
    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.

  7. #7
    albaker is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    14
    Got it. It's working now. Thanks

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

Similar Threads

  1. Unsure how to proceed
    By JeRz in forum Database Design
    Replies: 1
    Last Post: 04-06-2018, 12:29 AM
  2. Unsure if VBA or query is best to use.
    By lccrews in forum Access
    Replies: 6
    Last Post: 04-05-2018, 05:58 PM
  3. Aggregate Results from Union query
    By hsduk101 in forum Queries
    Replies: 1
    Last Post: 07-09-2017, 10:42 AM
  4. Replies: 7
    Last Post: 12-02-2015, 08:27 AM
  5. Help in aggregate query
    By somm in forum Queries
    Replies: 1
    Last Post: 02-15-2011, 10:18 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