Access 2003 Running Sum Question

  • Thread starter Thread starter tsquared1518
  • Start date Start date
T

tsquared1518

I have a report and sub-report that have running sums, one calculating
Material In, the other calculating Material Out. I added a text box to
calculate the inventory on hand (=Material In - Material Out). It works, but
it pulls the first running sum calculation, not the last. It looks somewhat
like below. Is there a way to get the Mat'l. Out Running sum to pull the
latest amount by date or something? Any help is greatly appreciated.
THANKS!!!!

Material In
4/4/10 250 ft Running Sum Mat'l. In
250 ft
Material Out Running Sum Mat'l. Out
4/7/10 125 ft 125 ft
4/10/10 100 ft 225 ft


Amount in Stock

125 ft (Should be 25 ft)
 
You shouldn't need to use the running sum values in this calculation. I would
think you could use something like:
=Sum(Abs([InOutField]="Material In") * [Qtyfield]) -
Sum(Abs([InOutField]="Material Out") * [Qtyfield])
 
I'm not sure I follow your suggestion. Maybe a pic of the actual report will
help explain my issue. I hope this works.

http://i814.photobucket.com/albums/zz64/tsquared1518/4-28-20102-31-58PM.jpg



Duane Hookom said:
You shouldn't need to use the running sum values in this calculation. I would
think you could use something like:
=Sum(Abs([InOutField]="Material In") * [Qtyfield]) -
Sum(Abs([InOutField]="Material Out") * [Qtyfield])

--
Duane Hookom
Microsoft Access MVP


tsquared1518 said:
I have a report and sub-report that have running sums, one calculating
Material In, the other calculating Material Out. I added a text box to
calculate the inventory on hand (=Material In - Material Out). It works, but
it pulls the first running sum calculation, not the last. It looks somewhat
like below. Is there a way to get the Mat'l. Out Running sum to pull the
latest amount by date or something? Any help is greatly appreciated.
THANKS!!!!

Material In
4/4/10 250 ft Running Sum Mat'l. In
250 ft
Material Out Running Sum Mat'l. Out
4/7/10 125 ft 125 ft
4/10/10 100 ft 225 ft


Amount in Stock

125 ft (Should be 25 ft)
Click to expand...
Click to expand...
 
That looks nothing like your first post. How about using words in here to
explain what we would see if we could see your monitor?

--
Duane Hookom
MS Access MVP


tsquared1518 said:
I'm not sure I follow your suggestion. Maybe a pic of the actual report
will
help explain my issue. I hope this works.

http://i814.photobucket.com/albums/zz64/tsquared1518/4-28-20102-31-58PM.jpg



Duane Hookom said:
You shouldn't need to use the running sum values in this calculation. I
would
think you could use something like:
=Sum(Abs([InOutField]="Material In") * [Qtyfield]) -
Sum(Abs([InOutField]="Material Out") * [Qtyfield])

--
Duane Hookom
Microsoft Access MVP


tsquared1518 said:
I have a report and sub-report that have running sums, one calculating
Material In, the other calculating Material Out. I added a text box to
calculate the inventory on hand (=Material In - Material Out). It
works, but
it pulls the first running sum calculation, not the last. It looks
somewhat
like below. Is there a way to get the Mat'l. Out Running sum to pull
the
latest amount by date or something? Any help is greatly appreciated.
THANKS!!!!

Material In
4/4/10 250 ft Running Sum Mat'l. In
250 ft
Material Out Running Sum Mat'l. Out
4/7/10 125 ft 125 ft
4/10/10 100 ft 225 ft


Amount in Stock

125 ft (Should be 25 ft)
Click to expand...
Click to expand...
Click to expand...
 
Sorry, I was trying to keep it as simple as I could in the first post. The
pic shows what my screen (report) looks like. Basically, I want to subtract
the last value on the bottom right of the subreport, which is the running sum
of material out, from the value for Running Sum of Material IN (only 1 value
on page in pic) to get the correct value for the Amount in Stock. As you can
see from the pic, its currently pulling the first value (top right in
subreport, 3). So the Amount in Stock should be -106.833 (1350-1456.833).
Anyone know how to accomplish this?



