Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Queries _ How To Decrease A Number In A Table For Inventory

Posted by: lclayton1997 Aug 15 2019, 07:59 AM

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!


Posted by: RJD Aug 15 2019, 09:39 AM

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

HTH
Joe

Posted by: GroverParkGeorge Aug 15 2019, 09:57 AM

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?

Posted by: lclayton1997 Aug 15 2019, 10:49 AM

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.

Posted by: lclayton1997 Aug 15 2019, 10:51 AM

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.

Posted by: GroverParkGeorge Aug 15 2019, 12:03 PM

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.

Posted by: lclayton1997 Aug 15 2019, 12:29 PM

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?


Posted by: RJD Aug 15 2019, 02:23 PM

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

 DecreaseInventory.zip ( 22.86K ): 2