JoeC_Glenmark
Registered User.
- Local time
- Today, 15:08
- Joined
- May 8, 2013
- Messages
- 10
Guys I need some help with sumifs in access.
HEre are my tables:
TAble1 = Material and Quantity available
ItemCode Qty
A 124
B 45
Table2: Product and Priority
Prod Priority
Prod1 1
Prod2 3
Prod3 2
Prod4 4
Table3: This is the master table which should tell me if for the product I have material available, based on the priority of allocation
Prod ItemCode Rqt Priority Shortage
Prod1 A 100 1 24
Prod2 A 12 3 -13
Prod3 A 25 2 -1
Prod1 B 25 1 20
Prod4 B 23 4 -3
Formula for Prioritycolumn in excel is a standard vlookup - Looking up prod in priority table
Formula for Shortagecolumn is what is bothering me - Stock from table 1 (Qty Field) minus total requirement if the priority is higher giving the shortages column.(SUM (Shortage) if (Priority) higher than resp priority and (MtlCode) is same as the respective mtl code.
For eg: Row 2 = The value of -13 is 124-100(Priority 1) - 25 (Priority 2) leaving us with -13 after subtracting the Prod 2 demand.
Also this isnt regular sumif, since the sumif field uses the column being calculated also, creating a potential circular loop, but avoids it due to the code.
Hope it isnt confusing!
HEre are my tables:
TAble1 = Material and Quantity available
ItemCode Qty
A 124
B 45
Table2: Product and Priority
Prod Priority
Prod1 1
Prod2 3
Prod3 2
Prod4 4
Table3: This is the master table which should tell me if for the product I have material available, based on the priority of allocation
Prod ItemCode Rqt Priority Shortage
Prod1 A 100 1 24
Prod2 A 12 3 -13
Prod3 A 25 2 -1
Prod1 B 25 1 20
Prod4 B 23 4 -3
Formula for Prioritycolumn in excel is a standard vlookup - Looking up prod in priority table
Formula for Shortagecolumn is what is bothering me - Stock from table 1 (Qty Field) minus total requirement if the priority is higher giving the shortages column.(SUM (Shortage) if (Priority) higher than resp priority and (MtlCode) is same as the respective mtl code.
For eg: Row 2 = The value of -13 is 124-100(Priority 1) - 25 (Priority 2) leaving us with -13 after subtracting the Prod 2 demand.
Also this isnt regular sumif, since the sumif field uses the column being calculated also, creating a potential circular loop, but avoids it due to the code.
Hope it isnt confusing!
Last edited:
