simple query w/two tables 1

[Goalie3533]

i need to perform a query using 2 tables that ultimately grabs the last time a customer made a payment for one of their bills(invoices).

the first table(i'll call it tblInvoices) has all of the customer invoice #'s. now, since a customer can make several installments for their invoice, as opposed to one large one, we have a 2nd table(tblPayments) that has a list of each invoice payment made for all of our customers. this table also contains a field that lists the date this payment was received. these 2 tables can be joined by the invoice #.

so what i need to do is select the last payment date(from tblPayments) for each and every invoice in tblInvoices.

how can i go about creating this select statement?

thanks in advance.

-goalie35

 

[Pattycake245]

select a.invoice, max(b.paymentdate)
from tblinvoices a left join tblPayments b
on a.invoice=b.invoice
group by a.invoice
order by a.invoice

Do you want to display all invoices even if no payments have been made with a dummy date? If so,

select a.invoice, isnull(max(b.paymentdate),'3000-01-01')
from tblinvoices a left join tblPayments b
on a.invoice=b.invoice
group by a.invoice
order by a.invoice

Tim

 

[Goalie3533]

Thanks for the help so far Pattycake but unfortunately, I only explained half the problem because I felt once I resolved the issue I mentioned, the rest of my query would work fine however I'm still having a problem so perhaps I should explain the entire issue I'm having.

Recently, we added a new "date" field to our "Invoices" table (tblInvoices). This new field needs to be UPDATED with the dates of the last time a client made a payment towards their invoice(which is stored on tblPayments).

So with that in mind and with the help of the code you provided me, my query so far looks like this:
----------------------------------------------------------
update tblInvoices set BillLastPaymentDate = (select max(a.SinglePaymentDate) from tblPayments a left join tblInvoices b
on a.PaymentInvoiceId=b.InvoiceId group by a.PaymentInvoiceId)
-----------------------------------------------------

My problem is I'm getting an error saying:
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

Any idea how I can fix this? Thanks again.

-Goalie35