Access - Running count and total query

I am trying to get a running total query to work.  Here is how it works.  When a person takes a test and passes with a score of over 80, they get a credit.  When they have passed 5 they have 5 credits and are eligble for a 50 cash bonus.  One table with employee info and one with the test info with employeeID as the key field.  

This query shows an employee with a test of over 80, how do I get it to show a running total of Credits_Earned so I can indicate everytime they have accumulated 5 credits?

SELECT Employees.EmployeeID, Employees.Email, Test.TestID, Test.Test_Score, Test.Credits_Earned, Test.Test_Date
FROM Employees INNER JOIN Test ON Employees.EmployeeID = Test.EmployeeID
GROUP BY Employees.EmployeeID, Employees.Email, Test.TestID, Test.Test_Score, Test.Credits_Earned, Test.Test_Date
HAVING (((Employees.EmployeeID)=140) AND ((Test.Test_Score)>80));

ANy help is appreciated,
John
JohnMac328Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
You need a Third "ManyToMany" table:

tblStudentsTests

0
JohnMac328Author Commented:
So what would be the minimum fields in the tblStudentsTests?
0
Jeffrey CoachmanMIS LiasonCommented:
Here is a sample Database

Study the Tables, the Relationships, and the Queries.

;-)

JeffCoachman
Access-EEQ24431499StudentTestCre.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jeffrey CoachmanMIS LiasonCommented:
oops.

And here is the same sample with the Report of the students with 5 Credits

I am sure you can take the basic concepts here and adapt them to work in your database.
;-)

JeffCoachman
Access-EEQ24431499StudentTestCre.mdb
0
JohnMac328Author Commented:
Great, I can take it from here.

Thanks
0
Jeffrey CoachmanMIS LiasonCommented:
;-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.