Summing Calculation Problem

[Bill Phillips]

Sorry if this has been answered before but I could not find an existing
solution.


I have a header record that contains the backorder qty of an item example 5000

Under that I have 3 purchase order records with a PO qty Lets use 3000, 3000
and 3000.

So the Info looks like this:

Item BO Qty 5000
PO#1 Qty 3000
PO#2 Qty 3000
PO#3 Qty 3000

I want to get a report that will tell me how much of the PO will be
fulfilling the BO

so the report would look like the following:

Item BO Qty 5000
PO#1 Qty 3000 Fulfill 3000
PO#2 Qty 3000 Fulfill 2000
PO#3 Qty 3000 Fulfill 0

Thanks,

Bill

 

[John Spencer]

Assumption the detail qty control is named txtCurrentQty and the BOQty
controls is named txtBOQty.

add a control to the detail section
Set it to running sum over group
Assign quantity as the source of the field
name it txtRunningQty

Add a second control to the detail section set its source to

= IIF(txtBOQty-txtRunningQty-txtCurrentQty>0, txtCurrentQty,
IIF(txtBOQty-txtRunningQty+txtCurrentQty>0,
txtBOQty-txtRunningQty+txtCurrentQty,0))

I think that will do what you want.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John Spencer]

Assumption the detail qty control is named txtCurrentQty and the BOQty
controls is named txtBOQty.

add a control to the detail section
Set it to running sum over group
Assign quantity as the source of the field
name it txtRunningQty

Add a second control to the detail section set its source to

= IIF(txtBOQty-txtRunningQty-txtCurrentQty>0, txtCurrentQty,
IIF(txtBOQty-txtRunningQty+txtCurrentQty>0,
txtBOQty-txtRunningQty+txtCurrentQty,0))

I think that will do what you want.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Bill Phillips]

Additional problems now. I have 2 groupings

Vendor
Item
Detail
Item: For the subtotal I created a text box in the detail called
txtInTransit. If the PO has not actually shipped I don't want to include it
in the calculations. I control that with the following: IIF( [NotShipped], 0,
txtCurrentQty) I sum this over the group.
In the Item footer I then created a text box txtItemOnOrder with the
source of txtInTransit. My item total calculation is then IIF(BOQty -
txtItemOnOrder >0, txtItemOnOrder, BOQty) This works fine.

PROBLEM:

Vendor Footer. Every time I try to calculate the totals per vendor I get one
of the following problems:

1. I get every record and it seriously overinflates the total.
2. I get only the total for the last item for each vendor.

I have tried to set a running sum in the vendor footer but it is not
calculating the way I need it to.

Any suggestions??

 

[Bill Phillips]

Additional problems now. I have 2 groupings

Vendor
Item
Detail
Item: For the subtotal I created a text box in the detail called
txtInTransit. If the PO has not actually shipped I don't want to include it
in the calculations. I control that with the following: IIF( [NotShipped], 0,
txtCurrentQty) I sum this over the group.
In the Item footer I then created a text box txtItemOnOrder with the
source of txtInTransit. My item total calculation is then IIF(BOQty -
txtItemOnOrder >0, txtItemOnOrder, BOQty) This works fine.

PROBLEM:

Vendor Footer. Every time I try to calculate the totals per vendor I get one
of the following problems:

1. I get every record and it seriously overinflates the total.
2. I get only the total for the last item for each vendor.

I have tried to set a running sum in the vendor footer but it is not
calculating the way I need it to.

Any suggestions??