Calculated Field

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

I am trying to calculate how many values that equal 5 in
in a field named [Grade1]. This calculated field is in a
report. The following calculation is what I am working on
but it is not working.

=DCount([Grade1],(5))

Thanks Dennis
 
Here's an alternative: try calculating the count in a separate query, then
joining this query object with the other data in the report source.
 
Dennis,

The format for DCOUNT() contains a field name, a table name, and a
criteria

=DCOUNT("fieldname", "tableName", "[Grade1] = 5")

All of these parameters must be enclosed in quotes. However, if you
want to use a control to reference the value instead of hardcoding '5'
into the database.

=DCOUNT("fieldname", "tableName", "[Grade1] = " &
forms!formName!ctrlName.value)

--
HTH

Dale Fye


I am trying to calculate how many values that equal 5 in
in a field named [Grade1]. This calculated field is in a
report. The following calculation is what I am working on
but it is not working.

=DCount([Grade1],(5))

Thanks Dennis
 
Sirocco,
I was not clear and only gave one result unstead of the 4
results I need. I tried your solution and it workd for
what I asked for but does not work for what I need. I have
values in the Table [Grade1] that value 1, 2, 3, 4, and 5.
I need to count all of the 1 and 2 together and count 3,
4, and 5 seperately looking like this:
-----------------------
1 & 2 3 4 5
-----------------------
32 25 10 15

When I try to expand your idea to include all of these
results I end up with the same totals in each field
result. The expression I am using is:

Grade4: Count([Employees]![Grade1]=4)

Thanks Dennis

-----Original Message-----
Here's an alternative: try calculating the count in a separate query, then
joining this query object with the other data in the report source.


I am trying to calculate how many values that equal 5 in
in a field named [Grade1]. This calculated field is in a
report. The following calculation is what I am working on
but it is not working.

=DCount([Grade1],(5))

Thanks Dennis
Click to expand...


.
Click to expand...
 
Dennis

Try creating a query with 4 "new" fields (calculated field)
In design view do something like the following for the
field (leaving table blank).
CountGrade1and2:=DCOUNT("[Grade1]", "tableName", "[Grade1]
= 1")+=DCOUNT("[Grade2]", "tableName", "[Grade1] = 2")
Next calculated field
CountGrade3:==DCOUNT("[Grade1]", "tableName", "[Grade1] =
3")
and so forth.

Use this query in your report.

You could also probably create text boxes within the
report with the above formulas instead of an actually
field name.

Jason




-----Original Message-----
Sirocco,
I was not clear and only gave one result unstead of the 4
results I need. I tried your solution and it workd for
what I asked for but does not work for what I need. I have
values in the Table [Grade1] that value 1, 2, 3, 4, and 5.
I need to count all of the 1 and 2 together and count 3,
4, and 5 seperately looking like this:
-----------------------
1 & 2 3 4 5
-----------------------
32 25 10 15

When I try to expand your idea to include all of these
results I end up with the same totals in each field
result. The expression I am using is:

Grade4: Count([Employees]![Grade1]=4)

Thanks Dennis

-----Original Message-----
Here's an alternative: try calculating the count in a separate query, then
joining this query object with the other data in the report source.


I am trying to calculate how many values that equal 5 in
in a field named [Grade1]. This calculated field is in a
report. The following calculation is what I am working on
but it is not working.

=DCount([Grade1],(5))

Thanks Dennis
Click to expand...


.
Click to expand...
.
Click to expand...
 
Don't use any code or DCount() expressions. All you need is
=Abs(Sum([Grade1]=5))
To get all the 1s and 2s counted together
=Abs(Sum([Grade1]=1 or [Grade1]=2))
 
Wouldn't that give you the Sum of the values? Don't we want the Count?


Duane Hookom said:
Don't use any code or DCount() expressions. All you need is
=Abs(Sum([Grade1]=5))
To get all the 1s and 2s counted together
=Abs(Sum([Grade1]=1 or [Grade1]=2))

--
Duane Hookom
MS Access MVP


Dennis said:
I am trying to calculate how many values that equal 5 in
in a field named [Grade1]. This calculated field is in a
report. The following calculation is what I am working on
but it is not working.

=DCount([Grade1],(5))

Thanks Dennis
Click to expand...
Click to expand...
 
The expression inside the Sum() will evaluate to either True/-1 or False/0.
Summing these will result in a number that is the inverse of the count.
That's why the Abs().

--
Duane Hookom
MS Access MVP


Sirocco said:
Wouldn't that give you the Sum of the values? Don't we want the Count?


Duane Hookom said:
Don't use any code or DCount() expressions. All you need is
=Abs(Sum([Grade1]=5))
To get all the 1s and 2s counted together
=Abs(Sum([Grade1]=1 or [Grade1]=2))

--
Duane Hookom
MS Access MVP


Dennis said:
I am trying to calculate how many values that equal 5 in
in a field named [Grade1]. This calculated field is in a
report. The following calculation is what I am working on
but it is not working.

=DCount([Grade1],(5))

Thanks Dennis
Click to expand...
Click to expand...
Click to expand...
 
But why would the expression inside the Sum() function evaluate to either T
or F? I wonder if Abs([Grade1]=1 or [Grade1]=2) will do the same thing
(notice I left out the inner "Sum" function, which seems to serve no
purpose).




Duane Hookom said:
The expression inside the Sum() will evaluate to either True/-1 or False/0.
Summing these will result in a number that is the inverse of the count.
That's why the Abs().

--
Duane Hookom
MS Access MVP


Sirocco said:
Wouldn't that give you the Sum of the values? Don't we want the Count?


Duane Hookom said:
Don't use any code or DCount() expressions. All you need is
=Abs(Sum([Grade1]=5))
To get all the 1s and 2s counted together
=Abs(Sum([Grade1]=1 or [Grade1]=2))

--
Duane Hookom
MS Access MVP


I am trying to calculate how many values that equal 5 in
in a field named [Grade1]. This calculated field is in a
report. The following calculation is what I am working on
but it is not working.

=DCount([Grade1],(5))

Thanks Dennis
Click to expand...
Click to expand...
Click to expand...
 
Sirocco said:
But why would the expression inside the Sum() function evaluate to either T
or F? I wonder if Abs([Grade1]=1 or [Grade1]=2) will do the same thing
(notice I left out the inner "Sum" function, which seems to serve no
purpose).
Click to expand...

That is how comparisons work. Go to the immediate window and type...

?(2+3)=5 <Enter>

You will get True as a response.
 
Back
Top