UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dlookup As Control Source Or Default Value?, Access 2016    
 
   
lisarisa333
post Jun 26 2019, 09:04 AM
Post#1



Posts: 6
Joined: 11-February 19



Hello there. I've got an inventory database that I've designed from scratch (no easy undertaking for a noob dazed.gif ). 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'd typed up my post in Word and included some screenshots, textboxes, arrows and whatnot to make it a bit easier to visualize....but I cannot add them here frown.gif

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. This form is for transaction types: 1 Receive and 3 Return to Stock.
• Issue_mainform uses TransactionQuery as its record source and is filtered for "Subtract" transactions. This form is for 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:
• Part123
o 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

Go to the top of the page
 
theDBguy
post Jun 26 2019, 10:50 AM
Post#2


Access Wiki and Forums Moderator
Posts: 75,999
Joined: 19-June 07
From: SunnySandyEggo


Hi Lisa. How about attaching the Word doc to your post? If you're not allowed to yet, you can maybe email it to me, and I'll do it for you.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
lisarisa333
post Jun 26 2019, 11:13 AM
Post#3



Posts: 6
Joined: 11-February 19



Thanks DBGuy. I've attached it Attached File  forum_question.zip ( 153.67K )Number of downloads: 7
. I have since edited some of the wording, so it's different than my OP, but the visuals are still there.
Go to the top of the page
 
lisarisa333
post Jun 27 2019, 09:18 AM
Post#4



Posts: 6
Joined: 11-February 19



Trying to get my question out there and seen and hopefully get some advice or guidance. I cross posted onto accessforums.net this morning in case there are folks on that forum that might not be on this one.

https://www.accessforums.net/showthread.php?t=77404

Go to the top of the page
 
theDBguy
post Jun 29 2019, 12:12 PM
Post#5


Access Wiki and Forums Moderator
Posts: 75,999
Joined: 19-June 07
From: SunnySandyEggo


Hi Lisa. Looks like you got your answer already. Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th August 2019 - 12:55 PM