Problems with data format when running a query against two other queries RRS feed

  • Question

  • I have query 1 which results in the following:

    Vuln Remediated < 30 days
    RemediationTime Risk
    18 Critical
    10 High
    13 Medium

    Query 2 results in the following:

    Total Annual Vulns
    Total Annual Vulns Risk
    24 Critical
    27 High
    16 Low
    20 Medium

    Query 3 is as follows, and then it give me the following table.

    SELECT (([Table 1].[RemediationTime])/([Table 2].[Total Annual Vulns])) AS Percentage, [Table 2].Risk as Risk
    FROM [Table 1], [Table 2]

    Vuln Percentage
    Percentage Risk
    8.91238670694864E-02 Critical
    7.77945619335347E-02 Critical
    9.81873111782477E-03 Critical
    4.03143149982918E-02 High
    3.51896139391869E-02 High
    4.44140758455757E-03 High
    19.6666666666667 Low
    17.1666666666667 Low
    2.16666666666667 Low
    1.15686274509804 Medium
    1.00980392156863 Medium
    0.127450980392157 Medium

    What's happening is that Cell 1 of Table 1 is divided by Cells 1, 2, 3, and 4 of Table 2. I only want Cell 1 of Table 1 to be divided by the associated Cell 1 of Table 2. Can someone point me in the right direction as to how I can limit how the operations are being done? Thanks

    Tuesday, September 24, 2019 11:51 PM

All replies

  • You have a Cartesian query. Try this to avoid that:

        [Table 1].[RemediationTime]/[Table 2].[Total Annual Vulns] AS Percentage, 
        [Table 2].Risk As Risk
        [Table 1], 
        [Table 2]
        [Table 1].Risk = [Table 2].Risk

    Gustav Brock

    Wednesday, September 25, 2019 8:07 AM
  • Did you name query 1 [Table 1] and query 2 [Table 2]??

    If so.

    I suggest joining the two queries as follows:

    SELECT [RemediationTime]/[Total Annual Vulns] AS Percentage, [Table 2].Risk
    FROM [Table 1] INNER JOIN [Table 2] ON [Table 1].Risk = [Table 2].Risk;

    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

    • Edited by DriveEV Wednesday, September 25, 2019 5:45 PM
    Wednesday, September 25, 2019 12:35 PM