Get the lastest date from purchasing history

C

Carlos

I have a table with Part Numbers for a Tool Crib, each PN has a Qty
field and a Reorder level. every day I need to generate a report with
all the parts that Qty<=ReorderPoint, at this point all is easy.

SQL sentence : SELECT tbParts.PN FROM tbParts WHERE
tbParts.qty<=tbparts.Reorder (Query name PN2buy)

also I have a sentence with purchasing history, What I need is to get
all the PN's that are under the re-order point and match those pn with
the LASTEST purchase record in table PNPurchase.

since there could exist many purchases for each PN we can get a
recordset as follow:

PN Vendr# Price Date
RODCOP0002 91602 $12.00 31-Jul-04
RODCOP0002 91602 $11.00 31-Jul-04
RODCOP0002 91522 $0.00 29-Jul-04
RODCOP0002 99999 $0.00 29-Jul-04
RODCOP0003 91602 $21.00 31-Jul-04
RODCOP0003 91522 $0.00 29-Jul-04
RODCOP0003 99999 $0.00 29-Jul-04
RODCOP0004
RODCOP0005
RODRET0001 80447 $0.00 29-Jul-04
RODRET0001 91522 $0.00 29-Jul-04
RODRET0001 91602 $0.00 29-Jul-04
RODRET0001 92057 $0.00 29-Jul-04
RODRET0002 91602 $1.00 31-Jul-04
RODRET0002 91522 $5.00 29-Jul-04
RODRET0002 91522 $0.00 29-Jul-04

I need to get the lastest purchase for each PN and the desired result
shuld looks as follow:

PN Vendr# Price Date
RODCOP0002 91602 $12.00 31-Jul-04
RODCOP0003 91602 $21.00 31-Jul-04
RODCOP0004
RODCOP0005
RODRET0001 80447 $2.00 29-Jul-04
RODRET0002 91602 $1.00 31-Jul-04

this is the SQL code for the first recordset:
SELECT DISTINCT PN2buy.PN, tbPUR.Prov, tbPUR.Precio, tbPUR.Fecha
FROM PN2buy LEFT JOIN tbPUR ON PN2buy.PN = tbPUR.PN
ORDER BY PN2buy.PN, tbPUR.Fecha DESC, tbPUR.Precio DESC;

can someone help me I have been dealing with these for days
regards
 
Top