>I have an Invoice table with 4 fields in which I need to pull that last invoice for a file_num.
>
>SELECT Invoice.invoice, MAX(Invoice.date), Invoice.amount, Invoice.file_num FROM openclose!invoice GROUP BY Invoice.file_num
>
>This gives me the Max date for a file_num but the invoice and amount don't match up with the date.
>
>How can I get the Last invoice based on the invoice date?
>
>Thanks in advance.
>
>Derick Carlson
Derick,
If you don't specify all non-aggregate in the Group By cluase, VFP returns the values for the non-specified fields from the record located physically last in the the table corresponding to the group. Try adding a subquery and concatenate a few fields.
Ex.
SELECT Invoice.invoice, Invoice.Date, Invoice.amount, Invoice.file_num FROM openclose!invoice where invoice+dtos(invoice.date) in ;
(select itab.invoice+dtos(max(itab.date) from invoice as itab group by itab.invoice)
That should match the date with the amount and file_num fields. As long as there aren't mulitple occurrences of invoice and date, you should get singular records in your result set.
HTH.
Larry Miller
MCSD
LWMiller3@verizon.netAccumulate learning by study, understand what you learn by questioning. -- Mingjiao