Duane Hookom said:
That looks nothing like your first post. How about using words in here to
explain what we would see if we could see your monitor?

--
Duane Hookom
MS Access MVP


tsquared1518 said:
I'm not sure I follow your suggestion. Maybe a pic of the actual report
will
help explain my issue. I hope this works.

http://i814.photobucket.com/albums/zz64/tsquared1518/4-28-20102-31-58PM.jpg



Duane Hookom said:
You shouldn't need to use the running sum values in this calculation. I
would
think you could use something like:
=Sum(Abs([InOutField]="Material In") * [Qtyfield]) -
Sum(Abs([InOutField]="Material Out") * [Qtyfield])

--
Duane Hookom
Microsoft Access MVP


:

I have a report and sub-report that have running sums, one calculating
Material In, the other calculating Material Out. I added a text box to
calculate the inventory on hand (=Material In - Material Out). It
works, but
it pulls the first running sum calculation, not the last. It looks
somewhat
like below. Is there a way to get the Mat'l. Out Running sum to pull
the
latest amount by date or something? Any help is greatly appreciated.
THANKS!!!!

Material In
4/4/10 250 ft Running Sum Mat'l. In
250 ft
Material Out Running Sum Mat'l. Out
4/7/10 125 ft 125 ft
4/10/10 100 ft 225 ft


Amount in Stock

125 ft (Should be 25 ft)
Click to expand...
Click to expand...
Click to expand...
 
So, there is a subreport involved in this also?
Can you just tell us about your data and how you determine In and Out? Is
this stored in the table?

I really don't think you should have to use a running sum to get the Net
value.

--
Duane Hookom
Microsoft Access MVP


tsquared1518 said:
Sorry, I was trying to keep it as simple as I could in the first post. The
pic shows what my screen (report) looks like. Basically, I want to subtract
the last value on the bottom right of the subreport, which is the running sum
of material out, from the value for Running Sum of Material IN (only 1 value
on page in pic) to get the correct value for the Amount in Stock. As you can
see from the pic, its currently pulling the first value (top right in
subreport, 3). So the Amount in Stock should be -106.833 (1350-1456.833).
Anyone know how to accomplish this?



Duane Hookom said:
That looks nothing like your first post. How about using words in here to
explain what we would see if we could see your monitor?

--
Duane Hookom
MS Access MVP


tsquared1518 said:
I'm not sure I follow your suggestion. Maybe a pic of the actual report
will
help explain my issue. I hope this works.

http://i814.photobucket.com/albums/zz64/tsquared1518/4-28-20102-31-58PM.jpg



:

You shouldn't need to use the running sum values in this calculation. I
would
think you could use something like:
=Sum(Abs([InOutField]="Material In") * [Qtyfield]) -
Sum(Abs([InOutField]="Material Out") * [Qtyfield])

--
Duane Hookom
Microsoft Access MVP


:

I have a report and sub-report that have running sums, one calculating
Material In, the other calculating Material Out. I added a text box to
calculate the inventory on hand (=Material In - Material Out). It
works, but
it pulls the first running sum calculation, not the last. It looks
somewhat
like below. Is there a way to get the Mat'l. Out Running sum to pull
the
latest amount by date or something? Any help is greatly appreciated.
THANKS!!!!

Material In
4/4/10 250 ft Running Sum Mat'l. In
250 ft
Material Out Running Sum Mat'l. Out
4/7/10 125 ft 125 ft
4/10/10 100 ft 225 ft


Amount in Stock

125 ft (Should be 25 ft)
Click to expand...
Click to expand...
Click to expand...
 
