VbzCart/queries/qryRstks info

Details

 * Returns: Information about restock requests and receipts, one line per receipt (or request, if nothing received)
 * Requires: ,
 * Used by: (MS Access) sfrm_SuppRstks
 * History:
 * 2008-11-30 Created for new restock process
 * Notes:
 * Creates one list by left-joining received restocks with their requests, another list of requests with no receipts, and then UNIONs them together

SQL
CREATE OR REPLACE VIEW qryRstks_info AS SELECT rq.ID AS ID_Req, rq.ID_Supplier, rq.PurchOrdNum, rq.SuppOrdNum, rq.WhenCreated, rq.WhenOrdered, rq.WhenKilled, rq.WhenOrphaned, rq.WhenClosed, rq.Notes AS NotesReq, rc.ID AS ID_Rcd, rc.SuppInvcNum, rc.WhenReceived, rc.TotalInvMerch, rc.TotalInvFinal, rc.Notes AS NotesRcd FROM rstk_rcd AS rc LEFT JOIN rstk_req AS rq ON rc.ID_Restock=rq.ID UNION SELECT rq.ID AS ID_Req, rq.ID_Supplier, rq.PurchOrdNum, rq.SuppOrdNum, rq.WhenCreated, rq.WhenOrdered, rq.WhenKilled, rq.WhenOrphaned, rq.WhenClosed, rq.Notes AS NotesReq, rc.ID AS ID_Rcd, rc.SuppInvcNum, rc.WhenReceived, rc.TotalInvMerch, rc.TotalInvFinal, rc.Notes AS NotesRcd FROM rstk_req AS rq LEFT JOIN rstk_rcd AS rc ON rc.ID_Restock=rq.ID WHERE rc.ID IS NULL;