Access Query [Resolved]

[dglienna]

I have an Access db, and I'm thinking about redoing the actual table to let it include transactions. The client has 4 sites, and wants to use a db with all the records in each site. I need a query to select each record.name and a sum of all the payments. Not sure how to do it.

cn.Execute "Insert into paymentrecord " & _
      "(LastName, Firstname, LastPayDate, BalanceDue, AmountPaid) " & _
      "Values ('" & txtLastName.Text & "','" & txtFirstName.Text & "','" & _
       CDate(txtLastPaymentDate.Text) & "','" & txtBalanceDue.Text & "','0')"

There are the fields that I use. I'm going to use a LastPayDate and Amount Paid for each time the client makes a payment. I need to group by lastname, firstname, and calculate the total payment amount. I'l load the result into a listbox like I do now, but if they add records from a different office, it will also work. I'll make the LastPaymentDate a key field so there can't be dups. Only one payment per key record allowed.

Thanks.

[si_the_geek]

How's this?

SELECT LastName, Firstname, Sum(AmountPaid)
FROM paymentrecord 
GROUP BY LastName, Firstname

(seems too easy!)

[dglienna]

Thanks. I've played around with it today, and decided that I probably need to use two tables. One with the names, and the other as a listitem table. I'll be back when I need to use a join to the two.

[si_the_geek]

That sounds good, here's a quick design for you (based on your current data):

tblPeople
  PersonID (unique ref)
  LastName
  Firstname

tblPayment
  PersonID (link to tblPeople)
  LastPayDate
  BalanceDue 
  AmountPaid

I think the tblPayment table needs a redesign, but how depends on exactly how it should work. Possibly this:

tblPaymentsMade
  PersonID (link to tblPeople)
  PayDate  (you can calculate a "last paid" date from this)
  AmountPaid

tblAmountOwed
  PersonID (link to tblPeople)
  DateOwed
  BalanceDue