running totals

[Melinda]

I am fairly new to access, so I am hoping this question is clear. I have
searched through the forums, but can't quite find what I am looking for. I
have an encumbered amount on a purchase order, ie $70,000.00. I want
everything I have purchased against that paticular purchase order to be
subtracted so that I know what my balance would be. Would the proper way to
do this be in a query or in a report? Kind of like a checkbook register is
what I want to mimick. [quantity]*[priceperunit] - the [encumbered] amount
of the purchase order. Thanks
Melinda

 

[Duane Hookom]

If I understand correctly, it would be easiest to do this in a report. Create
a text box
Name: txtRunSum
Control Source: =[quantity]*[priceperunit]
Running Sum: Over Group
Visible: No
Then create another text box:
Name: txtRunBalance
Control Source: =[Encumbered]-[txtRunSum]

 

[Melinda]

Duane--The txtrunsum worked for the total cost, but subtracting it from the
encumbered amount of the purchase order doesn't work. When I switch over to
Print Preview it prompts me to "Enter Parameter Value--txtRunSum" Do I need
to create a header with my encumbered beginning balance?

If I understand correctly, it would be easiest to do this in a report. Create
a text box
Name: txtRunSum
Control Source: =[quantity]*[priceperunit]
Running Sum: Over Group
Visible: No
Then create another text box:
Name: txtRunBalance
Control Source: =[Encumbered]-[txtRunSum]

--
Duane Hookom
Microsoft Access MVP

I am fairly new to access, so I am hoping this question is clear. I have
searched through the forums, but can't quite find what I am looking for. I
have an encumbered amount on a purchase order, ie $70,000.00. I want
everything I have purchased against that paticular purchase order to be
subtracted so that I know what my balance would be. Would the proper way to
do this be in a query or in a report? Kind of like a checkbook register is
what I want to mimick. [quantity]*[priceperunit] - the [encumbered] amount
of the purchase order. Thanks
Melinda

 

[Duane Hookom]

Did you create these new text boxes in the same report section?
--
Duane Hookom
Microsoft Access MVP

Duane--The txtrunsum worked for the total cost, but subtracting it from the
encumbered amount of the purchase order doesn't work. When I switch over to
Print Preview it prompts me to "Enter Parameter Value--txtRunSum" Do I need
to create a header with my encumbered beginning balance?

If I understand correctly, it would be easiest to do this in a report. Create
a text box
Name: txtRunSum
Control Source: =[quantity]*[priceperunit]
Running Sum: Over Group
Visible: No
Then create another text box:
Name: txtRunBalance
Control Source: =[Encumbered]-[txtRunSum]

--
Duane Hookom
Microsoft Access MVP

I am fairly new to access, so I am hoping this question is clear. I have
searched through the forums, but can't quite find what I am looking for. I
have an encumbered amount on a purchase order, ie $70,000.00. I want
everything I have purchased against that paticular purchase order to be
subtracted so that I know what my balance would be. Would the proper way to
do this be in a query or in a report? Kind of like a checkbook register is
what I want to mimick. [quantity]*[priceperunit] - the [encumbered] amount
of the purchase order. Thanks
Melinda

 

[Melinda]

I don't think I replied, so forgive me if I have. I put the two text boxes
in the report
in the detail section. The txtrunsum runs great with the correct results.
The TxtRunBalance I get a box to enter a Parameter Value tstrunsum---I have
been messing around with it and when I enter to bypass the prompt and I have
a running sum over the group it adds the balance of the first line for an
outrageous number.

If I understand correctly, it would be easiest to do this in a report. Create
a text box
Name: txtRunSum
Control Source: =[quantity]*[priceperunit]
Running Sum: Over Group
Visible: No
Then create another text box:
Name: txtRunBalance
Control Source: =[Encumbered]-[txtRunSum]

--
Duane Hookom
Microsoft Access MVP

