Tamar,
I tried to read your article but there seems to be an issue with the link. I've included a copy of the select statement as it stands now. I've also included the structure for the ICSUPL01.DBF (Inventory Supplier) and APVEND01.DBF (Accounts Payable Vendor) tables. The ultimate goal is to have the vendor number from ICSUPL01 for the last received order for each inventory item in ICITEM01.DBF. I was thinking I would use the LRECDTE (Last Received Date) field in ICSUPL01.DBF to determine which ICSUPL01.DBF record to reference for each inventory item. I just can't figure out how to return the VENDNO from ICSUPL01.DBF and in turn the COMPANY for that VENDNO from APVEND01.DBF.
SELECT Icitem01.item,;
Icitem01.itmdesc,;
Icitem01.plinid,;
Icitem01.lookup,;
Icitem01.stkumid,;
Icitem01.altitm1,;
Icitem01.avgcost,;
Icitem01.lstcost,;
Icitem01.ilsale,;
Icitem01.itmclss,;
Icitem01.comcode,;
Iciloc01.loctid,;
Iciloc01.lonhand,;
Iciloc01.lsoaloc,;
Iciloc01.lvprtno,;
Iciloc01.lsupplr,;
Iciloc01.lonordr,;
Iciloc01.ORDERPT,;
Iciloc01.YTDSLQT,;
CAST(0 AS N(15,5)) AS MONTHSLQT;
FROM icitem01;
LEFT JOIN iciloc01;
ON Icitem01.item = Iciloc01.item;
WHERE Icitem01.stkcode = 'Y' AND len(alltrim(Icitem01.item)) > 0;
ORDER BY Icitem01.item, Iciloc01.loctid
Structure for ICSUPL01.DBF
ITEM C(15)
VPARTNO C(15)
VENDNO C(6)
LRECDTE D(8)
LEAD N(3,0)
ORDMIN N(12,3)
ORDINCR N(12,3)
LASTCST N(15,5)
PDISRAT N(7,3)
YTDQTY N(12,3)
RATING C(3)
SUPMEMO M(4)
ADDUSER C(4)
ADDDATE D(8)
ADDTIME C(8)
LCKSTAT C(1)
LCKUSER C(4)
LCKDATE D(8)
LCKTIME C(8)
Structure for APVEND01.DBF
VENDNO C(6,0)
COMPANY C(35,0)
CONTACT C(20,0)
TITLE C(20,0)
ADDRESS1 C(30,0)
ADDRESS2 C(30,0)
CITY C(20,0)
STATE C(10,0)
ZIP C(10,0)
COUNTRY C(15,0)
PHONE C(20,0)
PHONE2 C(20,0)
FAXNO C(20,0)
EMAIL C(35,0)
CODE C(2,0)
CTYPE C(8,0)
BUYER C(2,0)
PRIORITY C(1,0)
LPAYDATE D(8,0)
LRECDATE D(8,0)
TAX N(7,3)
LIMIT Y(8,4)
DEBIT Y(8,4)
BALANCE Y(8,4)
HIBALANCE Y(8,4)
PTDPUR Y(8,4)
YTDPUR Y(8,4)
YTDPAY Y(8,4)
YTDDIS Y(8,4)
YTDADJ Y(8,4)
YTD1099 Y(8,4)
LYR1099 Y(8,4)
YR21099 Y(8,4)
PMTDAYS N(6,1)
LPAYAMT Y(8,4)
APRDIS Y(8,4)
APRPAY Y(8,4)
OPENPO Y(8,4)
TYP1099 C(5,0)
HISTORY C(1,0)
TAXCODE C(1,0)
CURRENCY C(3,0)
DEFACCT C(24,0)
PAYACCT C(24,0)
TAXPID C(11,0)
PTERMS C(20,0)
PDISC N(7,3)
PDAYS N(3,0)
PNET N(3,0)
COMMENT C(65,0)
VNDMEMO M(4,0)
ADDUSER C(4,0)
ADDDATE D(8,0)
ADDTIME C(8,0)
LCKSTAT C(1,0)
LCKUSER C(4,0)
LCKDATE D(8,0)
LCKTIME C(8,0)
BANKID C(6,0)
BANKACC C(15,0)
LATITUDE N(9,5)
LONGITUDE N(10,6)
VENDGEN G(4,0)
TIA
Jeff