LittleNick
Technical User
Hello everyone,
Let say I have these several tables, tblCustomerTransaction, tblCustomerShip, tblItemsType, tblItemsCost as follow:
tblCustomerTransaction
ID Item Orderdate Unit UnitCost
02 Tool 01/01/10 10
02 ToolKit 01/01/10 10
02 Electro 01/01/10 5
05 Games 01/01/10 2
05 Books 01/01/10 5
08 ToolKit 01/01/10 20
08 DVD 01/01/10 1
08 Tool 01/01/10 20
09 Tool 01/01/10 6
tblCustomerShip
ID TobeShipped Date
02 Yes 01/01/10
04 No 08/31/09
05 Yes 01/01/10
06 No 01/01/10
08 Yes 01/01/10
09 Yes 01/01/10
tblItemsOrdered
ID ItemID
02 A1
04 H3
05 C2
08 D5
09 R8
tblItemsCost
ItemID Item Cost
A1 Books $2.00
A1 Tools $3.00
A1 ToolKit $4.00
H3 DVD $5.00
H3 Books $6.00
C2 Games $7.00
C2 Books $8.00
D5 DVD $9.00
D5 Tools $10.00
D5 ToolKit $11.00
R8 Books $12.00
R8 DVD $13.00
Now I want to run an update to update tblCustomerTransaction.UnitCost of the Tool to the Cost from tblItmesCost only to the Customer (ID) who has both Tool and ToolKit (together) in the order and TobeShipped is ‘Yes’ and the date tblCustomerTransaction.OrderDate = tblCustomerShip.Date. I hope this is not too confusing, but the result I would like to obtain is after running the update, the column UnitCost in tblCustomerTransaction shoul look like below
tblCustomerTransaction
ID Item Orderdate Unit UnitCost
02 Tool 01/01/10 10 $3.00
02 ToolKit 01/01/10 10 $4.00
02 Electro 01/01/10 5
05 Games 01/01/10 2
05 Books 01/01/10 5
08 ToolKit 01/01/10 20 $11.00
08 DVD 01/01/10 1
08 Tool 01/01/10 20 $10.00
09 Tool 01/01/10 6
Appreciate your helps.
Let say I have these several tables, tblCustomerTransaction, tblCustomerShip, tblItemsType, tblItemsCost as follow:
tblCustomerTransaction
ID Item Orderdate Unit UnitCost
02 Tool 01/01/10 10
02 ToolKit 01/01/10 10
02 Electro 01/01/10 5
05 Games 01/01/10 2
05 Books 01/01/10 5
08 ToolKit 01/01/10 20
08 DVD 01/01/10 1
08 Tool 01/01/10 20
09 Tool 01/01/10 6
tblCustomerShip
ID TobeShipped Date
02 Yes 01/01/10
04 No 08/31/09
05 Yes 01/01/10
06 No 01/01/10
08 Yes 01/01/10
09 Yes 01/01/10
tblItemsOrdered
ID ItemID
02 A1
04 H3
05 C2
08 D5
09 R8
tblItemsCost
ItemID Item Cost
A1 Books $2.00
A1 Tools $3.00
A1 ToolKit $4.00
H3 DVD $5.00
H3 Books $6.00
C2 Games $7.00
C2 Books $8.00
D5 DVD $9.00
D5 Tools $10.00
D5 ToolKit $11.00
R8 Books $12.00
R8 DVD $13.00
Now I want to run an update to update tblCustomerTransaction.UnitCost of the Tool to the Cost from tblItmesCost only to the Customer (ID) who has both Tool and ToolKit (together) in the order and TobeShipped is ‘Yes’ and the date tblCustomerTransaction.OrderDate = tblCustomerShip.Date. I hope this is not too confusing, but the result I would like to obtain is after running the update, the column UnitCost in tblCustomerTransaction shoul look like below
tblCustomerTransaction
ID Item Orderdate Unit UnitCost
02 Tool 01/01/10 10 $3.00
02 ToolKit 01/01/10 10 $4.00
02 Electro 01/01/10 5
05 Games 01/01/10 2
05 Books 01/01/10 5
08 ToolKit 01/01/10 20 $11.00
08 DVD 01/01/10 1
08 Tool 01/01/10 20 $10.00
09 Tool 01/01/10 6
Appreciate your helps.