Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Jun 2010
    Location
    Quincy, MA
    Posts
    62
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Nested SQL query

    Hi,
    I'm trying to build a nested SQL query for use in Access. I need to look over a period of time and indicate how many times an action was completed Same Day, <= 5 Days, <=30 Days <=60 Days, <=90 Days and >90 Days. I can successfully build the query to return one value at a time:

    SELECT Qry_Aging_Subtotals_1.[Configuration Item Name], Sum(Qry_Aging_Subtotals_1.[CountOfConfiguration Item Name]) AS Same_Day
    FROM Qry_Aging_Subtotals_1
    WHERE (((Qry_Aging_Subtotals_1.Expr1)=0))
    GROUP BY Qry_Aging_Subtotals_1.[Configuration Item Name];

    There must be a way to combine each of the silos but I cannot figure it out. Any assistance is greatly appreciated. Thanks,
    Thanos

  2. #2
    jwoods
    Guest
    You can use COUNT() or SUM(), which I see you're using, depending on the result you want.

    I might COUNT the number of employees in a department, and SUM their unused vacation days.

  3. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    There is one thing I don't understand - if you are interested just in the number of times the action was taken, can't you write a query that simply does that regardless of the period the action was taken? Why add the results of all other queries?

    In any case, if you want to go with the latter, you can get what you want using a UNION query to assemble all the results into a single query and then sum those results:

    Code:
    SELECT [Configuration Item Name], SUM(NumberofActions) FROM (
    
    SELECT Qry_Aging_Subtotals_1.[Configuration Item Name], Sum(Qry_Aging_Subtotals_1.[CountOfConfiguration Item Name]) AS NumberofActions
    FROM Qry_Aging_Subtotals_1
    WHERE (((Qry_Aging_Subtotals_1.Expr1)=0))
    GROUP BY Qry_Aging_Subtotals_1.[Configuration Item Name];
    
    UNION
    
    (next query)
    
    UNION
    
    (Next Query)
    
    )
    
    GROUP BY [Configuration Item Name];
    Notice the change in the name of the returned 2n column, as it is important for the SUM in the final main query.
    Rui
    -------
    R4

  4. #4
    Star Lounger
    Join Date
    Jun 2010
    Location
    Quincy, MA
    Posts
    62
    Thanks
    2
    Thanked 0 Times in 0 Posts
    ruirib - thank you for your response. To try and answer your question, the resulting grid of information across applications (4) and date silos will be distributed to my audience. Rather than running the query changing it and running it again, this is what I came up with. I'm not the sharpest tool in the Access / SQL shed. That said, when I try to work this I get a Syntax Error In Union Query. The adjusted SQL statement is shown below and no doubt, I've done something whacky. Thanks, Thanos

    SELECT Qry_Aging_Subtotals_1.[Configuration Item Name], Sum(Qry_Aging_Subtotals_1.[CountOfConfiguration Item Name]) AS NumberofActions
    FROM Qry_Aging_Subtotals_1
    WHERE (((Qry_Aging_Subtotals_1.Expr1)=0))
    GROUP BY Qry_Aging_Subtotals_1.[Configuration Item Name];

    UNION

    (Qry_Aging_Subtotals_B)

    UNION

    (Qry_Aging_Subtotals_1_C)

    )

    GROUP BY [Configuration Item Name];

  5. #5
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Unions do not work like that. You can probably solve your problem by using a SELECT clause in the added queries. You were also missing the first query, which is essential to add the results from the intermediate queries:
    Code:
    SELECT [Configuration Item Name], SUM(NumberOfActions) FROM (
    
    SELECT Qry_Aging_Subtotals_1.[Configuration Item Name], Sum(Qry_Aging_Subtotals_1.[CountOfConfiguration Item Name]) AS NumberofActions
    FROM Qry_Aging_Subtotals_1
    WHERE (((Qry_Aging_Subtotals_1.Expr1)=0))
    GROUP BY Qry_Aging_Subtotals_1.[Configuration Item Name];
    
    UNION
    
    SELECT * FROM [Qry_Aging_Subtotals_B]
    
    UNION
    
    SELECT * FROM [Qry_Aging_Subtotals_1_C]
    
    )
    
    GROUP BY [Configuration Item Name];
    To create such a query, you need to edit your Access query in SQL mode.
    Rui
    -------
    R4

  6. #6
    Star Lounger
    Join Date
    Jun 2010
    Location
    Quincy, MA
    Posts
    62
    Thanks
    2
    Thanked 0 Times in 0 Posts
    ruirib - I', sorry to monopolize your time. I see / grasp what it is we're trying to do, but again, a syntax error, "Syntax Error in FROM clause". The cursor jumps to the end of this line, GROUP BY Qry_Aging_Subtotals_1.[Configuration Item Name]; Yet I don't understand why. Thank you for your continued assistance, Thanos

  7. #7
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    I can't know what the error is without seeing the syntax of the full query here. Get the query into SQL view and then copy the whole statement and paste it here.
    Rui
    -------
    R4

  8. #8
    Star Lounger
    Join Date
    Jun 2010
    Location
    Quincy, MA
    Posts
    62
    Thanks
    2
    Thanked 0 Times in 0 Posts
    ELECT [Configuration Item Name], SUM(NumberOfActions) FROM (

    SELECT Qry_Aging_Subtotals_1.[Configuration Item Name], Sum(Qry_Aging_Subtotals_1.[CountOfConfiguration Item Name]) AS NumberofActions
    FROM Qry_Aging_Subtotals_1
    WHERE (((Qry_Aging_Subtotals_1.Expr1)=0))
    GROUP BY Qry_Aging_Subtotals_1.[Configuration Item Name];


    UNION

    SELECT * FROM [Qry_Aging_Subtotals_1_A]

    UNION

    SELECT * FROM [Qry_Aging_Subtotals_1_B]

    UNION

    SELECT * FROM [Qry_Aging_Subtotals_1_C]

    UNION

    SELECT * FROM [Qry_Aging_Subtotals_1_D]

    UNION

    SELECT * FROM [Qry_Aging_Subtotals_1_E]

    UNION

    SELECT * FROM [Qry_Aging_Subtotals_1_F]

    )

    GROUP BY [Configuration Item Name];

  9. #9
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Try this:

    Code:
    SELECT [Configuration Item Name], SUM(NumberOfActions) FROM (
    
    SELECT Qry_Aging_Subtotals_1.[Configuration Item Name], Sum(Qry_Aging_Subtotals_1.[CountOfConfiguration Item Name]) AS NumberofActions
    FROM Qry_Aging_Subtotals_1
    WHERE (((Qry_Aging_Subtotals_1.Expr1)=0))
    GROUP BY Qry_Aging_Subtotals_1.[Configuration Item Name];
    
    
    UNION
    
    SELECT * FROM [Qry_Aging_Subtotals_1_A]
    
    UNION
    
    SELECT * FROM [Qry_Aging_Subtotals_1_B]
    
    UNION
    
    SELECT * FROM [Qry_Aging_Subtotals_1_C]
    
    UNION
    
    SELECT * FROM [Qry_Aging_Subtotals_1_D]
    
    UNION
    
    SELECT * FROM [Qry_Aging_Subtotals_1_E]
    
    UNION
    
    SELECT * FROM [Qry_Aging_Subtotals_1_F]
    
    ) As T
    
    GROUP BY [Configuration Item Name];
    Rui
    -------
    R4

  10. #10
    Star Lounger
    Join Date
    Jun 2010
    Location
    Quincy, MA
    Posts
    62
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Same issue unfortunately. "Syntax Error in FROM clause". The cursor jumps to the end of this line,
    GROUP BY Qry_Aging_Subtotals_1.[Configuration Item Name];

  11. #11
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    There were some semi-colons in the SQL, where they should not be. Please remove them, as below.
    Code:
    SELECT [Configuration Item Name], SUM(NumberOfActions) FROM (
    
    SELECT Qry_Aging_Subtotals_1.[Configuration Item Name], Sum(Qry_Aging_Subtotals_1.[CountOfConfiguration Item Name]) AS NumberofActions
    FROM Qry_Aging_Subtotals_1
    WHERE (((Qry_Aging_Subtotals_1.Expr1)=0))
    GROUP BY Qry_Aging_Subtotals_1.[Configuration Item Name]
    
    
    UNION
    
    SELECT * FROM [Qry_Aging_Subtotals_1_A]
    
    UNION
    
    SELECT * FROM [Qry_Aging_Subtotals_1_B]
    
    UNION
    
    SELECT * FROM [Qry_Aging_Subtotals_1_C]
    
    UNION
    
    SELECT * FROM [Qry_Aging_Subtotals_1_D]
    
    UNION
    
    SELECT * FROM [Qry_Aging_Subtotals_1_E]
    
    UNION
    
    SELECT * FROM [Qry_Aging_Subtotals_1_F]
    
    ) As T
    
    GROUP BY [Configuration Item Name]
    Rui
    -------
    R4

  12. #12
    Star Lounger
    Join Date
    Jun 2010
    Location
    Quincy, MA
    Posts
    62
    Thanks
    2
    Thanked 0 Times in 0 Posts
    ruirib - You are amazing! That did work things out and no syntax issues. Thank you!

  13. #13
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Great .
    Rui
    -------
    R4

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •