Count in a Report Footer of a Report

[JE]

I have a report (run off a query) that lists:

Name Dept Code 1 Code2 Code 3

The Code fields are text (Code1 = AAA, Code 2 = BBB, Code 3 = CCC).

Each person may have results in any, some or all of the code fields. I want
to add a total count at the bottom of the report.

I added a text box in the footer. Originally I used ControlSource code of
=Count(*) but that resulted in counting every line regardless if there was
text. I then tried: =Count(*) And [QryName.Label]="Field Name" but that
resulted in an Error.

Can this be accomplished?

Many thanks!

 

[John Spencer]

=Count([Code1])

Or perhaps
= Abs(Sum(

="AAA"))


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

 

[Ken Sheridan]

Both of John's solutions should work, or if you want to avoid reliance on the
implementation: =Sum(IIf([Code 1]="AAA",1,0)), should work to return a count
of all rows with a value of "AAA" in the Code 1 column.

However, by having three columns your table design is flawed. You are in
effect doing what's known as 'encoding data as column headings' or more
colloquially 'committing spreadsheet'. In a relational database the
underlying table should be normalized by decomposition into two tables, by
creating a Codes table with a foreign key column referencing the primary key
of the existing table, e.g. EmployeeID, and a Code column. This table might
look like this:

EmployeeID Code
1 AAA
1 BBB
2 AAA
2 CCC
3 AAA
3 BBB
3 CCC

To produce a report in the same layout as at present you could, provided the
codes are a fixed set of values (as seems to be the case), base it on a
crosstab query, or you could use a multi-column subreport for the codes.
You'll find an example of the latter used in this way at:


http://community.netscape.com/n/pfx...libraryMessages&webtag=ws-msdevapps&tid=24271


The file available from the link also includes a single report solution in
which the column positions are adjusted in code in the form's module, but a
subreport is a lot easier. The code solution was only included because the
file was originally produced in response to a question by a reader of a
magazine column written by a contact of mine, to show that it could be done,
not that it should!

If you are thinking that it would be a big task to move all the data from
your current non-normalized table to a normalized Codes table, then it can be
done very easily and quickly with 'append' queries. Let us know if you need
guidance on how to do that.

Ken Sheridan
Stafford, England