I have a query that works but I need a column that tells what amount the employee will get when they complete the next group of tests. If an employee accumulates a sum of 5 credits, they are paid $50. When they complete the next 5 they are paid $100, the report should show them that the next amount they are to be paid is $50. If they had taken 15 tests, then it would show that they are to be paid $100 when they do complete the next 5 tests. The pattern is 50-100-50-100. The current query shows what they have accumulated in credits and calculated how much they have already been paid.
SELECT Employees.First_Name, Employees.Last_Name, SUM(Test.Credits_Earned) AS Credits, ROUND(SUM(Test.Credits_Earned) / 5, 0, 1)
* 50 + ROUND(SUM(Test.Credits_Earned) / 10, 0, 1) * 50 AS AmountPaid
FROM Employees INNER JOIN
Test ON Employees.EmployeeID = Test.EmployeeID
GROUP BY Employees.First_Name, Employees.Last_Name, Employees.EmployeeID
HAVING (Employees.EmployeeID IS NOT NULL) AND (SUM(Test.Credits_Earned) > 0)
ORDER BY Employees.First_Name, Employees.Last_Name