Results 1 to 9 of 9
  1. #1
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281

    Trying to Divide the results of one query byt the resuts of another query

    Greetings ~



    I've gone as far as Google will take me on this one...

    Seems simple - just trying to divide the results of two queries.
    Code:
    SELECT (SELECT CallDate, AgentID, EmpName, TeamName, SUM (TblACD.HandleTime) AS HandleTime
    FROM TblACD
    WHERE TblACD.CallDate = #10/14/2019# AND TblACD.AgentID ="HF171627"
    Group By CallDate, AgentID, EmpName, TeamName)  /
    
    
    (SELECT CallDate, AgentID, EmpName, TeamName, SUM (TblACD.CallsHandled) AS CallsHandled
    FROM TblACD
    WHERE TblACD.CallDate = #10/14/2019# AND TblACD.AgentID="HF171627"
    Group By CallDate, AgentID, EmpName, TeamName)
    Both queries work fine independantly but join them and I have just about exhausted MS's library of error messages

    This can be done? right?

    Thank You for any help...

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    in the joined table query
    table1.field1 / table2.field1

  3. #3
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Ran! - Good to hear from you!! Thanks for chiming in...

    With particular issue the table in question is the same table

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    then :
    field1/field2

  5. #5
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Hey Ran ~ I'm not followin'...

    In my mind, dividing the two fields is what I'm doing with the two SELECT Queries - which of course provide the necessary filters

    I'm trying now to work with DSUM - But I think that is going to be a waste of my time - as I can not even get this to work properly (It doesn't error - but it pulls the wrong result)
    Code:
    SELECT DSum("HandleTime","TblACD", ("CallDate = #2019-10-14#" And "AgentID ='HF171627'")) AS HandleTime
    The above formula is of course just the one side of the equation which I try to get working first before adding the second half - But, as mentioned, I can't even get this to pull correctly

    Can you throw a little bit bigger bone my way... Thanks

  6. #6
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    I'm not feelin' all warm and fuzzy and using DSUM - but I did get these two functions to work independantly but as soon as I put a / between them -

    I get a SELECT Statement includes a reserved word, argument name, missing, misspelled or is incorrect error

    When I click the OK button on the error it highlights the dividing sign...
    Code:
    SELECT DSum("HandleTime","TblACD", "[CallDate] = #2019-10-14# And [AgentID] ='HF171627'") AS HandleTime / SELECT DSum("CallsHandled","TblACD", "[CallDate] = #2019-10-14# And [AgentID] ='HF171627'") AS CallsHandled
    I'm struggling to believe this is that difficult

    I'd rather use something similiar to what I originally posted if at all possible as there will be control names used to pull specific requested data.

    I'm not giving up - I hope you guys won't either

    Thank you so much...

  7. #7
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Making progress - but not getting any closer...

    I did get the DSUM to work - but now realize I cannot (or at least I believe I cannot) add fields to the function -

    Thus it is only good for producing a one column result - again, I think
    Code:
    SELECT DSum("HandleTime","TblACD","[CallDate] = #2019-10-14# And [AgentID] ='HF171627'") / DSum("CallsHandled","TblACD","[CallDate] = #2019-10-14# And [AgentID] ='HF171627'") AS AvgHandleTime;
    So I'm back to needing to divide to SELECT Queries

    And I still need help...

  8. #8
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Trying something different - I'm trying to JOIN the DSUM results to the SELECT query

    Again, both the SELECT & the DSUM queries work fine independently - now how do I JOIN

    AND is this even a good way to do what I'm trying to do??
    Code:
    SELECT CallDate, AgentID, EmpName, TeamName, Location
    FROM TblACD
    WHERE TblACD.CallDate = #10/14/2019# AND TblACD.AgentID ="HF171627"
    Group By CallDate, AgentID, EmpName, TeamName, Location
    
    
    JOIN (SELECT DSum("HandleTime","TblACD", "CallDate = #10/14/2019# AND AgentID ='HF171627'") AS HandleTime, DSum("CallsHandled","TblACD","CallDate = #10/14/2019#
    AND AgentID ='HF171627'") AS CallsHandled, DSum("HandleTime","TblACD","CallDate = #10/14/2019# AND AgentID ='HF171627'")
    / 
    DSum("CallsHandled","TblACD","CallDate = #10/14/2019# AND AgentID ='HF171627'") AS AvgHandleTime;)
    Still needing help if anyone is out there...

    Thank You so much...

  9. #9
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    SOLUTION!!!

    The query below returns precisely what I was looking for...

    Thanks RanMan - for throwing in on this -
    Code:
    SELECT CallDate, AgentID, EmpName, TeamName, SUM (TblACD.HandleTime) AS HandleTime, SUM (TblACD.CallsHandled) AS CallsHandled, Format((HandleTime / CallsHandled),"###0") AS AvgHandleTime 
    FROM TblACD
    WHERE TblACD.CallDate = #10/14/2019# AND TblACD.AgentID="HF171627"
    Group By CallDate, AgentID, EmpName, TeamName, Location

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

Similar Threads

  1. Replies: 6
    Last Post: 08-21-2019, 07:17 AM
  2. Replies: 30
    Last Post: 03-23-2014, 10:39 AM
  3. Replies: 3
    Last Post: 05-02-2013, 10:36 AM
  4. Replies: 6
    Last Post: 05-14-2012, 07:24 AM
  5. Replies: 0
    Last Post: 03-18-2010, 01:31 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