Help with writing code for "complex" problem

[dutchman]

Longtime lurker and first time poster. Thank you to all who help those like me use Access in better and smarter ways. Using this forum I have been able to build a somewhat robust database. Thank you.
Now the next step. I would like to get a report that will tell me how many "widgets" I will sell in the future. With further processing needed in order to sell them, I know I will sell a certain % each week at sometime in the future. For example, I receive 100 widgets today (week 1). I know that in Week 4 I will sell 10% of the original 100, in weeks 5, 6 and 7 I will sell 20% of the original 100 in each week. In week 8, I will sell the remaining 30%.

The complication arises because next week (week 2) I will receive 150 widgets that will sell 10% in Week 5 and so forth. It looks something like this:

Week1 Week 2 Week3 Week 4 Week 5 Week 6 Week 7 Week 8 week 9 week 10
100in 10 20 20 20 30
150in 15 30 30 30 45
50in 5 10 10 10 15


This is "easy" in Excel spreadsheet but that means manually transferring purchased widgets into the correct week etc.

[Dal Jeanis]

It's easy in Access, also, but to be of the most help, I need to know what you are doing with the projected values.

Here's how I would set it up. I would create three tables.

The first table is projection type. Currently, you have only one projection type, Normal (0/0/0/10/20/20/20/30).
The second table is the weekly projections. Here, you would have one projection per week that expects sales. In this case, there would be five projection records, one for each week that you expect to sell.

tblPType
     PTypeID     Autokey
    PTypeName   Text

tblProjSales
    PSaleID      Autokey
    PTypeID      FK to tblPType
    DateOffset   Number    (weeks or days after receipt)
    PSalesPct    Currency   (Currency type stores and multiplies even percentages best)

The third table -- lets call it tblSalesToBe -- would be the one where you insert your calculations. I can't suggest a layout there without having a little more info on how it would be used. For instance, if you are projecting sales on an ongoing basis, then you need to track back the stuff that came in six weeks ago and make sure you're not projecting future sales of more than you have left.

Maybe that means you'd want to have a PsoldNbr record on the tblSalesToBe table, indicating how many should have already been sold at that point. Also, that might suggest a design where you always have a few more records on the end of tblProjSales that say you'll sell 30% in weeks 9, 10 and 11, for example. When you recalculate sales and your past sales on a product have been way low, these would pick up the leftover product and project moving them out the door in those weeks.

[dutchman]

I appreciate your response.

I simply want a sum tally of my projected sales by week. My tally will be a % from purchases that arrived over five weeks. My purchases can vary by several thousand each week so therefore my sales will vary by thousands each week.

I also have TWO projection types Normal (0/0/0/10/20/20/20/30) and Extended (0/0/0/0/0/0/10/20/20/20/30)

Currently, I have a table tblPurchases. Each purchase is entered into this mass table (via a nice FORM, of course) individually. Each week could have purchases from several different vendors (could be 30 each week), each with differing numbers purchased. This table includes a mass of information not needed for the discussion here but I would need to "query" the actual date arrived and the number arrived, group the sum by week, and then project my sales out.