jimmypickins
New member
- Local time
- Today, 15:12
- Joined
- Oct 22, 2015
- Messages
- 3
Good day,
I am trying to build a database to handle a small inventory shipping and receiving. I thought I had everything working, but noticed right at the end that the append query was made incorrectly. This query is only handling what was actually shipped/received off the order as a whole. The problem is you can have multiple shipments per order.
More specifically I'll use an example:
[Order] 999 is created for [Quantity] 50 of [Item] 111. We only have 20 in stock, so [QtyShipped] is 20. The append query would then create a [Shipment] 1 for [QtyShipped] of 20 for [Item] 111 on [Order] 999. I use VBA on the form to set [QtyRemaining] to 50-20 to get 30 (which we need to track backorders).
The remaining 30 show up 3 days later, so I run through the same steps and the append query adds [QtyShipped] of 30 for [Item] 111 on [Order] 999. The VBA side keeps track and sets backorder to 0.
Because each line of the order can have multiple shipments, there doesn't seem to be a good unique key to use. I could probably use [Order]+[Date()] but if the very rare case that the backordered items show up in the afternoon it would prevent the user from being able to send the second shipment.
Is there a way to use the append table here? Any suggestions are greatly appreciated.
Thanks in advance,
Jimmy.
I am trying to build a database to handle a small inventory shipping and receiving. I thought I had everything working, but noticed right at the end that the append query was made incorrectly. This query is only handling what was actually shipped/received off the order as a whole. The problem is you can have multiple shipments per order.
More specifically I'll use an example:
[Order] 999 is created for [Quantity] 50 of [Item] 111. We only have 20 in stock, so [QtyShipped] is 20. The append query would then create a [Shipment] 1 for [QtyShipped] of 20 for [Item] 111 on [Order] 999. I use VBA on the form to set [QtyRemaining] to 50-20 to get 30 (which we need to track backorders).
The remaining 30 show up 3 days later, so I run through the same steps and the append query adds [QtyShipped] of 30 for [Item] 111 on [Order] 999. The VBA side keeps track and sets backorder to 0.
Because each line of the order can have multiple shipments, there doesn't seem to be a good unique key to use. I could probably use [Order]+[Date()] but if the very rare case that the backordered items show up in the afternoon it would prevent the user from being able to send the second shipment.
Is there a way to use the append table here? Any suggestions are greatly appreciated.
Thanks in advance,
Jimmy.