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])

--
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)
>
>
>
>
>

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)
> >
> >
> >
> >
> >

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" <(E-Mail Removed)> wrote in message
news:3AAE3916-2B88-4B3B-901E-(E-Mail Removed)...
> 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)
>> >
>> >
>> >
>> >
>> >

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" <(E-Mail Removed)> wrote in message
> news:3AAE3916-2B88-4B3B-901E-(E-Mail Removed)...
> > 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)
> >> >
> >> >
> >> >
> >> >
> >> >

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" <(E-Mail Removed)> wrote in message
> > news:3AAE3916-2B88-4B3B-901E-(E-Mail Removed)...
> > > 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)
> > >> >
> > >> >
> > >> >
> > >> >
> > >> >

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" <(E-Mail Removed)> wrote in message
> > > news:3AAE3916-2B88-4B3B-901E-(E-Mail Removed)...
> > > > 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)
> > > >> >
> > > >> >
> > > >> >
> > > >> >
> > > >> >

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" <(E-Mail Removed)> wrote in message
> > > > news:3AAE3916-2B88-4B3B-901E-(E-Mail Removed)...
> > > > > 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)
> > > > >> >
> > > > >> >
> > > > >> >
> > > > >> >
> > > > >> >

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" <(E-Mail Removed)> wrote in message
> > > > > news:3AAE3916-2B88-4B3B-901E-(E-Mail Removed)...
> > > > > > 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)
> > > > > >> >
> > > > > >> >
> > > > > >> >
> > > > > >> >
> > > > > >> >