Hello All,
Please forgive me if this is to easy for me to figure out. I have an Access database that is being used as a report tool for and AS400 db. What I want to do is to take the values I have queried and perform some calculations on them. Here is an example!

Column Names
Resource#(not unique, and Item number)|Description(of item)|QtyRequired(of item)|QtyonHand(of item)|Date(of when item is needed).

This is a query that shows:
123|Widgets|100|45|1/1/2006
123|Widgets|100|150|2/1/2006

This is a sum query that sums the fields QtyRequired and QtyonHand and groups them by Resoure#, Description, and Date.
Simple sofar.

Now I want to report on this query.
The report has two group headers 1)Resource# and 2)Date
Resource# can be used for multple dates and each date has a different amount required but the whole resource only has a set qty of items on hand.
So from the example above the report on each detail section would have a running decrease in the QtyonHand value i.e. record 1 would show:
Resource#
123
Date:1/1/2006
       QTYonHAND QTYRequired QTY Left over or need to order
          100 45 55
Date:2/1/2006
       QTYonHAND QTYRequired QTY Left over or need to order
          55 150 -95
This seems simple but I can not wrap my mind around this.
It also needs to reset with each new resource number. I know that this should be easy but I am very puzzled. Any help would be great.

Thanks in advance.
Wes

Wes E.

hi WES ,
  If u required this in the report u can directlly built an expression using an list or text box. or if u want to use in the from or store in the table then u have to make with the use of Query Criteria build expression
 hope this idea will help u to get into the concept

 bye..

Yes, I would like to perform this in the report because at this time I do not see a need to save the value it should always update at run time. But what I am having trouble with is writing the Access formula that would increment or decrement based on a the data sources and carry that value over to a textbox and make that textbox change on each date grouping and on each resource grouping.

Wes E.

OK i got ur idea,

  and don't mistake me i want to know why u require the QOH left and how can u get the Quantity on Hand and closing balance if ur using this DB for any Inventory purpose bcs if u want the subraction in the report i won't store in the backend and the stock won't get decrease. And would u able to clear me what type of DB ur having and for wat purpose ur using . Because wrong idea leads to wrong report that y iam not able to give the solution..
 bye
 P.Anukprasanna
 Stores Assistant.
 Indian Institute of Brackishwater Aquaculture

Ok, What I am doing is taking an AS400 db and trying to make some inventory reports that are not available in the db. Since this is only a report there is no reason to store the decrease. It only needs to be calculated at run time. Example of what the report is to look like:
Resource # 1234
Date: 3/31/06 Qty on Hand Qty Required Qty Still Needed
            140 30 110
Date: 4/3/06
            110 161 -51
Date: 4/7/06
            -51 80 -131

What I am doing is taking information from a production schedule table and information from an inventory table. On the inventory table there are items that are frozen and thawed. What I want to be able to do is to take the thawed items lets say I need fruit 3 times this week. I cannot use it frozen. I have 140 lbs of fruit thawed and 500 lbs frozen. On the first usage I only need 30 lbs, second usage I need 161 lbs, and third I need 80 lbs. Also it takes 2 days to thaw. So for the first usage I have enough fruit for what I am making, but on the second and third usage I do not. For the 2nd I need 51 lbs and the 3rd I need 80 lbs. I just want to see a report on the item being consumed in production and how much is thawed for that day of production. This will tell an inventory clerk that for the 2nd and 3rd run to thaw more fruit and will let them know how much fruit is needed for each production day.


Wes E.

Ok it sounds me,

   So ur taking data form differents table to generate the report . If it pls try the method given below :
  (Note : If ur using the QTY Still as Text box in the Report Only)

  Assume , Table 1 : Production Schedule
   So ur taking one data field for that

 Table2: Inventory Table
 Ur taking some table from that

 Add this QTy Still any of the table and creat the Query using the table with the desire data field..
with this Qty Still and creat an report using the Query Created. and if u generate the report every field as an value but not in QTY Still .Then go to the desing view and in the QTY Still properties , control source ,in build expression give this command

   " =QTY on hand - Qty required"

 This is the assamption not the correct data pls put ur correct field name .

 may this ill work and fill ur need if not give me an reply.

Bye
p.Anukprasanna
Stores Assistant.
Indian Institute of Brackishwater Aquaculture

Thanks for the help but it will not work because it does not decrement correctly I will give an example again of how it is to decrement.

Item #1234
 Produced on 4/1/06 Needs 30 widgets I have 140 widgets available. So on this date I am fine I do not need any more widgets I have an excess of 110 widgets. I hope you are with me.
Item #1234
 Produced on 4/3/06 Needs 161 widgets I have 110 widgets available. So I now need 51 more widgets that I will have to order, otherwise I will be short.
Item #1234
 Produced on 4/6/06 Needs 80 widgets. I currently am -51 widgets short. So at this time it shows me that I am currently in need of 131 widgets.

This report will show an item being manufactured, what sub items go into manufacturing it, the day it will be manufactured. It will also show the sub items how many of each sub item is needed, how much is currently in my warehouse, and how much more I need to order by the date I will produce the item.

When you do as you say the result is for every day no matter when I run the production I have 140 widgets on hand. That never decreases as it is used. It just assumes that what was used yesterday was put back today.

Wes E.

OK sorry for my mistake first ,
 pls give ur mail id i ill sent an DB which will help u as an example . if u fell privacy then my mail id is [email protected]

P.A.P.Raguv

[email protected]

Wes E.