I am fairly new to access, so I am hoping this question is clear. I have
searched through the forums, but can't quite find what I am looking for. I
have an encumbered amount on a purchase order, ie $70,000.00. I want
everything I have purchased against that paticular purchase order to be
subtracted so that I know what my balance would be. Would the proper way to
do this be in a query or in a report? Kind of like a checkbook register is
what I want to mimick. [quantity]*[priceperunit] - the [encumbered] amount
of the purchase order. Thanks
Melinda

 

[Melinda]

Yes I put them in the detail section

Did you create these new text boxes in the same report section?
--
Duane Hookom
Microsoft Access MVP

Duane--The txtrunsum worked for the total cost, but subtracting it from the
encumbered amount of the purchase order doesn't work. When I switch over to
Print Preview it prompts me to "Enter Parameter Value--txtRunSum" Do I need
to create a header with my encumbered beginning balance?

If I understand correctly, it would be easiest to do this in a report. Create
a text box
Name: txtRunSum
Control Source: =[quantity]*[priceperunit]
Running Sum: Over Group
Visible: No
Then create another text box:
Name: txtRunBalance
Control Source: =[Encumbered]-[txtRunSum]

--
Duane Hookom
Microsoft Access MVP


:

I am fairly new to access, so I am hoping this question is clear. I have
searched through the forums, but can't quite find what I am looking for. I
have an encumbered amount on a purchase order, ie $70,000.00. I want
everything I have purchased against that paticular purchase order to be
subtracted so that I know what my balance would be. Would the proper way to
do this be in a query or in a report? Kind of like a checkbook register is
what I want to mimick. [quantity]*[priceperunit] - the [encumbered] amount
of the purchase order. Thanks
Melinda

 

[Duane Hookom]

The controls need to be in the same section of the report.

Does your report group by Purchase Order Number?

"tstrunsum" should be txtRunSum

--
Duane Hookom
Microsoft Access MVP

I don't think I replied, so forgive me if I have. I put the two text boxes
in the report
in the detail section. The txtrunsum runs great with the correct results.
The TxtRunBalance I get a box to enter a Parameter Value tstrunsum---I have
been messing around with it and when I enter to bypass the prompt and I have
a running sum over the group it adds the balance of the first line for an
outrageous number.

If I understand correctly, it would be easiest to do this in a report. Create
a text box
Name: txtRunSum
Control Source: =[quantity]*[priceperunit]
Running Sum: Over Group
Visible: No
Then create another text box:
Name: txtRunBalance
Control Source: =[Encumbered]-[txtRunSum]

--
Duane Hookom
Microsoft Access MVP

I am fairly new to access, so I am hoping this question is clear. I have
searched through the forums, but can't quite find what I am looking for. I
have an encumbered amount on a purchase order, ie $70,000.00. I want
everything I have purchased against that paticular purchase order to be
subtracted so that I know what my balance would be. Would the proper way to
do this be in a query or in a report? Kind of like a checkbook register is
what I want to mimick. [quantity]*[priceperunit] - the [encumbered] amount
of the purchase order. Thanks
Melinda

 

[Melinda]

Correct, I am grouping by purchase order. Sorry, I had it typed in
txtRunSum, I just can't retype it correctly. I have checked all my spelling
to be sure it matches, but not sure where the error is.

The controls need to be in the same section of the report.

Does your report group by Purchase Order Number?

"tstrunsum" should be txtRunSum

--
Duane Hookom
Microsoft Access MVP

I don't think I replied, so forgive me if I have. I put the two text boxes
in the report
in the detail section. The txtrunsum runs great with the correct results.
The TxtRunBalance I get a box to enter a Parameter Value tstrunsum---I have
been messing around with it and when I enter to bypass the prompt and I have
a running sum over the group it adds the balance of the first line for an
outrageous number.

If I understand correctly, it would be easiest to do this in a report. Create
a text box
Name: txtRunSum
Control Source: =[quantity]*[priceperunit]
Running Sum: Over Group
Visible: No
Then create another text box:
Name: txtRunBalance
Control Source: =[Encumbered]-[txtRunSum]

--
Duane Hookom
Microsoft Access MVP


:

