SQL Query How to?

[dcarlson]

I have a database that tracks client invoices with a one to many relationship. What I want to do is pull the last invoice for a client based on the invoice date.

Such as:

strsql = "SELECT Nrcs.file_num, MAX(invoice.date), "
strsql = strsql & "Invoice.amount, Invoice.invoice "
strsql = strsql & "FROM  openclose!nrcs INNER JOIN "
strsql = strsql & "openclose!invoice ON Nrcs.file_num = Invoice.file_num "
strsql = strsql & "Group By Nrcs.file_num"

This statement gives me the max date OK but the invoice number and amount don't match up with the date.

I would appreciate any help with this. Thanks.

[Gary.Lowe]

dcarlson

Off the top of my head I think you will need to add

WHERE invoice.date = MAX(invoice.date)
(or somethign to that effect)

I think you are pulling back the first record as far as the other fields go and the max invoice date in the other field.

Hope this helps

[TheGoldenShogun]

Not sure if this will help, but in your SELECT statement, take out the MAX(invoice.date) and just SELECT invoice.date (which will give you that field instead of that date and put a WHERE clause after the innerjoin like this

strsql = "SELECT Nrcs.file_num, invoice.date, "
strsql = strsql & "Invoice.amount, Invoice.invoice "
strsql = strsql & "FROM openclose!nrcs INNER JOIN "
strsql = strsql & "openclose!invoice ON Nrcs.file_num = Invoice.file_num "
strsql = strsql & "Group By Nrcs.file_num "
strsql = strsql & "WHERE invoice.date = MAX(invoice.date)"

that way you'll SELECT the invoice.date field itself and not select the value.

[dcarlson]

What a piece of !@#$, I'm using a FoxPro database and this is how I finally got it to work.

strsql = "SELECT Nrcs.file_num, Invoice.amount, "
strsql = strsql & "Invoice.invoice, Invoice.file_num, Invoice.date "
strsql = strsql & "FROM openclose!nrcs INNER JOIN openclose!invoice ON "
strsql = strqsl & "Nrcs.file_num = Invoice.file_num "
strsql = strsql & "WHERE invoice.date = MAX(invoice.date, {01/01/1990}) "
strsql = strsql & "GROUP BY invoice.file_num"

The max function was looking for a secong argument to evaluate which one a of the dates was greater. It wouldn't let me put MAX(invoice.date) on it's own.

You guys put me on the right track. Thanks for the help.