General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
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
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only