Calculations

  • Thread starter Thread starter bill
  • Start date Start date
B

bill

I have a form that requests input from the user in 5
seperate, yet identical text boxes (the are asked to
enter up to 5 different job numbers). I then want to do
a sum calculation on a report for a field in the query
for each of the 5 different job numbers.

Can anyone provide the syntax for the report text field?

Thanks much
Bill
 
Hi, Bill. The syntax for totaling all the values of a
field is:

=Sum([yourfieldname])

Place it in the report footer or header.

If you're also asking how to select only these records
from the report's underlying data source, construct a
WHERE clause from the user inputted job numbers, such as:

Dim strCriteria As String
strCriteria = "[JobNumber]=" & textbox1 & " _
OR [JobNumber] =" & textbox2 & " _
OR [JobNumber] =" & textbox3 & " _
OR [JobNumber] =" & textbox4 & " _
OR [JobNumber] =" & textbox5

Use this criteria when you open the report:

DoCmd.OpenReport("yourreportname",acViewPreview, _
"yourqueryname", strCriteria,,)

HTH
Kevin Sprinkel




and
 
Thanks Kevin.

In a little more detail, here is what I am looking to do:

The user can select upto 5 different job numbers. The
data on the report comes from a field that contains the
value 1-5. I want the report to look something like:

Job #1 Job #2 Job #3 etc.
(5) ### (5) ### (5) ###
(4) ### (4) ### (4) ###
(3) ### (3) ### (3) ###
etc.

I need all five jobs to print on the same report, just
summarized seperately. I cant figure out the correct
syntax for the control source that includes the "when"
condition for each job#
-----Original Message-----
Hi, Bill. The syntax for totaling all the values of a
field is:

=Sum([yourfieldname])

Place it in the report footer or header.

If you're also asking how to select only these records
from the report's underlying data source, construct a
WHERE clause from the user inputted job numbers, such as:

Dim strCriteria As String
strCriteria = "[JobNumber]=" & textbox1 & " _
OR [JobNumber] =" & textbox2 & " _
OR [JobNumber] =" & textbox3 & " _
OR [JobNumber] =" & textbox4 & " _
OR [JobNumber] =" & textbox5

Use this criteria when you open the report:

DoCmd.OpenReport("yourreportname",acViewPreview, _
"yourqueryname", strCriteria,,)

HTH
Kevin Sprinkel




and
-----Original Message-----
I have a form that requests input from the user in 5
seperate, yet identical text boxes (the are asked to
enter up to 5 different job numbers). I then want to do
a sum calculation on a report for a field in the query
for each of the 5 different job numbers.

Can anyone provide the syntax for the report text field?

Thanks much
Bill
.
Click to expand...
.
Click to expand...
 
Bill,

I'm not clear on what you're trying to do. Please post the
underlying table definition (its fields and field types).
Background of the business task you're trying to
accomplish would also be helpful.

It looks like you're after the Count of each value grouped
by JobNumber. E.G., if your table data looked like:

JobNumber OtherFieldValue
11111 5
11111 5
11111 5
11111 3
11111 2
11112 2
11112 3
11112 3

are you interested in the following output?

JobNumber 11111
Count of 5: 3
Count of 3: 1
Count of 2: 2

Job Number 11112
Count of 2: 1
Count of 3: 2

KGS
-----Original Message-----
Thanks Kevin.

In a little more detail, here is what I am looking to do:

The user can select upto 5 different job numbers. The
data on the report comes from a field that contains the
value 1-5. I want the report to look something like:

Job #1 Job #2 Job #3 etc.
(5) ### (5) ### (5) ###
(4) ### (4) ### (4) ###
(3) ### (3) ### (3) ###
etc.

I need all five jobs to print on the same report, just
summarized seperately. I cant figure out the correct
syntax for the control source that includes the "when"
condition for each job#
-----Original Message-----
Hi, Bill. The syntax for totaling all the values of a
field is:

=Sum([yourfieldname])

Place it in the report footer or header.

If you're also asking how to select only these records
from the report's underlying data source, construct a
WHERE clause from the user inputted job numbers, such as:

Dim strCriteria As String
strCriteria = "[JobNumber]=" & textbox1 & " _
OR [JobNumber] =" & textbox2 & " _
OR [JobNumber] =" & textbox3 & " _
OR [JobNumber] =" & textbox4 & " _
OR [JobNumber] =" & textbox5

Use this criteria when you open the report:

DoCmd.OpenReport("yourreportname",acViewPreview, _
"yourqueryname", strCriteria,,)

HTH
Kevin Sprinkel




and
-----Original Message-----
I have a form that requests input from the user in 5
seperate, yet identical text boxes (the are asked to
enter up to 5 different job numbers). I then want to do
a sum calculation on a report for a field in the query
for each of the 5 different job numbers.

Can anyone provide the syntax for the report text field?

Thanks much
Bill
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Kevin, thats exactly what I am trying to do
-----Original Message-----
Bill,

I'm not clear on what you're trying to do. Please post the
underlying table definition (its fields and field types).
Background of the business task you're trying to
accomplish would also be helpful.

It looks like you're after the Count of each value grouped
by JobNumber. E.G., if your table data looked like:

JobNumber OtherFieldValue
11111 5
11111 5
11111 5
11111 3
11111 2
11112 2
11112 3
11112 3

are you interested in the following output?

JobNumber 11111
Count of 5: 3
Count of 3: 1
Count of 2: 2

Job Number 11112
Count of 2: 1
Count of 3: 2

KGS
-----Original Message-----
Thanks Kevin.

In a little more detail, here is what I am looking to do:

The user can select upto 5 different job numbers. The
data on the report comes from a field that contains the
value 1-5. I want the report to look something like:

Job #1 Job #2 Job #3 etc.
(5) ### (5) ### (5) ###
(4) ### (4) ### (4) ###
(3) ### (3) ### (3) ###
etc.

I need all five jobs to print on the same report, just
summarized seperately. I cant figure out the correct
syntax for the control source that includes the "when"
condition for each job#
-----Original Message-----
Hi, Bill. The syntax for totaling all the values of a
field is:

=Sum([yourfieldname])

Place it in the report footer or header.

If you're also asking how to select only these records
from the report's underlying data source, construct a
WHERE clause from the user inputted job numbers, such as:

Dim strCriteria As String
strCriteria = "[JobNumber]=" & textbox1 & " _
OR [JobNumber] =" & textbox2 & " _
OR [JobNumber] =" & textbox3 & " _
OR [JobNumber] =" & textbox4 & " _
OR [JobNumber] =" & textbox5

Use this criteria when you open the report:

DoCmd.OpenReport("yourreportname",acViewPreview, _
"yourqueryname", strCriteria,,)

HTH
Kevin Sprinkel




and

-----Original Message-----
I have a form that requests input from the user in 5
seperate, yet identical text boxes (the are asked to
enter up to 5 different job numbers). I then want to do
a sum calculation on a report for a field in the query
for each of the 5 different job numbers.

Can anyone provide the syntax for the report text field?

Thanks much
Bill
.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Kevin, below is the code I am trying to implement. Both
Study Number is a text field in the table Startup and
txtjob1 is a textbox on form frmFPIselect

=Sum(Abs([FPI]=5)) where "[Startup]![Study Number]"
= "[forms]![frmFRISelect]![txtjob1]"

Just having problems with the exact syntax

Any help is greatly appreciated
-----Original Message-----
Bill,

I'm not clear on what you're trying to do. Please post the
underlying table definition (its fields and field types).
Background of the business task you're trying to
accomplish would also be helpful.

It looks like you're after the Count of each value grouped
by JobNumber. E.G., if your table data looked like:

JobNumber OtherFieldValue
11111 5
11111 5
11111 5
11111 3
11111 2
11112 2
11112 3
11112 3

are you interested in the following output?

JobNumber 11111
Count of 5: 3
Count of 3: 1
Count of 2: 2

Job Number 11112
Count of 2: 1
Count of 3: 2

KGS
-----Original Message-----
Thanks Kevin.

In a little more detail, here is what I am looking to do:

The user can select upto 5 different job numbers. The
data on the report comes from a field that contains the
value 1-5. I want the report to look something like:

Job #1 Job #2 Job #3 etc.
(5) ### (5) ### (5) ###
(4) ### (4) ### (4) ###
(3) ### (3) ### (3) ###
etc.

I need all five jobs to print on the same report, just
summarized seperately. I cant figure out the correct
syntax for the control source that includes the "when"
condition for each job#
-----Original Message-----
Hi, Bill. The syntax for totaling all the values of a
field is:

=Sum([yourfieldname])

Place it in the report footer or header.

If you're also asking how to select only these records
from the report's underlying data source, construct a
WHERE clause from the user inputted job numbers, such as:

Dim strCriteria As String
strCriteria = "[JobNumber]=" & textbox1 & " _
OR [JobNumber] =" & textbox2 & " _
OR [JobNumber] =" & textbox3 & " _
OR [JobNumber] =" & textbox4 & " _
OR [JobNumber] =" & textbox5

Use this criteria when you open the report:

DoCmd.OpenReport("yourreportname",acViewPreview, _
"yourqueryname", strCriteria,,)

HTH
Kevin Sprinkel




and

-----Original Message-----
I have a form that requests input from the user in 5
seperate, yet identical text boxes (the are asked to
enter up to 5 different job numbers). I then want to do
a sum calculation on a report for a field in the query
for each of the 5 different job numbers.

Can anyone provide the syntax for the report text field?

Thanks much
Bill
.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
-----Original Message-----
Kevin, thats exactly what I am trying to do
Click to expand...

OK; good.

First create a Totals query. Start with a new query based
on your table. Select the JobNumber field, then your data
field TWICE. Now switch to Totals view (View, Totals).

Access will have placed "Group By" in the Totals row of
all three fields. Change the last instance of your data
field to "Count". The query will now count the number of
records of each unique combination of JobNumber and your
data field.

Now, have the Report Wizard create a new report based on
this query. Select all three fields, and group on
JobNumber. Output will be something like:

JobNumber YourDataField CountofYourDataField
=================================================
11111 1 15
2 16
3 25
4 29
5 37

11112 1 18
2 37
3 21
4 22
5 38

HTH
Kevin Sprinkel
 
Back
Top