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
> How To Decrease A Number In A Table For Inventory, Access 2016    
 
   
lclayton1997
post Aug 15 2019, 07:59 AM
Post#1



Posts: 166
Joined: 2-September 15



Racking my brain for the past couple days and need help.

My first table1 has these:

Prodid YYYYMMDD Qty
1234 20140402 5
1234 20160301 14
1234 20170804 3
1234 20181010 146

My second table2 has the usage:

ProdID QtyUsed
1234 12

So now I need to apply the 12 to the first in (FIFO) of 5 with remaining 7 then applied to the next 14 leaving a remainder of 0.
I have many products that it might need to loop through until the full QtyUsed goes to zero.

I'm sure its code but I don't even know where to start.

Thank you for your help!

Go to the top of the page
 
RJD
post Aug 15 2019, 09:39 AM
Post#2


UtterAccess VIP
Posts: 9,917
Joined: 25-October 10
From: Gulf South USA


Hi: Were you looking for a table update solution, or will a report alone meet the requirement?

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
GroverParkGeorge
post Aug 15 2019, 09:57 AM
Post#3


UA Admin
Posts: 35,514
Joined: 20-June 02
From: Newcastle, WA


In the usage table, are there any other fields? Is there one, for example, to record the DATE of the usage? If not, how would you know what order to use in the calculations?

BTW: I'm also assuming that the first table represents the incoming inventory by product and date acquired?
This post has been edited by GroverParkGeorge: Aug 15 2019, 09:58 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
lclayton1997
post Aug 15 2019, 10:49 AM
Post#4



Posts: 166
Joined: 2-September 15



both tables have an auto number so the records are unique.

The first table the YYYYMMDD is the date so I sort on these and the AutoID and put them in ascending order.
Go to the top of the page
 
lclayton1997
post Aug 15 2019, 10:51 AM
Post#5



Posts: 166
Joined: 2-September 15



I am updating the first table with the usage.
so there would be a remainder field that would then get applied to the next data until the remainder is zero.
Go to the top of the page
 
GroverParkGeorge
post Aug 15 2019, 12:03 PM
Post#6


UA Admin
Posts: 35,514
Joined: 20-June 02
From: Newcastle, WA


Okay, that makes sense except that I do NOT think it's wise to rely on a sort on AutoNumber. While it is highly likely that it will be sequenced as you want, that's not guranteed. One really needs a valid field, like a datetime field, on which reliable sorts can be made.

Also, you indicate that there are, indeed, additional fields needed beyond those called out in the initial question, "...there would be a remainder field ...".

So, we'll have more to work with if we can see the actual tables. Can you provide a sample of the accdb, with just enough sample data to illustrate the process?

Thanks.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
lclayton1997
post Aug 15 2019, 12:29 PM
Post#7



Posts: 166
Joined: 2-September 15



Original question did mention that there would be a "remainder". Sorry if I was not clear on that smile.gif
I added the autoID so I would have unique records thinking I may need that.

I have the two tables above and I would add the remainder field in the first along with a bal remaining field.
I imagine then there would be vba to loop through and do the math and update the table until the used qty is all used up in Table1.

That is why I am asking the question. How would this be done?

Go to the top of the page
 
RJD
post Aug 15 2019, 02:23 PM
Post#8


UtterAccess VIP
Posts: 9,917
Joined: 25-October 10
From: Gulf South USA


Hi: Perhaps you could consider an alternative to saving the balance in Table1. If you save the balance, this means you always have to make sure to run the update query. If you fail to do that, and do it correctly, then your results will not be correct. In addition, it is rarely wise to save the result of a calculation (there are a few exceptions, but this does not appear to be one of them).

Perhaps you would consider using a report to tell you the balances, with balances being calculated automatically when you run the report. By combining a query to get the current Usage total with Table1 in a new query and using that as the report record source, and by using the Running Sum feature along with an appropriate textbox calculation, you should be able to see the values you want.

See the demo attached, using your posted data. Take a look at the report, and how it is built up, and see what you think.

I don't think George's concern about the usage date is an issue (although you should store that anyway). Since an issue from inventory can only take place if there is sufficient inventory to meet the usage requirement, and the method is FIFO, the usage date resolves itself in the running sum approach.

HTH
Joe
Attached File(s)
Attached File  DecreaseInventory.zip ( 22.86K )Number of downloads: 1
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th August 2019 - 02:26 AM