Subtracting in a report

  • Thread starter Thread starter Melinda
  • Start date Start date
M

Melinda

Hello All!
I am creating a report for a friend. It will display a
name along with guess as to a number of beans in a jar. I
want to have a column that displays the difference between
the guess and the actual number of beans. The problem is,
I don't have that actual number. So, I want my friend to
be able to enter this number in a form and then have the
report use that number to calculate the difference.
Here's the question: Is there an easy way to do this?
The only way I've been able to get calculations to work is
if the actual number and guess are in the same table. But
I don't want her to have to enter the actual number for
each guess. Suggestions?
Thanks!
Melinda
 
Melinda said:
I am creating a report for a friend. It will display a
name along with guess as to a number of beans in a jar. I
want to have a column that displays the difference between
the guess and the actual number of beans. The problem is,
I don't have that actual number. So, I want my friend to
be able to enter this number in a form and then have the
report use that number to calculate the difference.
Here's the question: Is there an easy way to do this?
The only way I've been able to get calculations to work is
if the actual number and guess are in the same table. But
I don't want her to have to enter the actual number for
each guess.
Click to expand...


You can use a text box on the form to enter theactual
number. Use a query to retrieve the guesses along with a
calculated field that refers back to the form:

Diff: Forms!theform.thetextbox - thetable.guess
 
In the design view of her table, she can set the Default Value of the field
which contains the Number of Jelly Beans to the Actual Number of Jelly Beans
 
Melinda,

Try putting the actual number of beans in a control on the
form and change the controls visible properties from yes
to no. In your query that will be use for the report
create an expression that will subtact the quess from the
actual number on the form. ie =[Forms].[frmName].[Actual]-
[Quess]

Charlie O'Neill
 
Thanks for all of the suggestions. I ended up using a
variation of Marsh's. All I did was create a field in my
query that said Diff: [Actual]-[tblGuesses!Guess] This
essentially created a parameter query that allows my
friend to enter the actual value when she opens the
report. I never realized that one could use parameter
querys for numerical values. Nor had I seen the Diff
function.
Thanks again!
Melinda
 
Melinda said:
Thanks for all of the suggestions. I ended up using a
variation of Marsh's. All I did was create a field in my
query that said Diff: [Actual]-[tblGuesses!Guess] This
essentially created a parameter query that allows my
friend to enter the actual value when she opens the
report. I never realized that one could use parameter
querys for numerical values. Nor had I seen the Diff
function.
Click to expand...


I hope that was just a posting typo, the brackets are
wrong:

Diff: [Actual] - [tblGuesses]![Guess]
--
Marsh
MVP [MS Access]



 
Back
Top