Isskint
Slowly Developing
- Local time
- Today, 20:55
- Joined
- Apr 25, 2012
- Messages
- 1,302
Hi everyone,
I am trying to 'fix' a Supply database, author unknown. I have created a query to provide a goods in diary, but the resulting recordset can not be updated.
I have provided a screen shot of the relevant relationships. There are 5 tables;
Purchase_Orders_List - Stores the PO ref and the Supplier
OrderBook_List - Stores details of the products ordered with a FK to Purchase_Orders_List
Shipping_List - Stores details of the shipping including container number, arrival date etc with a FK to Purchase_Orders_List.
GoodsIn_Diary - stores details of date and time delivery due with a FK to Purchase_Orders_List
Invoice_List - Stores details of various invoices against the PO with a FK to Purchase_Orders_List
Background to relationship.
For any single Purchase Order, there could be multiple order lines (OrderBook_List), multiple shipping/transport requirements (Shipping_List) and multiple invoices (Invoice_List). The GoodsIn_Diary table is separate as bookings can be postponed/amended so the history of multiple bookings for the same PO is important (costs can be incurred after set times, but if a booking has been postponed the time period is reset).
The query
So the query is based on GoodsIn_Diary table, adding other relevant data from Purchase_Orders_List and Shipping_List. SQL below may help;
I have even tried creating a sub query to return the details of the Purchase_Orders_List and Shipping_List but this still leaves me with a recordset can not be updated.
What am i missing?
I am trying to 'fix' a Supply database, author unknown. I have created a query to provide a goods in diary, but the resulting recordset can not be updated.
I have provided a screen shot of the relevant relationships. There are 5 tables;
Purchase_Orders_List - Stores the PO ref and the Supplier
OrderBook_List - Stores details of the products ordered with a FK to Purchase_Orders_List
Shipping_List - Stores details of the shipping including container number, arrival date etc with a FK to Purchase_Orders_List.
GoodsIn_Diary - stores details of date and time delivery due with a FK to Purchase_Orders_List
Invoice_List - Stores details of various invoices against the PO with a FK to Purchase_Orders_List
Background to relationship.
For any single Purchase Order, there could be multiple order lines (OrderBook_List), multiple shipping/transport requirements (Shipping_List) and multiple invoices (Invoice_List). The GoodsIn_Diary table is separate as bookings can be postponed/amended so the history of multiple bookings for the same PO is important (costs can be incurred after set times, but if a booking has been postponed the time period is reset).
The query
So the query is based on GoodsIn_Diary table, adding other relevant data from Purchase_Orders_List and Shipping_List. SQL below may help;
Code:
SELECT GoodsIn_Diary.giDate, GoodsIn_Diary.giTime, GoodsIn_Diary.giID, GoodsIn_Diary.poID, GoodsIn_Diary.giDay, GoodsIn_Diary.giRef, GoodsIn_Diary.giBW, GoodsIn_Diary.giBB, GoodsIn_Diary.giStatus, GoodsIn_Diary.giNote, GoodsIn_Diary.giLock, Purchase_Orders_List.suppID, Purchase_Orders_List.poWC, Shipping_List.shpCont, Shipping_List.shpArr, Purchase_Orders_List.poDesc, Purchase_Orders_List.poDest, Shipping_List.shpID
FROM (Purchase_Orders_List INNER JOIN GoodsIn_Diary ON Purchase_Orders_List.poID = GoodsIn_Diary.poID) INNER JOIN Shipping_List ON Purchase_Orders_List.poID = Shipping_List.poID
WHERE (((Purchase_Orders_List.suppID) Like IIf([Forms]![Purchase_Orders_List]![cmbSupp].[listindex]=-1,"*",[Forms]![Purchase_Orders_List]![cmbSupp])) AND ((Purchase_Orders_List.poWC) Like IIf([Forms]![Purchase_Orders_List]![cmbWC].[listindex]=-1,"*",[Forms]![Purchase_Orders_List]![cmbWC])))
ORDER BY GoodsIn_Diary.giDate, GoodsIn_Diary.giTime;
What am i missing?