Solved Change record field using Form fields (1 Viewer)

HeavyD

New member
Local time
Today, 08:25
Joined
Nov 17, 2021
Messages
18
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
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:25
Joined
May 7, 2009
Messages
19,169
add Transaction type to your table, example

TransType=1 (initial inventory +)
TransType=2 (purchases +)
TransType=3 (sales -)
TransType=4 (return +)

on your query you can compute your current inventory:

select item_name, sum(switch(trans_type<3,[qty], trans_type=3,-[qty],[trans_type=4, [qty])) as [current balance] from yourtable group by item_name;

also you add Date field, therefore you will know when this transactions happens.
 

HeavyD

New member
Local time
Today, 08:25
Joined
Nov 17, 2021
Messages
18
Thank you June7 and arnelgp. I feel like you're both on the same page with your respective recommendations. This raises some fundamental questions about the database that I have. Thank you both for your insight.

Thanks for the welcome, theDBguy.
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:25
Joined
Jan 23, 2006
Messages
15,361
Welcome to AWF, HeavyD.
You may get some insight from this youtube video on StockManagement.
The Allen Browne reference that June offered is oft quoted and well deserved.
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:25
Joined
Sep 21, 2011
Messages
14,038
add Transaction type to your table, example

TransType=1 (initial inventory +)
TransType=2 (purchases +)
TransType=3 (sales -)
TransType=4 (return +)

on your query you can compute your current inventory:

select item_name, sum(switch(trans_type<3,[qty], trans_type=3,-[qty],[trans_type=4, [qty])) as [current balance] from yourtable group by item_name;

also you add Date field, therefore you will know when this transactions happens.
@arnelgp
Why would Returns be positive?
 

June7

AWF VIP
Local time
Today, 03:25
Joined
Mar 9, 2014
Messages
5,423
Because returns reverse a sale, puts items into inventory, just like a purchase.
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:25
Joined
Sep 21, 2011
Messages
14,038
Because returns reverse a sale, puts items into inventory, just like a purchase.
I was thinking of returns to manufacturer? as end of life was mentioned. Not sure a customer can return something they have been using for years, just because it is at end of Life?
 

HeavyD

New member
Local time
Today, 08:25
Joined
Nov 17, 2021
Messages
18
Alright folks,

I took your advice and created a table of Orders and a table of Returns. The current Inventory is realized by by comparing the Orders and Returns tables. Anything not common to both tables is my Inventory.

Each item in the Orders Table and Returns table have unique serial numbers which can be used to match items on both lists.

Is there a way to generate an Inventory table that is essentially Orders - Returns = Inventory?

High level logic in my head:
For each record of Orders.SerialNum,
Look for match in Returns.SerialNum.
If match found, do nothing.
Else copy matched record on Orders to Inventory

Any insight is appreciated.

P.S. - I'm tempted to cross-post or make this into a post of it's own. @Moderator, if needed I will do so.
 

HeavyD

New member
Local time
Today, 08:25
Joined
Nov 17, 2021
Messages
18
Hmmm, try Find Unmatched Query Wizard.
I got this...

+1 for HeavyD :)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:25
Joined
Feb 19, 2002
Messages
42,970
If everything is serialized, then I wouldn't use an orderDetails table. I would add OrderID to the inventory table and use that field to determine what is available and what has been sold.

For returns, I would use a separate table because you will need to keep history of returns when a single item is returned more than once. The returns table would contain the OrderID, ReturnDT, and return reason. If the item was returned for reasons other than defects that make it unsalable, I would update the inventory record to remove the orderID and that would put the item back into inventory.

When you have two updates that must both succeed or fail such as the insert of the return record and the update to return an item to inventory, you should enclose them in a transaction. That ensures that both succeed or neither succeeds so your data remains consistant.
 

June7

AWF VIP
Local time
Today, 03:25
Joined
Mar 9, 2014
Messages
5,423
Find Unmatched query won't provide correct inventory if item is returned and resold - assuming that is a possibility and that transaction history is maintained.

Pat's idea of OrderID field in Inventory table is one approach but even this could get out of sync with data - thus the recommendation to wrap in a Transaction method (I have used this only once). Another approach would be to calculate item's status when needed. If there is a return record dated later than most recent sale of item and item is salable then it is counted in inventory total, otherwise it is not available for sale and not tallied in inventory count.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:25
Joined
Feb 19, 2002
Messages
42,970
but even this could get out of sync with data -
Only if RI is not enforced or if you don't use a transaction to update both the inventory and return tables as a single action.
 

Users who are viewing this thread

Top Bottom