A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Access 2003 Running Sum Question



 
 
Thread Tools Display Modes
  #1  
Old April 28th, 2010, 06:43 PM posted to microsoft.public.access.reports
tsquared1518
external usenet poster
 
Posts: 41
Default Access 2003 Running Sum Question

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)





  #2  
Old April 28th, 2010, 08:15 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Access 2003 Running Sum Question

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" wrote:

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)





  #3  
Old April 28th, 2010, 08:36 PM posted to microsoft.public.access.reports
tsquared1518
external usenet poster
 
Posts: 41
Default Access 2003 Running Sum Question

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/z...02-31-58PM.jpg



"Duane Hookom" wrote:

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" wrote:

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)





  #4  
Old April 29th, 2010, 02:30 AM posted to microsoft.public.access.reports
Duane Hookom[_4_]
external usenet poster
 
Posts: 316
Default Access 2003 Running Sum Question

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" wrote in message
...
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/z...02-31-58PM.jpg



"Duane Hookom" wrote:

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" wrote:

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)





  #5  
Old April 29th, 2010, 03:39 PM posted to microsoft.public.access.reports
tsquared1518
external usenet poster
 
Posts: 41
Default Access 2003 Running Sum Question

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" wrote:

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" wrote in message
...
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/z...02-31-58PM.jpg



"Duane Hookom" wrote:

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" wrote:

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)





  #6  
Old April 29th, 2010, 10:13 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Access 2003 Running Sum Question

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" wrote:

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" wrote:

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" wrote in message
...
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/z...02-31-58PM.jpg



"Duane Hookom" wrote:

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" wrote:

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)





  #7  
Old April 30th, 2010, 01:36 PM posted to microsoft.public.access.reports
tsquared1518
external usenet poster
 
Posts: 41
Default Access 2003 Running Sum Question

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" wrote:

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" wrote:

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" wrote:

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" wrote in message
...
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/z...02-31-58PM.jpg



"Duane Hookom" wrote:

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" wrote:

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)





  #8  
Old April 30th, 2010, 08:22 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Access 2003 Running Sum Question

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote in message
...
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/z...02-31-58PM.jpg



"Duane Hookom" wrote:

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" wrote:

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)





  #9  
Old May 4th, 2010, 05:51 PM posted to microsoft.public.access.reports
tsquared1518
external usenet poster
 
Posts: 41
Default Access 2003 Running Sum Question

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote in message
...
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/z...02-31-58PM.jpg



"Duane Hookom" wrote:

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" wrote:

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)





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:51 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.