Hey Guys,

I opened access for the first time last week and I've gotta say it's pretty amazing.

I'm trying to create a more sophisticated system for my little music school (as in, keeping records instead of losing them!). I've been doing ok but I've just been stumped by some angry sounding error messages!

Here is the problem:

I need a system to tell me how many lesson credits are remaining for each student.

I have two queries: a) lesson totals and b)attendance totals.

Here is a) Lesson Totals:
SELECT Invoice.StudentID, Invoice.InstructorID, Sum([PaymentAmount]/20) AS Credits
FROM Invoice
GROUP BY Invoice.StudentID, Invoice.InstructorID;

Here is b) Attendance Totals:
SELECT Attendance.StudentID, Attendance.InstructorID, Sum(Attendance.CreditsUsedToday) AS SumOfCreditsUsedToday
FROM Attendance
GROUP BY Attendance.StudentID, Attendance.InstructorID;

I've tried to amalgamate the two queries in a new query. The idea is something like:

SELECT [Attendance Totals].StudentID, Sum([Credits]-[sumofcreditsusedtoday]) AS Remainder
FROM [Attendance Totals], [Lesson Totals]
GROUP BY [Attendance Totals].StudentID;


Unfortunately the Remainder comes up with values that I can't interpret. If you see the attached pictures you'll see that the values in the Credits Column are small numbers but the results in the Remainder Column are related incorrectly.

Any ideas?

Attached Images

	attendancetotalsquery.gif (40.4 KB, 4 views)
	lessontotalsquery.gif (39.2 KB, 4 views)
	remainderquery.gif (32.5 KB, 3 views)