Counting Summary in Reports/Queries

[Guest]

I know this is covered but I have spend more time trying the solutions rather
than find the one I really need.

I have Workstations that belong to Branches and I need to count how many
Workstations are in each branch. The Branches field is a Combo Box if that
matters. I have 10 Branches. I need to create a summary of all the branches.

IE:
COB 5
IAAS 25
PGO 3

I tried
=Sum(ABS([BranchShort] = "IAAS"))
and get error "This expression is typed incorrectly, or it is too complex to
be evaluated. A Numberic expression may contain too many complicated
elements. Try simplifying the expression by assigning parts of the
expression to variables."

=Sum(Iff([BranchShort]="IAAS",1,0))
I get error "Undefined function IFF in Expression"

The info is coming from a query into a report. Is there any info I am missing?

 

[John Spencer]

Well if it I I F not I F F. So that is the first thing that you can try
to correct.

Second, why not use a simple totals query.

SELECT BranchShort, Count(WorkStations) as Count
FROM SomeTable
GROUP BY BranchShort



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Al Campagna]

Treeble,
You don't actually have to count the WSs in the query... just deliver the correct
listing of all the WSs (with their associated Branches). The counting can be done on the
form.
In the report Sorting and Grouping you would indicate a Branches Group, and in the
BranchGroupFooter...
= Count(Workstations)
would yield the count for each group.
You don't even need to use the Detail section, just the BranchFooter, and it will look
just like your example.
Also, in the ReportFooter... the same calculation...
= Count(Workstations)
would yield the total for all Branches.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Al Campagna]

Treeble,
In my response, I may have used inadvertently used the word "form" in my answer.
Everything I wrote pertains to a report.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

Thanks Al and John. I chose Al's solution which worked. I guess the only
further question I have is by putting that formula in the BranchNameFooter, I
got the count correct. However the caption all say "Text49". Can you help
me get the list of the names of the branches in the correct location.

Treeble,
You don't actually have to count the WSs in the query... just deliver the correct
listing of all the WSs (with their associated Branches). The counting can be done on the
form.
In the report Sorting and Grouping you would indicate a Branches Group, and in the
BranchGroupFooter...
= Count(Workstations)
would yield the count for each group.
You don't even need to use the Detail section, just the BranchFooter, and it will look
just like your example.
Also, in the ReportFooter... the same calculation...
= Count(Workstations)
would yield the total for all Branches.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I know this is covered but I have spend more time trying the solutions rather
than find the one I really need.

I have Workstations that belong to Branches and I need to count how many
Workstations are in each branch. The Branches field is a Combo Box if that
matters. I have 10 Branches. I need to create a summary of all the branches.

IE:
COB 5
IAAS 25
PGO 3

I tried
=Sum(ABS([BranchShort] = "IAAS"))
and get error "This expression is typed incorrectly, or it is too complex to
be evaluated. A Numberic expression may contain too many complicated
elements. Try simplifying the expression by assigning parts of the
expression to variables."

=Sum(Iff([BranchShort]="IAAS",1,0))
I get error "Undefined function IFF in Expression"

The info is coming from a query into a report. Is there any info I am missing?

 

[Al Campagna]

Treeble,
In the same calculated field in the Branch footer, change the claculation to...
= [BranchFieldName] & " Count = " & Count(Workstations)
This is called a concatenation.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Thanks Al and John. I chose Al's solution which worked. I guess the only
further question I have is by putting that formula in the BranchNameFooter, I
got the count correct. However the caption all say "Text49". Can you help
me get the list of the names of the branches in the correct location.

Treeble,
You don't actually have to count the WSs in the query... just deliver the correct
listing of all the WSs (with their associated Branches). The counting can be done on
the
form.
In the report Sorting and Grouping you would indicate a Branches Group, and in the
BranchGroupFooter...
= Count(Workstations)
would yield the count for each group.
You don't even need to use the Detail section, just the BranchFooter, and it will
look
just like your example.
Also, in the ReportFooter... the same calculation...
= Count(Workstations)
would yield the total for all Branches.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I know this is covered but I have spend more time trying the solutions rather
than find the one I really need.

I have Workstations that belong to Branches and I need to count how many
Workstations are in each branch. The Branches field is a Combo Box if that
matters. I have 10 Branches. I need to create a summary of all the branches.

IE:
COB 5
IAAS 25
PGO 3

I tried
=Sum(ABS([BranchShort] = "IAAS"))
and get error "This expression is typed incorrectly, or it is too complex to
be evaluated. A Numberic expression may contain too many complicated
elements. Try simplifying the expression by assigning parts of the
expression to variables."

=Sum(Iff([BranchShort]="IAAS",1,0))
I get error "Undefined function IFF in Expression"

The info is coming from a query into a report. Is there any info I am missing?