I am fairly new to access, so I am hoping this question is clear. I have
searched through the forums, but can't quite find what I am looking for. I
have an encumbered amount on a purchase order, ie $70,000.00. I want
everything I have purchased against that paticular purchase order to be
subtracted so that I know what my balance would be. Would the proper way to
do this be in a query or in a report? Kind of like a checkbook register is
what I want to mimick. [quantity]*[priceperunit] - the [encumbered] amount
of the purchase order. Thanks
Melinda

 

[Duane Hookom]

Tell us exactly what you now have for controls, control sources, sections, etc.
--
Duane Hookom
Microsoft Access MVP

Yes I put them in the detail section

Did you create these new text boxes in the same report section?
--
Duane Hookom
Microsoft Access MVP

Duane--The txtrunsum worked for the total cost, but subtracting it from the
encumbered amount of the purchase order doesn't work. When I switch over to
Print Preview it prompts me to "Enter Parameter Value--txtRunSum" Do I need
to create a header with my encumbered beginning balance?

:

If I understand correctly, it would be easiest to do this in a report. Create
a text box
Name: txtRunSum
Control Source: =[quantity]*[priceperunit]
Running Sum: Over Group
Visible: No
Then create another text box:
Name: txtRunBalance
Control Source: =[Encumbered]-[txtRunSum]

--
Duane Hookom
Microsoft Access MVP


:

I am fairly new to access, so I am hoping this question is clear. I have
searched through the forums, but can't quite find what I am looking for. I
have an encumbered amount on a purchase order, ie $70,000.00. I want
everything I have purchased against that paticular purchase order to be
subtracted so that I know what my balance would be. Would the proper way to
do this be in a query or in a report? Kind of like a checkbook register is
what I want to mimick. [quantity]*[priceperunit] - the [encumbered] amount
of the purchase order. Thanks
Melinda

 

[Melinda]

I don't know if I missed your reply, but I have tried several different ways
and nothing seems to work. The txtRunSum worked so sweet, it is odd that the
TxtRunBalance won't run a sum. thanks again

Correct, I am grouping by purchase order. Sorry, I had it typed in
txtRunSum, I just can't retype it correctly. I have checked all my spelling
to be sure it matches, but not sure where the error is.

The controls need to be in the same section of the report.

Does your report group by Purchase Order Number?

"tstrunsum" should be txtRunSum

--
Duane Hookom
Microsoft Access MVP

I don't think I replied, so forgive me if I have. I put the two text boxes
in the report
in the detail section. The txtrunsum runs great with the correct results.
The TxtRunBalance I get a box to enter a Parameter Value tstrunsum---I have
been messing around with it and when I enter to bypass the prompt and I have
a running sum over the group it adds the balance of the first line for an
outrageous number.


:

If I understand correctly, it would be easiest to do this in a report. Create
a text box
Name: txtRunSum
Control Source: =[quantity]*[priceperunit]
Running Sum: Over Group
Visible: No
Then create another text box:
Name: txtRunBalance
Control Source: =[Encumbered]-[txtRunSum]

--
Duane Hookom
Microsoft Access MVP


:

I am fairly new to access, so I am hoping this question is clear. I have
searched through the forums, but can't quite find what I am looking for. I
have an encumbered amount on a purchase order, ie $70,000.00. I want
everything I have purchased against that paticular purchase order to be
subtracted so that I know what my balance would be. Would the proper way to
do this be in a query or in a report? Kind of like a checkbook register is
what I want to mimick. [quantity]*[priceperunit] - the [encumbered] amount
of the purchase order. Thanks
Melinda

 

[Duane Hookom]

I just created a quick test of this in the Northwind using the Order Details
table. My report's record source is:
SELECT [Order Details].*, 1000 AS Encumbered
FROM [Order Details];
I grouped my report by OrderID with Header and Footer. I created a text box
in the detail section:
Name: txtRunSum
Control Source:=[Quantity]*[UnitPrice]
Running Sum: Over Group
I then created another text box in the detail section:
Control Source: =[Encumbered]-[txtRunSum]
This worked exactly as expected with each order detail line subtracting its
cost from the Encumbered value.
--
Duane Hookom
Microsoft Access MVP

