Count multiple categories within the same report header

[Whitney]

I have an attrition report that is sorted by end date by month, in the end
date header I have =count(*) counting total records for the month.

Withing that same month I would like to also count the employee status
(temps vs Regular), total Temps and total Regular; as well as count the
separation status (voluntary vs involuntary) total voluntary and total
involuntary, within the same hearder. What count formula can I use for each
text box to distinguish between the categories?

Thanks!

 

[akphidelt]

Look up DCount

Just a broad overview I think it is

Dcount("fieldname","tablename","where") so your first one would look kind of
like

Dcount("empstatus","tblemployee","where empstatus='temps'")

Not entirely 100% sure on the syntax... but that could be a jump start if
you just search it on google.

 

[KARL DEWEY]

Total Temps: Sum(IIF([employee status] = "Temp", 0))
Total Regular: Sum(IIF([employee status] = "Regular", 0))

Total Voluntary: Sum(IIF([separation status] = "voluntary", 1, 0))
Total Involuntary: Sum(IIF([separation status] = "involuntary", 1, 0))

 

[John Spencer]

I believe that Karl made one small error in his example code.

Total Temps: Sum(IIF([employee status] = "Temp",1,0))
Total Regular: Sum(IIF([employee status] = "Regular",1,0))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Whitney]

Where do I put this, in the query or in a text box in the report header?

I believe that Karl made one small error in his example code.

Total Temps: Sum(IIF([employee status] = "Temp",1,0))
Total Regular: Sum(IIF([employee status] = "Regular",1,0))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Total Temps: Sum(IIF([employee status] = "Temp", 0))
Total Regular: Sum(IIF([employee status] = "Regular", 0))

Total Voluntary: Sum(IIF([separation status] = "voluntary", 1, 0))
Total Involuntary: Sum(IIF([separation status] = "involuntary", 1, 0))

 

[John Spencer]

Add a text box for each expression
Set the Control source to = theexpression

Control Name: txtCountTemps
Control Source: =Sum(IIF([employee status] = "Temp",1,0))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Where do I put this, in the query or in a text box in the report header?

I believe that Karl made one small error in his example code.

Total Temps: Sum(IIF([employee status] = "Temp",1,0))
Total Regular: Sum(IIF([employee status] = "Regular",1,0))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Total Temps: Sum(IIF([employee status] = "Temp", 0))
Total Regular: Sum(IIF([employee status] = "Regular", 0))

Total Voluntary: Sum(IIF([separation status] = "voluntary", 1, 0))
Total Involuntary: Sum(IIF([separation status] = "involuntary", 1, 0))

 

[John Spencer]

I've never seen that error message associated with the control source of
a control on a form or report.

Where did you enter the expression? Hopefully not in a query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

It says the expression is too complex.

Add a text box for each expression
Set the Control source to = theexpression

Control Name: txtCountTemps
Control Source: =Sum(IIF([employee status] = "Temp",1,0))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Where do I put this, in the query or in a text box in the report header?

:

I believe that Karl made one small error in his example code.

Total Temps: Sum(IIF([employee status] = "Temp",1,0))
Total Regular: Sum(IIF([employee status] = "Regular",1,0))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

KARL DEWEY wrote:
Total Temps: Sum(IIF([employee status] = "Temp", 0))
Total Regular: Sum(IIF([employee status] = "Regular", 0))

Total Voluntary: Sum(IIF([separation status] = "voluntary", 1, 0))
Total Involuntary: Sum(IIF([separation status] = "involuntary", 1, 0))

 

[Whitney]

I defenitely entered the formula into a text box in the report.
However, where I think the problem lies is with the database design. I
inherited this database and am not sure if it was set up the best way.
In actuality the EmployeeStatus field on the report is pulling info from the
EmployeeStatusCode field in the query. Instead of "Temp" it is code "1",
Regular is "2". Same with the Separation Status, voluntary is "1",
involuntary is "2".

The row source on the EmployeeStatus fields reads like below to display the
status instead of the code in the query results.
SELECT EmployeeStatus.EmployeeStatusCode, EmployeeStatus.EmployeeStatus FROM
EmployeeStatus;

Errors:
=Sum(IIf([EmployeeStatus]="Temp",1,0)) Doesn't recognize EmployeeStatus
or
=Sum(IIf([EmployeeStatusCode]="1",1,0)) The expression is typed
incorrectly, or it is too complex to be evaluated.

HELP!! What am I doing wrong?

I've never seen that error message associated with the control source of
a control on a form or report.

Where did you enter the expression? Hopefully not in a query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

It says the expression is too complex.

Add a text box for each expression
Set the Control source to = theexpression

