Results 1 to 7 of 7
  1. #1
    RunTime91 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2014
    Posts
    281

    Trying to divide results of 2 SELECT queries

    Greetings ~



    I am trying to divide the results of 2 SELECT queries - I'm working in SSMS then will move the code over to Access VBA

    Below are some of my attempts - The first two queries are the two queries I am trying to divide and both produce the correct values which are 6 & 10

    Thus, the result of the division should be 60.00% - However, all of the division queries are returning 0.00%

    Code:
    SELECTSUM(Points)AS Score
    FROM Tri.KCQuizAnswers
    WHERE QuizID = 1 AND EmpID ='FS115436'
    
    SELECTCOUNT(QuizID)ASCount
    FROM Tri.KCQuizMain
    WHERE QuizID = 1
    
    
    SELECTFORMAT(SUM(Points)/COUNT(A.QuizID),'p')AS [Score]
    FROM Tri.KCQuizMain A JOIN Tri.KCQuizAnswers B ON A.QuizID = B.QuizID
    WHERE A.QuizID = 1 AND EmpID ='FS115436'
    
    
    SELECTFORMAT((SELECTSUM(Points)FROM Tri.KCQuizAnswers WHERE QuizID = 1 AND EmpID ='FS115436')
    /
    (SELECTCOUNT(QuizID)FROM Tri.KCQuizMain WHERE QuizID = 1),'p')As Boo
    
    
    SELECTFORMAT((A.Foo / B.Poo),'p')As Boo
    FROM
    (SELECTSUM(Points)As Foo FROM Tri.KCQuizAnswers WHERE QuizID = 1 AND EmpID ='FS115436') A
    ,(SELECTCOUNT(QuizID)AS Poo FROM Tri.KCQuizMain WHERE QuizID = 1) B
    


    I have tried CAST as well but I am not familiar with how CAST works and thus I'm certain I wasn't doing it correctly

    As always Thank You for any assistance

    Rt91

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    AFAIK, Access does not recognize CAST() function.

    This would probably be simpler to do in Access report design.

    Suggest not formatting in query. Format function returns a string, not a number and this can cause issue with subsequent calcs.

    Advise not to use reserved words as names for objects - Count is a reserved word.

    Provide sample data and desired result. If you want to provide db for analysis, follow instructions at bottom of my post.
    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
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try

    SELECT FORMAT((A.Foo / CAST(B.Poo as money)),'p') As Boo
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    hci_jon is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Montgomery, IL
    Posts
    9
    Your basic issue is that the Count is an integer and therefore the answer will always return an integer. Since the answer is always a percentage (less than one, between 0.00 and 1.00) it will drop the decimal and you will get either a 1 if it is 100% or a 0 if it is less than 100%.

    You will want to cast the Count ideally to the same numeric type that you are summing. Probably a double precision float. In Access you would use the CDbl(Count([field])) function to do this for a JET database, but if you are using a passthru query to SQL Server, you would use CAST(Count([field]) as Float)

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I tested Sum([integer field]) / Count("*") and get result of 23.75.

    So don't Format() data in query. Format on report.
    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.

  6. #6
    RunTime91 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2014
    Posts
    281
    Wow! Great Responses and Explanations!!

    Below is what I went with - June you can use CAST in Access and hci - Yes, this is a Pass-Thru - And Paul - as always - Thanks for getting the ball rolling on this one

    June - you solution would work brilliant - except the recordset from the query is simply populating a TextBox rather than a report

    This build is a training tool that simply allows Sups/Mgrs & Trainers to create product knowledge quiz's -

    The recordset will simply let the agent taking the quiz know what his/her accuracy rate is.

    Code:
    StrSQLScore = "SELECT FORMAT((A.Foo / CAST(B.Poo As Float)),'p') As Boo " & _
    "From (SELECT SUM(Points) As Foo FROM Tri.KCQuizAnswers WHERE QuizID = 1 AND EmpID = 'FS115436') A " & _
    ",(SELECT COUNT(QuizID) AS Poo FROM Tri.KCQuizMain WHERE QuizID = 1) B"
    


    Thank You Everyone! for your help!!

    Onto the next challenge -


  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Well, I tried Cast() in a regular Access query and it is not recognized. I am still with Access 2010 so unless something has changed...

    I have no experience with SQLServer.

    Textbox on form or report - format textbox, not field in query is my preference.

    But glad you have a solution.
    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.

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

Similar Threads

  1. Replies: 8
    Last Post: 12-10-2019, 04:45 PM
  2. SQL Select + Loop through results in VBA
    By aebstract in forum Programming
    Replies: 2
    Last Post: 11-15-2019, 03:39 PM
  3. Wrong results with SELECT TOP
    By Claudio in forum Queries
    Replies: 13
    Last Post: 03-10-2019, 08:59 PM
  4. Replies: 5
    Last Post: 05-14-2014, 01:17 PM
  5. Different results in queries
    By Vic in forum Programming
    Replies: 6
    Last Post: 11-06-2009, 10:58 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