I'm currently developing a system which consist of desk order module and delivery order module. Desk order is about how many quantity of item customer order while delivery order is quantity of item delivered to the customer. one desk order might have one or more delivery order. eg, if customer order 20 pieces. it can be 2 delivery order, 10 each.
I have done all the insert part. The problem I face is, the syntax to total up all item of delivery order details to desk order details. Ok, fyi, I have 4 tables: desk_order (DK), desk_order_details (DKD), delivery_order (DO), delivery_order_details (DOD).
DK: DK_ID (Primary Key), DK_No
DKD: DKD_ID (Primary Key), DK_ID (Foreign Key), Qty_Ordered, Qty_Out
DO: DO_ID (Primary Key), DK_ID (Foreign Key), DO_No
DOD: DOD_ID (Primary Key), DO_ID (Foreign Key), DKD_ID (Foreign Key), Qty_Delivered
Eg
DKD
DKD_ID | DK_ID | Qty_Ordered | Qty_Out
1 | 001 | 4 | ???
2 | 001 | 20 | ???
DOD
DOD_ID | DKD_ID | Qty_Delivered
111 | 1 | 4
112 | 2 | 10
113 | 2 | 10
What I am doing is to find ???, which is Qty_Out. So, the code I have is
sql = "SELECT SUM(qty_delivered) as qty_delivered, qty_out FROM delivery_order_details as dod, delivery_order as do"
sql = sql & " WHERE dod.desk_order_id = do.desk_order_id"
sql = sql & " GROUP BY dod.desk_order_id "
rs2.Open sql, conn, 3, 3
if not rs2.eof then
do while not rs2.eof
rs2("qty_out") = rs2("qty_delivered")
rs2.update
rs2.movenext
loop
e nd if
But when I run it, got error saying:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
ODBC driver does not support the requested properties.
Just hope someone can help me to fix this. Thanks.
|