Control Name: txtCountTemps
Control Source: =Sum(IIF([employee status] = "Temp",1,0))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Whitney wrote:
Where do I put this, in the query or in a text box in the report header?

:

I believe that Karl made one small error in his example code.

Total Temps: Sum(IIF([employee status] = "Temp",1,0))
Total Regular: Sum(IIF([employee status] = "Regular",1,0))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

KARL DEWEY wrote:
Total Temps: Sum(IIF([employee status] = "Temp", 0))
Total Regular: Sum(IIF([employee status] = "Regular", 0))

Total Voluntary: Sum(IIF([separation status] = "voluntary", 1, 0))
Total Involuntary: Sum(IIF([separation status] = "involuntary", 1, 0))

 

[Douglas J. Steele]

Are you sure they're text and not actually numbers? Try removing the quotes
from around the values.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I defenitely entered the formula into a text box in the report.
However, where I think the problem lies is with the database design. I
inherited this database and am not sure if it was set up the best way.
In actuality the EmployeeStatus field on the report is pulling info from
the
EmployeeStatusCode field in the query. Instead of "Temp" it is code "1",
Regular is "2". Same with the Separation Status, voluntary is "1",
involuntary is "2".

The row source on the EmployeeStatus fields reads like below to display
the
status instead of the code in the query results.
SELECT EmployeeStatus.EmployeeStatusCode, EmployeeStatus.EmployeeStatus
FROM
EmployeeStatus;

Errors:
=Sum(IIf([EmployeeStatus]="Temp",1,0)) Doesn't recognize
EmployeeStatus
or
=Sum(IIf([EmployeeStatusCode]="1",1,0)) The expression is typed
incorrectly, or it is too complex to be evaluated.

HELP!! What am I doing wrong?

I've never seen that error message associated with the control source of
a control on a form or report.

Where did you enter the expression? Hopefully not in a query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

It says the expression is too complex.

:

Add a text box for each expression
Set the Control source to = theexpression

Control Name: txtCountTemps
Control Source: =Sum(IIF([employee status] = "Temp",1,0))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Whitney wrote:
Where do I put this, in the query or in a text box in the report
header?

:

I believe that Karl made one small error in his example code.

Total Temps: Sum(IIF([employee status] = "Temp",1,0))
Total Regular: Sum(IIF([employee status] = "Regular",1,0))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

KARL DEWEY wrote:
Total Temps: Sum(IIF([employee status] = "Temp", 0))
Total Regular: Sum(IIF([employee status] = "Regular", 0))

Total Voluntary: Sum(IIF([separation status] = "voluntary", 1, 0))
Total Involuntary: Sum(IIF([separation status] = "involuntary", 1,
0))

 

[Whitney]

Ok, now I feel dumb, but thank you it worked.

Are you sure they're text and not actually numbers? Try removing the quotes
from around the values.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I defenitely entered the formula into a text box in the report.
However, where I think the problem lies is with the database design. I
inherited this database and am not sure if it was set up the best way.
In actuality the EmployeeStatus field on the report is pulling info from
the
EmployeeStatusCode field in the query. Instead of "Temp" it is code "1",
Regular is "2". Same with the Separation Status, voluntary is "1",
involuntary is "2".

The row source on the EmployeeStatus fields reads like below to display
the
status instead of the code in the query results.
SELECT EmployeeStatus.EmployeeStatusCode, EmployeeStatus.EmployeeStatus
FROM
EmployeeStatus;

Errors:
=Sum(IIf([EmployeeStatus]="Temp",1,0)) Doesn't recognize
EmployeeStatus
or
=Sum(IIf([EmployeeStatusCode]="1",1,0)) The expression is typed
incorrectly, or it is too complex to be evaluated.

HELP!! What am I doing wrong?

I've never seen that error message associated with the control source of
a control on a form or report.

Where did you enter the expression? Hopefully not in a query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Whitney wrote:
It says the expression is too complex.

:

Add a text box for each expression
Set the Control source to = theexpression

Control Name: txtCountTemps
Control Source: =Sum(IIF([employee status] = "Temp",1,0))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Whitney wrote:
Where do I put this, in the query or in a text box in the report
header?

:

I believe that Karl made one small error in his example code.

Total Temps: Sum(IIF([employee status] = "Temp",1,0))
Total Regular: Sum(IIF([employee status] = "Regular",1,0))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

KARL DEWEY wrote:
Total Temps: Sum(IIF([employee status] = "Temp", 0))
Total Regular: Sum(IIF([employee status] = "Regular", 0))

Total Voluntary: Sum(IIF([separation status] = "voluntary", 1, 0))
Total Involuntary: Sum(IIF([separation status] = "involuntary", 1,
0))