Data from last transaction needed in another table
From: Roy Dean Leighton Jr. (anonymous_at_discussions.microsoft.com)
Date: 08/13/04
- Next message: Bob: "finding records using a Date expresion"
- Previous message: Hugo Kornelis: "Re: Order By - Conditional"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 13 Aug 2004 15:41:47 -0700
If I can explain this well enough - I need to update two
fields in one table with data from the last inventory
transaction entered for a serialized item. The last
inventory transaction is tied to the serialized item via
another table that associates the inventory transaction id
with the serialized item. Here is the script that works in
Access when querying a SQL server. I need it converted to
a stored procedure so that it can be executed on the SQL
Server.
INSERT INTO tblTraceTemp ( PART_ID, ID, NUMBERING_ID,
OUT_QTY, IN_QTY, REPORTED_QTY, ASSIGNED_QTY, APROPERTY_1,
APROPERTY_2, APROPERTY_3, APROPERTY_4, APROPERTY_5,
NPROPERTY_1, NPROPERTY_2, NPROPERTY_3, NPROPERTY_4,
NPROPERTY_5, COMMENTS, EXPIRATION_DATE, CREATE_DATE,
DISP_IN_QTY, DISP_OUT_QTY, UNAVAILABLE_QTY, OWNER_ID,
LOT_ID, SERIAL_ID, COMMITTED_QTY, PRODUCTION_DATE,
RECEIVE_BY_DATE, AVAILABLE_DATE, SHIP_BY_DATE )
SELECT tblTraceUpdate.PART_ID, tblTraceUpdate.ID,
tblTraceUpdate.NUMBERING_ID, tblTraceUpdate.OUT_QTY,
tblTraceUpdate.IN_QTY, tblTraceUpdate.REPORTED_QTY,
tblTraceUpdate.ASSIGNED_QTY, tblTraceUpdate.APROPERTY_1,
tblTraceUpdate.APROPERTY_2,
[qryTraceBarsOnHandAccessLastLocation].WAREHOUSE_ID AS
APROPERTY_3,
[qryTraceBarsOnHandAccessLastLocation].LOCATION_ID AS
APROPERTY_4, tblTraceUpdate.APROPERTY_5,
tblTraceUpdate.NPROPERTY_1, tblTraceUpdate.NPROPERTY_2,
tblTraceUpdate.NPROPERTY_3, tblTraceUpdate.NPROPERTY_4,
tblTraceUpdate.NPROPERTY_5, tblTraceUpdate.COMMENTS,
tblTraceUpdate.EXPIRATION_DATE,
tblTraceUpdate.CREATE_DATE, tblTraceUpdate.DISP_IN_QTY,
tblTraceUpdate.DISP_OUT_QTY,
tblTraceUpdate.UNAVAILABLE_QTY, tblTraceUpdate.OWNER_ID,
tblTraceUpdate.LOT_ID, tblTraceUpdate.SERIAL_ID,
tblTraceUpdate.COMMITTED_QTY,
tblTraceUpdate.PRODUCTION_DATE,
tblTraceUpdate.RECEIVE_BY_DATE,
tblTraceUpdate.AVAILABLE_DATE, tblTraceUpdate.SHIP_BY_DATE
FROM tblTraceUpdate LEFT JOIN (SELECT
qryTraceBarsOnHandInventoryTrans.PART_ID,
qryTraceBarsOnHandInventoryTrans.TRACE_ID, Last
(qryTraceBarsOnHandInventoryTrans.WAREHOUSE_ID) AS
WAREHOUSE_ID, Last
(qryTraceBarsOnHandInventoryTrans.LOCATION_ID) AS
LOCATION_ID
FROM (SELECT tblTRACE_INV_TRANSUpdate.PART_ID,
tblTRACE_INV_TRANSUpdate.TRACE_ID,
tblTRACE_INV_TRANSUpdate.TRANSACTION_ID,
qryTraceBarsOnHand.NET, tblTRACE_INV_TRANSUpdate.QTY,
tblTRACE_INV_TRANSUpdate.CREATE_DATE,
tblINVENTORY_TRANSUpdate.WAREHOUSE_ID,
tblINVENTORY_TRANSUpdate.LOCATION_ID
FROM (SELECT tblTraceUpdate.PART_ID, tblTraceUpdate.ID,
([IN_QTY]-[OUT_QTY]) AS NET
FROM tblTraceUpdate
WHERE (((([IN_QTY]-[OUT_QTY]))>0))
ORDER BY tblTraceUpdate.PART_ID, tblTraceUpdate.ID,
([IN_QTY]-[OUT_QTY])) as qryTraceBarsOnHand INNER JOIN
(tblTRACE_INV_TRANSUpdate INNER JOIN
tblINVENTORY_TRANSUpdate ON
tblTRACE_INV_TRANSUpdate.TRANSACTION_ID =
tblINVENTORY_TRANSUpdate.TRANSACTION_ID) ON
(qryTraceBarsOnHand.ID =
tblTRACE_INV_TRANSUpdate.TRACE_ID) AND
(qryTraceBarsOnHand.PART_ID =
tblTRACE_INV_TRANSUpdate.PART_ID)
WHERE (((tblTRACE_INV_TRANSUpdate.QTY)>0))
ORDER BY tblTRACE_INV_TRANSUpdate.PART_ID,
tblTRACE_INV_TRANSUpdate.TRACE_ID,
tblTRACE_INV_TRANSUpdate.TRANSACTION_ID) AS
qryTraceBarsOnHandInventoryTrans
GROUP BY qryTraceBarsOnHandInventoryTrans.PART_ID,
qryTraceBarsOnHandInventoryTrans.TRACE_ID) AS
qryTraceBarsOnHandAccessLastLocation ON (tblTraceUpdate.ID
= [qryTraceBarsOnHandAccessLastLocation].TRACE_ID) AND
(tblTraceUpdate.PART_ID =
[qryTraceBarsOnHandAccessLastLocation].PART_ID);
- Next message: Bob: "finding records using a Date expresion"
- Previous message: Hugo Kornelis: "Re: Order By - Conditional"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
