Good day lovely people,
Still getting familiar with the various Access Functions. Status: Intermediate Noob.
I've built an inventory table that looks like below. The Order# is always maintained with the database record.
Inventory Table:
Product | QTY | Order#
A ------- 4 ------- 1111
A ------- 2 ------- 2222
B------- 2 ------- 1111
C------- 3 ------- 2222
Inventory table records are added by Stock Order Forms that look like so:
Stock Order#: 1111 (Form - Add New Record)
Product: A QTY:4
Product: B QTY:2
Stock Order#: 2222 (Form - Add New Record)
Product: A QTY:2
Product: C QTY:3
When Products reach their end of life, they are returned and the Inventory DB needs to be updated. The Return Forms look like so:
Return Order (Form - Modify Matching Record)
QTY:2 Product: A Order#: 1111
QTY:1 Product: C Order#: 2222
The Problem/Question: I am looking for a way to process each line of a Return Form using the following logic:
- Search [Inventory Table] field [Product] = "Return Form [Product]" AND [Inventory Table] field [Order#] = "Return Form [Order#]"
- On match, decrement matching [Inventory Table} field [QTY] by "Return Form [QTY]"
I want to use fields on the Return Form as search criteria and decrement values.
The resulting Inventory Table would look like so:
Inventory Table:
Product | QTY | Order#
A ------- 2 ------- 1111 <---- Decremented by 2
A ------- 2 ------- 2222
B------- 2 ------- 1111
C------- 2 ------- 2222 <---- Decremented by 1
I'll worry about error handling later (no match, QTY=0)
Any insight is greatly appreciated. If there is any clarification required, please let me know. Thanks in advance.
HeavyD
Still getting familiar with the various Access Functions. Status: Intermediate Noob.
I've built an inventory table that looks like below. The Order# is always maintained with the database record.
Inventory Table:
Product | QTY | Order#
A ------- 4 ------- 1111
A ------- 2 ------- 2222
B------- 2 ------- 1111
C------- 3 ------- 2222
Inventory table records are added by Stock Order Forms that look like so:
Stock Order#: 1111 (Form - Add New Record)
Product: A QTY:4
Product: B QTY:2
Stock Order#: 2222 (Form - Add New Record)
Product: A QTY:2
Product: C QTY:3
When Products reach their end of life, they are returned and the Inventory DB needs to be updated. The Return Forms look like so:
Return Order (Form - Modify Matching Record)
QTY:2 Product: A Order#: 1111
QTY:1 Product: C Order#: 2222
The Problem/Question: I am looking for a way to process each line of a Return Form using the following logic:
- Search [Inventory Table] field [Product] = "Return Form [Product]" AND [Inventory Table] field [Order#] = "Return Form [Order#]"
- On match, decrement matching [Inventory Table} field [QTY] by "Return Form [QTY]"
I want to use fields on the Return Form as search criteria and decrement values.
The resulting Inventory Table would look like so:
Inventory Table:
Product | QTY | Order#
A ------- 2 ------- 1111 <---- Decremented by 2
A ------- 2 ------- 2222
B------- 2 ------- 1111
C------- 2 ------- 2222 <---- Decremented by 1
I'll worry about error handling later (no match, QTY=0)
Any insight is greatly appreciated. If there is any clarification required, please let me know. Thanks in advance.
HeavyD
Last edited: