Question how to deduct quantities based on date - start from the lowest date

[illusionek]

Hello,

I have attached a sample of a database, which hopefully illustrates my problem well.

Table 1 has all the items I am trying to sell with sell by date after which I cannot sell this item. Then in Table 2 I have forecasted sales. So now I am trying to calculate stock consumption to see if I will be left with any stock that I cannot sell.

So now somehow I need to deduct sales forecast from my stock holding but it needs to go by date i.e. consume all stock for Item 1 with date 16/09 before moving to Item 1 with sale by date 23/09.

So based on the attached example, I can see that on 16/09 I will consume only 5 cases from sell by date 16/09 and another sale is 18/09. So that would give me information that I will be left with 95 items dated 16/09, which I cannot sell because they will be out of date.

Ideally I would like also to include the logic that if Item is out of date it would move to the next sell by date.

So in this case sale of Item 1 forecasted for 18/09 (94) would consume the whole stock (50) with date 23/09 and another 44 from date 01/10

For Item 2 I can see that units with Sell by date 30/09 will be consumed on 25/09 and I will start taking stock from next sell by date which is 14/10.

I hope all this makes sense and someone would be able to help me out

 

[MarkK]

I don't think your table design is refined enough to achieve that outcome. I recommend you do some reading on "database normalization" and "entity-relationship model" or "ER Model" before proceeding. You need a better understanding of how to model objects in a database, and how to relate them to each other using "key fields."

 

[plog]

I'm agnostic on your table design. It might be enough, it might not be. I need some more information about what you want.

You've given a good explanation, but exactly what do you want? Are you looking for a query? A function that you pass a date and item number and it returns a Yes/No if it can be fulfilled? What exactly do you want?


If its a query, tell me what the resulting data should be using the data in the sample you uploaded.

 

[illusionek]

@ MarkK

I cannot really redesign database if I do not know how to achieve desired outcome. At the moment I have two simple tables with three columns each. They look exactly like tables with raw data I have but as I said I cannot change tables if I do not know how they need to look like to get the result

@ plog

Ideally I would prefer a query rather than functions or macros but I am not sure if this can be achieved using query only. The overall aim of this project is to identify at what date I use up all stock with particar date or if I do not use how much stock will be left for each sell by date.



Can anyone help please?

 

[plog]

It sounds like a query is what you need then. Can you provide me what that query will display based on the data you previously provided? I'm not looking for an explanation, I want data.

 

[MarkK]

Raw data has a structure independent of any result or outcome. If rows in your raw data cannot be uniquely identified, for instance, or if you try to store data that should only be calculated, then you have flaws in the structure of your raw data. #GIGO

 

[illusionek]

Hi plog,

I have attached a spreadsheet that is currently filled manually. This is the end result I am trying to achieve. I used data from my sample database. I hope this illustrates what I am trying to achieve.

The idea is to link the query to Pivot Table in Excel, so I can manipulate data in any format that is required.


Thanks a lot for all your help!

 

[plog]

Thanks, that's exactly what I wanted. Still not sure its possible, but this will give me a target to aim for.

Busy the rest of the night, but will be able to work on this in the morning.

 

[plog]

Good news/bad news: It's possible with the data you have/it's going to require a complex module to determine it and it will be slow to generate the results you want (and I don't have the time to do it).

Because your sales table (Table2) doesn't directly link to your inventory table (Table1), you can't easily determine when a record of inventory is exhausted (either through sales or expiration). As you've shown in the sample, you can logically deduce it. Unfortunately, it's an iterative process--you can't know when the last record in inventory will be exhausted without first determining when every record before it will be.

If you want to tackle this, here's a general outline of what to do (open up Table1 & Table2 in your database, filter to Item1 to follow along) :

You would build a function that loops through every record in Table1 by product, in Sell by date order. You would then cacluate and write to a table when that will be exhausted along with any remainder that still need to be used for a sale on that date. That table would look like this after processing the first record in Table1:

tmpInventoryExhaustion
Product, SellByDate, ExhaustionDate, Remainder
Item1, 9/16/2014, 9/16/2014, 0

That means the Item1 set to expire on 9/16/2014 will expire and the Remainder is 0 because no sales in Table2 exist that were partially fulfilled by that inventory. So, it goes to the next record, using that information:

tmpInventoryExhaustion
Product, SellByDate, ExhaustionDate, Remainder
Item1, 9/16/2014, 9/16/2014, 0
Item1, 9/23/2014, 9/18/2014, 44

It first subtracts the previous remainder from its Qty (50 - 0) and then subtracts the next forecasted sale quantity after the previous records ExhaustionDate (9/16/2014, so it looks at the 9/18/2014 forecasted sale). Since it can't fulfill that entire sale, it exhausts on 9/18/2014 with a remainder of 44 that need to be fulfilled. It then moves to the next record using that information:

tmpInventoryExhaustion
Product, SellByDate, ExhaustionDate, Remainder
Item1, 9/16/2014, 9/16/2014, 0
Item1, 9/23/2014, 9/18/2014, 44
Item1, 10/1/2014, 10/1/2014, 0

It first subtracts the previous remainder from its Qty (100 - 44) and then subtracts the next forecasted sale quantity after the previous records ExhaustionDate (9/18/2014, so it looks at the 9/20/2014 forecasted sale). It fulfills that sale and isn't exhausted so it looks for more sales to fulfil--but none exist prior to its SellByDate so it Exhausts then without any remainder.

That's the outline of how that function shoudl work. Again, you would have to build that temporary table which would have to process every record in Table1, which in turn would need to do some looking into Table2, which adds up to a lot of processing time. After that, with tmpInventoryExhaustion you could generate the query you desire.