That is correct, a subreport is involved. The data is stored in 2 separate
tables. The "Material In" is the material we order from our supplier to make
our products. It is checked upon arrival, and results are stored in a table.
The second table is Work Orders. This is the "Material Out", which is what's
shown in the subreport. For each order, we record details of the product
being made, and keep track of the total amount of material used on each
order.

Duane Hookom said:
So, there is a subreport involved in this also?
Can you just tell us about your data and how you determine In and Out? Is
this stored in the table?

I really don't think you should have to use a running sum to get the Net
value.

--
Duane Hookom
Microsoft Access MVP


tsquared1518 said:
Sorry, I was trying to keep it as simple as I could in the first post. The
pic shows what my screen (report) looks like. Basically, I want to subtract
the last value on the bottom right of the subreport, which is the running sum
of material out, from the value for Running Sum of Material IN (only 1 value
on page in pic) to get the correct value for the Amount in Stock. As you can
see from the pic, its currently pulling the first value (top right in
subreport, 3). So the Amount in Stock should be -106.833 (1350-1456.833).
Anyone know how to accomplish this?



Duane Hookom said:
That looks nothing like your first post. How about using words in here to
explain what we would see if we could see your monitor?

--
Duane Hookom
MS Access MVP


I'm not sure I follow your suggestion. Maybe a pic of the actual report
will
help explain my issue. I hope this works.

http://i814.photobucket.com/albums/zz64/tsquared1518/4-28-20102-31-58PM.jpg



:

You shouldn't need to use the running sum values in this calculation. I
would
think you could use something like:
=Sum(Abs([InOutField]="Material In") * [Qtyfield]) -
Sum(Abs([InOutField]="Material Out") * [Qtyfield])

--
Duane Hookom
Microsoft Access MVP


:

I have a report and sub-report that have running sums, one calculating
Material In, the other calculating Material Out. I added a text box to
calculate the inventory on hand (=Material In - Material Out). It
works, but
it pulls the first running sum calculation, not the last. It looks
somewhat
like below. Is there a way to get the Mat'l. Out Running sum to pull
the
latest amount by date or something? Any help is greatly appreciated.
THANKS!!!!

Material In
4/4/10 250 ft Running Sum Mat'l. In
250 ft
Material Out Running Sum Mat'l. Out
4/7/10 125 ft 125 ft
4/10/10 100 ft 225 ft


Amount in Stock

125 ft (Should be 25 ft)
Click to expand...
Click to expand...
Click to expand...
 
I would probably calculate this outside of the report. You create totals
query to create the sums of Out and In and then join them to your report's
record source so you don't have to rely on the running sums.

--
Duane Hookom
Microsoft Access MVP


tsquared1518 said:
That is correct, a subreport is involved. The data is stored in 2 separate
tables. The "Material In" is the material we order from our supplier to make
our products. It is checked upon arrival, and results are stored in a table.
The second table is Work Orders. This is the "Material Out", which is what's
shown in the subreport. For each order, we record details of the product
being made, and keep track of the total amount of material used on each
order.

Duane Hookom said:
So, there is a subreport involved in this also?
Can you just tell us about your data and how you determine In and Out? Is
this stored in the table?

I really don't think you should have to use a running sum to get the Net
value.

--
Duane Hookom
Microsoft Access MVP


tsquared1518 said:
Sorry, I was trying to keep it as simple as I could in the first post. The
pic shows what my screen (report) looks like. Basically, I want to subtract
the last value on the bottom right of the subreport, which is the running sum
of material out, from the value for Running Sum of Material IN (only 1 value
on page in pic) to get the correct value for the Amount in Stock. As you can
see from the pic, its currently pulling the first value (top right in
subreport, 3). So the Amount in Stock should be -106.833 (1350-1456.833).
Anyone know how to accomplish this?



:

That looks nothing like your first post. How about using words in here to
explain what we would see if we could see your monitor?

--
Duane Hookom
MS Access MVP


I'm not sure I follow your suggestion. Maybe a pic of the actual report
will
help explain my issue. I hope this works.