I don't know if I missed your reply, but I have tried several different ways
and nothing seems to work. The txtRunSum worked so sweet, it is odd that the
TxtRunBalance won't run a sum. thanks again

Correct, I am grouping by purchase order. Sorry, I had it typed in
txtRunSum, I just can't retype it correctly. I have checked all my spelling
to be sure it matches, but not sure where the error is.

The controls need to be in the same section of the report.

Does your report group by Purchase Order Number?

"tstrunsum" should be txtRunSum

--
Duane Hookom
Microsoft Access MVP


:

I don't think I replied, so forgive me if I have. I put the two text boxes
in the report
in the detail section. The txtrunsum runs great with the correct results.
The TxtRunBalance I get a box to enter a Parameter Value tstrunsum---I have
been messing around with it and when I enter to bypass the prompt and I have
a running sum over the group it adds the balance of the first line for an
outrageous number.


:

If I understand correctly, it would be easiest to do this in a report. Create
a text box
Name: txtRunSum
Control Source: =[quantity]*[priceperunit]
Running Sum: Over Group
Visible: No
Then create another text box:
Name: txtRunBalance
Control Source: =[Encumbered]-[txtRunSum]

--
Duane Hookom
Microsoft Access MVP


:

I am fairly new to access, so I am hoping this question is clear. I have
searched through the forums, but can't quite find what I am looking for. I
have an encumbered amount on a purchase order, ie $70,000.00. I want
everything I have purchased against that paticular purchase order to be
subtracted so that I know what my balance would be. Would the proper way to
do this be in a query or in a report? Kind of like a checkbook register is
what I want to mimick. [quantity]*[priceperunit] - the [encumbered] amount
of the purchase order. Thanks
Melinda

 

[Melinda]

I finally got it to work, thanks for all your help.

I just created a quick test of this in the Northwind using the Order Details
table. My report's record source is:
SELECT [Order Details].*, 1000 AS Encumbered
FROM [Order Details];
I grouped my report by OrderID with Header and Footer. I created a text box
in the detail section:
Name: txtRunSum
Control Source:=[Quantity]*[UnitPrice]
Running Sum: Over Group
I then created another text box in the detail section:
Control Source: =[Encumbered]-[txtRunSum]
This worked exactly as expected with each order detail line subtracting its
cost from the Encumbered value.
--
Duane Hookom
Microsoft Access MVP

I don't know if I missed your reply, but I have tried several different ways
and nothing seems to work. The txtRunSum worked so sweet, it is odd that the
TxtRunBalance won't run a sum. thanks again

Correct, I am grouping by purchase order. Sorry, I had it typed in
txtRunSum, I just can't retype it correctly. I have checked all my spelling
to be sure it matches, but not sure where the error is.

:

The controls need to be in the same section of the report.

Does your report group by Purchase Order Number?

"tstrunsum" should be txtRunSum

--
Duane Hookom
Microsoft Access MVP


:

I don't think I replied, so forgive me if I have. I put the two text boxes
in the report
in the detail section. The txtrunsum runs great with the correct results.
The TxtRunBalance I get a box to enter a Parameter Value tstrunsum---I have
been messing around with it and when I enter to bypass the prompt and I have
a running sum over the group it adds the balance of the first line for an
outrageous number.


:

If I understand correctly, it would be easiest to do this in a report. Create
a text box
Name: txtRunSum
Control Source: =[quantity]*[priceperunit]
Running Sum: Over Group
Visible: No
Then create another text box:
Name: txtRunBalance
Control Source: =[Encumbered]-[txtRunSum]

--
Duane Hookom
Microsoft Access MVP


:

I am fairly new to access, so I am hoping this question is clear. I have
searched through the forums, but can't quite find what I am looking for. I
have an encumbered amount on a purchase order, ie $70,000.00. I want
everything I have purchased against that paticular purchase order to be
subtracted so that I know what my balance would be. Would the proper way to
do this be in a query or in a report? Kind of like a checkbook register is
what I want to mimick. [quantity]*[priceperunit] - the [encumbered] amount
of the purchase order. Thanks
Melinda