I have 3 tables (with only relavent fields):
table 1 - id, items counted during inventory
table 2 - id, nunber sold, datesold
table 3 - id, number ordered, number recieved, date received,
number back ordered, date back order recieved,
status code

I want to create a table sorted by id for all ids in table 1 with the following data, inventory count, sum of number sold for a specified date range and item code, sum of number recieved for a specified date range and item code, and a calculated field equal to inventory count minus total sold + total recieved (order and back ordered). I tried SQL Select for a left outer join and get the the totals as seperate requests but not as a single request, Is it possible to do it as on request? How do I create a field based on results of other fields. Code for table 1 and 3 follows, want to add table 2 and do calculation

SELECT pinvtcnt.cstkno ;
pinvtcnt.nphyscnt, ;
vitems.costkno, ;
SUM(IIF(BETWEEN(vitems.drecdate,;
ldJune1, ldJune3 ;
AND INLIST(vitems.citemstatus,'A','E','O','P'), ;
vitems.nrecqty,0) ) ;
AS sum_nrecqty, ;
SUM(IIF(BETWEEN(vitems.dbackrec, ;
ldJune1, ldJune30) ;
AND vitems.citemstatus = 'F', ;
vitems.nbackqty,0) ) ;
AS sum_nbackqty ;
FROM pinvtcnt LEFT OUTER ;
JOIN vitems ON pinvtcnt.cstkno = vitems.costkno;
GROUP BY pinvtcnt.cstkno