Page 1 of 1

Pulling data for report

Posted: Wed Oct 31, 2012 11:08 pm
by Harry_Jones_(Guest)
Here is my test data. FoxPro free table.__ __We want to have the total qty sold of each item in a year column__and the last price, by date, for that item in a year column.__Qty sold is a no brainer but I^m having a problem with picking __the last price for the year to go in the column. __ __Picking the highest price is works using maximum() but the highest price during the year is not necessarily the last price for the year.____Notice the price for the first item was $30 for 2007 and 2008 but during 2009 the price had changed by the last invoice date. Same scenario for the second item. ____Is a UDF the best way to go here? One to have the report look at all the invoices for a given customer, in a given year and then choose the latest invoice price to display/print.____If a UDF is the best way I don_t have a clue how to write it!____custno item invdte price qtyshp__"BCEL01","2731201GW31-15T",01/23/2007,30.00000,30.000000__"BCEL01","2731201GW31-15T",02/27/2007,30.00000,30.000000__"BCEL01","2731201GW31-15T",05/29/2007,30.00000,30.000000__"BCEL01","2731201GW31-15T",10/26/2007,30.00000,30.000000__"BCEL01","2731201GW31-15T",11/30/2007,30.00000,30.000000__"BCEL01","2731201GW31-15T",01/23/2008,30.00000,30.000000__"BCEL01","2731201GW31-15T",07/17/2008,30.00000,30.000000__"BCEL01","2731201GW31-15T",04/22/2009,30.00000,20.000000__"BCEL01","2731201GW31-15T",06/11/2009,29.50000,21.000000__"BCEL01","2773501GL-030T",05/29/2007,35.30000,5.000000__"BCEL01","2773501GL-030T",07/03/2007,35.30000,5.000000__"BCEL01","2773501GL-030T",09/28/2007,35.30000,5.000000__"BCEL01","2773501GL-030T",04/15/2008,35.30000,5.000000__"BCEL01","2773501GL-030T",10/16/2008,35.30000,5.000000__"BCEL01","2773501GL-030T",11/26/2008,35.30000,1.000000__"BCEL01","2773501GL-030T",04/13/2009,35.30000,10.000000__"BCEL01","2773501GL-030T",06/22/2009,34.36000,15.000000__"BCEL01","2773501GL-030T",08/24/2009,34.36000,15.000000______ Any help or advice would be appreciated.