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