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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|