Microsoft KB Archive/105141
How to Get Latest Invoice for Each Customer Number
The information in this article applies to:
Customers frequently ask how to pull certain information out of a database that is the latest or most current information, usually based on some other field that may be duplicated in the same database.
While there are many different methods that might be employed, including using DO WHILE loops, creating a program or procedure with the following SELECT statements will achieve the quickest result (the INVOICES.DBF file in the TUTORIAL subdirectory has been used in this example because it gives a good approximation of this type of database structure):
SELECT invoices.cno, MAX(invoices.idate) AS idate FROM invoices; GROUP BY invoices.cno; INTO cursor temp SELECT invoices.*; FROM invoices, temp; WHERE invoices.cno = temp.cno; AND invoices.idate = temp.idate; ORDER BY invoices.cno
These statements will automatically generate a Browse window with the desired result. Additional reference words: FoxDos FoxWin 2.00 2.50 2.50a KBCategory: kbprg KBSubcategory: FxprgBrowse
Last Reviewed: June 27, 1995