If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Count in a Report Footer of a Report
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! |
#2
|
|||
|
|||
Count in a Report Footer of a Report
=Count([Code1])
Or perhaps = Abs(Sum([code]="AAA")) John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County JE wrote: 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! |
#3
|
|||
|
|||
Count in a Report Footer of a Report
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/forum.aspx?tsn=1&nav=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 "JE" wrote: 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! |
Thread Tools | |
Display Modes | |
|
|