****To be up front, I posted my question on UtterAccess yesterday, but have not received any replies. Hoping maybe someone here may be able to assist. https://www.utteraccess.com/forum/in...ic=2054251&hl= ****
Hello there. I've got an inventory database that I've designed from scratch (no easy undertaking for a noob with no prior Access experience). I've gotten most everything created and functioning properly, but have recently been tasked with adding the "Actual Quantity" to the db. Trying to figure out how to incorporate it into all my forms, tables, and queries.
I know there's a lot of info in this post. I tried to be thorough and provide sufficient info.
Here's the "situation" :
For inventory transactions there are two forms with subforms.
• Receive_mainform uses TransactionQuery as its record source and is filtered for "Add" transactions. Transaction types: 1 Receive and 3 Return to Stock (RTS).
• Issue_mainform uses TransactionQuery as its record source and is filtered for "Subtract" transactions. Transaction types: 2 Issue and 4 Return to Vendor.
The subforms both use TransactionDetailQuery as their record source.
I'm wondering if instead of having all 4 types of transactions together, should the received transactions (type 1) be in their own query, form or table to be able to achieve my desired result?
There is another form that shows all the transactions for each part and the total quantity on hand as well as a breakdown of qty avail per part tag - I'll have to incorporate the actuals into this as well.
The tables:
TransactionType
-TypeID - auto - PK
-Transaction - ST
-AddSubtract - ST
ChargeNumber
-ChargeID - auto - PK
-ChargeNumber - ST
-OrderType - ST
PartTag
-TagID - auto - PK
-TagNumber - ST
Part
-PartID - auto - PK
-PartNumber - ST
-Description - ST
-PartPic - ST
Transaction
-TransID - auto - PK
-TransDate - Date
-TypeID - Number - FK from TransactionType
-ChargeID - Number - FK from ChargeNumber
TransactionDetail
-DetailsID - auto - PK
-TransID - number - FK from Transaction
-Order - number
-TagID - number - FK from PartTag
-PartID - number - FK from Part
-Location - ST
-TransQty - Number
-TransQtyUOM - ST
-TransCount - Number
-TransCountUOM - ST
-TransNote - ST
Below is a combination of how the process currently works and with the functionality of the "actual" that I want to incorporate.
Step 1 – Receiving – Transaction Type 1
1. “Part123” is received.
2. Part Tag number “2250” is assigned. This is a unique number (mostly, a couple of older transactions are the exception) and a crucial part of the entire process.
3. The Quantity, Quantity UOM, Count, and Count UOM are manually input. For example:a. Quantity – 2
b. Quantity UOM – pack(s)
c. Count – 10
d. Count UOM - each
4. The Actual Quantity is calculated using a query expression “Quantity x Count = Actual Quantity”
5. Actual Quantity UOM will be the same as Count UOM…pulled as a DLookup, or whatever the best method is, from the Count UOM … 2 pack(s) x 10 each = 20 each
***Note) Each part can be received multiple times which means multiple part tags. Example:
• Part123o Tag 2250 - 2 pack(s) @ 10 each = 20 each
o Tag 3941 – 15 each @ 1 each = 15 each
o Tag 4141 – 4 pack(s) @ 10 each = 40 each
• Inventory will show the Actual Quantity of 75 each. But ideally will also break it down to show that we have 6 pack(s) of 10 each as well as the 15 each.
Step 2 – Issuing – Transaction Type 2
1. 13 each of Part123 is issued from the available Actual Quantity from part tag 2250.
2. This is manually input in the field “Actual Quantity” using a hidden textbox.
3. The Quantity UOM, Count, Count UOM, and Actual Quantity UOM, are each pulled from the received transaction for Tag 2250 using DLookup or whatever is the best method.
4. The Quantity is calculated using VBA and the hidden textbox method, “Actual Quantity / Count = Quantity” http://allenbrowne.com/TechniqueEnterCalcText.html
Qty : -1.3 - calculated
QtyUOM : packs - DLookup?
Count : 10 - DLookup?
CountUOM : each - DLookup?
ActualQty : -13 - manually entered in a hidden textbox on top of the query expression field
ActualQtyUOM : each - DLookup?
Step 3 – Return to Stock (RTS) – Transaction Type 3 (essentially the same as Step 2, except this is a positive transaction)
1. 3 each of Part123 is Returned to Stock to the same part tag it was issued from, 2250.
2. This is manually input in the field “Actual Quantity” using a hidden textbox.
3. The Quantity UOM, Count, Count UOM, and Actual Quantity UOM, are each pulled from the received transaction for Tag 2250 using DLookup or whatever is the best method.
4. The Quantity is calculated using VBA and the hidden textbox method, “Actual Quantity / Count = Quantity” http://allenbrowne.com/TechniqueEnterCalcText.html
Qty : 0.3 - calculated
QtyUOM : packs - DLookup?
Count : 10 - DLookup?
CountUOM : each - DLookup?
ActualQty : 3 - manually entered in a hidden textbox on top of the query expression field
ActualQtyUOM : each - DLookup?
Inventory would reflect
Part123 / TagNumber 2250
Receive 2 packs x 10 each = 20 each
Issue -1.3 packs x 10 each = -13 each
RTS 0.3 packs x 10 each = 3 each
On Hand 1 packs x 10 each = 10 each
If you've gotten all the way down here, I thank you so much for reading and for any assistance or guidance you may be able to provide =)
-Lisa