1. 

   ajdjackson, Member

   Hi All

   I need advice on how I go about creating a report that on the fgace of it seems simple but i have no idea on how to create it.

   I have a simple Stock table that has the following definition:

   Stock_ID
   StockQty
   InStock_Date
   UsageRate
   SellPrice

   What I'm looking to do is to look at each Stock Item and by using its Usage Rate slot a value into future time periods.

   For example if ProductA is currently in stock, has a stock qty of 55 and a usage rate of 10 per week then I would need to populate the report with the next 10 Weeks with a quantity of 10. ProductB may have an in-stock quantity of 30 and a usage rate of 7/week so I need to populate the next row of table with the next 4 weeks with value of 7 and Week 5 with the remainder of the stock, 2. And so on with the remaining products.

   Period
   Product 1 2 3 4 5 6 .........
   A 10 10 10 10 10 5
   B 7 7 7 7 2
   C 0 0 5 5 5 5

   A further requirement is handling Products that come into in the future for example 3 weeks from now. I would like to perform the above but the starting Week will be Week 3 - Product C above.

   Another thing I would like to do is to perform calculations on the above such as multiplying each weeks quantities by the products' selling prices to get projected income by week.

   I have no idea on how I would go about doing this.

   Can you help?

   Thanks

   Jake
2. 

   myDBR Team, Key Master

   Jake,
   We cannot write your reports for you, but we can point you to right direction. As you do not have the data you wish to show you need to create it first.

   The basic report is a crosstab report which has a row for each product that has data for that week.

   To create the data, make a temporary table into which you loop the data from the Stock table and from the previous weeks for as many weeks as you need. Selling prices would be calculated same way, the income by week can be calculated by myDBR.

   --
   myDBR Team
3. 

   ajdjackson, Member

   Hi

   Thanks for your reply.

   I'd thought about tmp tables but was just wondering if there was a more efficient way.

   It would be great if you could give me a couple of lines of code showing me how to loop data into a tmp table if it's not too much bother.

   Thanks

   Jake

Reply

You must log in to post.