http://i814.photobucket.com/albums/zz64/tsquared1518/4-28-20102-31-58PM.jpg



:

You shouldn't need to use the running sum values in this calculation. I
would
think you could use something like:
=Sum(Abs([InOutField]="Material In") * [Qtyfield]) -
Sum(Abs([InOutField]="Material Out") * [Qtyfield])

--
Duane Hookom
Microsoft Access MVP


:

I have a report and sub-report that have running sums, one calculating
Material In, the other calculating Material Out. I added a text box to
calculate the inventory on hand (=Material In - Material Out). It
works, but
it pulls the first running sum calculation, not the last. It looks
somewhat
like below. Is there a way to get the Mat'l. Out Running sum to pull
the
latest amount by date or something? Any help is greatly appreciated.
THANKS!!!!

Material In
4/4/10 250 ft Running Sum Mat'l. In
250 ft
Material Out Running Sum Mat'l. Out
4/7/10 125 ft 125 ft
4/10/10 100 ft 225 ft


Amount in Stock

125 ft (Should be 25 ft)
Click to expand...
Click to expand...
Click to expand...
 
I finally figured it out!! I bought a copy of Microsoft Office Access 2003
Inside Out, and it had an example of how to do the calculation. I figure I
already got my money's worth out of it, ha...Thanks for all your help!

Duane Hookom said:
I would probably calculate this outside of the report. You create totals
query to create the sums of Out and In and then join them to your report's
record source so you don't have to rely on the running sums.

--
Duane Hookom
Microsoft Access MVP


tsquared1518 said:
That is correct, a subreport is involved. The data is stored in 2 separate
tables. The "Material In" is the material we order from our supplier to make
our products. It is checked upon arrival, and results are stored in a table.
The second table is Work Orders. This is the "Material Out", which is what's
shown in the subreport. For each order, we record details of the product
being made, and keep track of the total amount of material used on each
order.

Duane Hookom said:
So, there is a subreport involved in this also?
Can you just tell us about your data and how you determine In and Out? Is
this stored in the table?

I really don't think you should have to use a running sum to get the Net
value.

--
Duane Hookom
Microsoft Access MVP


:

Sorry, I was trying to keep it as simple as I could in the first post. The
pic shows what my screen (report) looks like. Basically, I want to subtract
the last value on the bottom right of the subreport, which is the running sum
of material out, from the value for Running Sum of Material IN (only 1 value
on page in pic) to get the correct value for the Amount in Stock. As you can
see from the pic, its currently pulling the first value (top right in
subreport, 3). So the Amount in Stock should be -106.833 (1350-1456.833).
Anyone know how to accomplish this?



:

That looks nothing like your first post. How about using words in here to
explain what we would see if we could see your monitor?

--
Duane Hookom
MS Access MVP


I'm not sure I follow your suggestion. Maybe a pic of the actual report
will
help explain my issue. I hope this works.

http://i814.photobucket.com/albums/zz64/tsquared1518/4-28-20102-31-58PM.jpg



:

You shouldn't need to use the running sum values in this calculation. I
would
think you could use something like:
=Sum(Abs([InOutField]="Material In") * [Qtyfield]) -
Sum(Abs([InOutField]="Material Out") * [Qtyfield])

--
Duane Hookom
Microsoft Access MVP


:

I have a report and sub-report that have running sums, one calculating
Material In, the other calculating Material Out. I added a text box to
calculate the inventory on hand (=Material In - Material Out). It
works, but
it pulls the first running sum calculation, not the last. It looks
somewhat
like below. Is there a way to get the Mat'l. Out Running sum to pull
the
latest amount by date or something? Any help is greatly appreciated.
THANKS!!!!

Material In
4/4/10 250 ft Running Sum Mat'l. In
250 ft
Material Out Running Sum Mat'l. Out
4/7/10 125 ft 125 ft
4/10/10 100 ft 225 ft


Amount in Stock

125 ft (Should be 25 ft)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top