MS Access Query - total sum?

[mrasad]

Hello,

I've create a very simple database which records payments made for a course. The students can pay in five instalments (payment 1, payment 2, payment 3, payment 4, and payment 5). There is total payment field, and an outstanding balance.

I have currently got the fields in my form calculating the five payments and then placing the results in the total payment and outstanding payment. However, these are not being written back to the table.

Is it possible to either have the total payment and outstanding payments written back to the tables which would make writing a query to find out total payments and outstanding payment simple to write OR can a sum be written in a query to that a total payment and outstanding balance are displayed?

Please let me know if none of the above makes sense. Any help will be greatly received.

Asad

 

[SkipVought]

mrasad,

Generally speaking, summary values are NOT stored in detail tables, but are REPORTED via a query on demand.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[mrasad]

what would a select statement look like to gain infomation above?

 

[SkipVought]

My StudentPayment table might look like this
[tt]
StudentID
PaymentDate
PaymentAmount
[/tt]
My StudentMaster table might look like this
[tt]
StudentID
AmountDue
[/tt]

Then the SQL would be...

sSQL = "Select sp.StudentID, sp.Sum(PaymentAmount), sm.AmountDue-sp.Sum(PaymentAmount) " & _ 
  "From StudentPayment sp, StudentMaster sm " & _ 
  "Where sp.StudentID=sm.StudentID " & _
  "Group By sp.StudentID "

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884