Counts of defined records

[Banderberg]

Report has records of errors. There are 3 types of error. In the report footer, I would like a total count (this is working!) and a count by type (can't get this to work).

Error Total =Count[ErrorType]
Compliance Total =Sum(IIf([ErrorType]="Compliance",1,0))
Audit Total =Sum(IIf([ErrorType]="Audit",1,0))
Quality Total =Sum(IIf([ErrorType]="Quality",1,0))

I have tried setting groupings on the report but this alters the detail sorting (currently sent by the date field newest to oldest) and provides the count within the detail.

Any guidance would be appreciated. Thanks - B

 

[CJ_London]

Try putting three hidden fields in the detail section with a control source of

IIf([ErrorType]="Compliance",1,0)
etc

Then at the repport footer

Compliance Total =Sum(ComplianceFld)
etc

 

[Banderberg]

A million thanks CJ...I feel like I'm heading in the right direction. I followed your instruction above (with novice interpretation) and had less than stellar results:

1) IIf control source in detail. This only worked for 1 of 10 lines.
2) Sum control source in footer. Not working, no totals provided.
3) When repoer opens, a dialoque windows requesting parameter values for AuditFld, ComplianceFld, etc...is popping up.

Please see attached screen prints of report in design view and report view.

 

[Banderberg]

Attachments....View attachment Error_IIf_reprt2.pdf

View attachment Error_IIf_design2.pdf

View attachment Error_IIf_design1.pdf

 

[CJ_London]

The coding looks OK so this implies it is something to do with the data.

The ErrorType control - is the rowsource only producing Audit, Compliance and Quality strings (image is too fuzzy)? either as a value list or as something like "SELECT EType from ErrorTable" - what I am trying to determine is that what you see in the errortype control is 'correct' for the formula.

If your errortype control has a rowsource such as SELECT EType, EDescription from ErrorTable and the bound column is 1 then your formula is picking up EType rather than EDescription

 

[Banderberg]

You are correct - the control source is for ErrorType is:

SELECT [Error_Type].[ID], [Error_Type].[Error_Type] FROM Error_Type ORDER BY [Error_Type];

THANKS for all your help!

 

[CJ_London]

no problem! Just change your dcount by replacing compliance with whatever value is ID - note if ID is numeric, you will need to remove the quotation marks

 

[Banderberg]

Thanks CJ - YES!!! that worked in the reports detail.

Now to address the sums in the footer. These are not working and when the report opens a dialogue window appears request a parameter value for these 3 fields.

 

[CJ_London]

Are they correctly referencing the name of the dlookup controls in the detail area (they are not called text1 etc?

 

[Banderberg]

The name of the text box in the detail is Accuracy_Sum
The expression in the footer is =Sum([Accuracy_Sum]) i tried with and without quote ticks around control name.

If has a IIF expression not a Dlookup - I'm not sure if that makes a difference, or the terms are synonymous. =IIF([ErrorType]=1,1,0)

Your promptness is greatly appreciated!

 

[CJ_London]

my mistake re dlookup - mixing up the threads!

I've just set up a simple test report and have the same problem, however if the iff statements are put into your source query it will then works - It looks like the report won't sum unbound controls
ie.. Select ..... IIF([ErrorType]=1,1,0) As Accuracy_Sum,..... FROM..

Note there is no need to put the field into the report, unless you want to show it

 

[Banderberg]

CJ can you clarify what the query expression should be. I have tried a few different ones and not getting the results intended.

And Yes, the intention is for the report to show the sums but just as a summary in footer, not in the detail (I was making that field not visible on the report).

 

[CJ_London]

I don't have the full query, it's not been supplied. It goes in your report recordsource

If you post the query for your form recordsource I can show you where. Also look at this link which resolves a similar problem

http://www.access-programmers.co.uk/forums/showthread.php?p=1247522#post1247522

 

[Banderberg]

The query is the recordsource for the report.
Here is the SQL for the query. It may be note worthy that 2 of the fields have criteria that is coming from a dialogue form.

SELECT Error_Entry.TM, Error_Entry.Shift, Error_Entry.ErrorDept, Error_Entry.DateError, Error_Entry.DateDiscovery, Error_Entry.DateDiscussion, Error_Entry.AuditType, Error_Entry.ErrorCause, Error_Entry.Comments, Error_Entry.LPN, Error_Entry.Action, Error_Entry.FormReturn, Error_Entry.SUPV, Error_Entry.ErrorType, IIf(["ErrorType"]=2,1,0) AS CompTotal, IIf(["ErrorType"]=3,1,0) AS QualTotal, IIf(["ErrorType"]=1,1,0) AS AccTotal
FROM Error_Entry
GROUP BY Error_Entry.TM, Error_Entry.Shift, Error_Entry.ErrorDept, Error_Entry.DateError, Error_Entry.DateDiscovery, Error_Entry.DateDiscussion, Error_Entry.AuditType, Error_Entry.ErrorCause, Error_Entry.Comments, Error_Entry.LPN, Error_Entry.Action, Error_Entry.FormReturn, Error_Entry.SUPV, Error_Entry.ErrorType, IIf(["ErrorType"]=2,1,0), IIf(["ErrorType"]=3,1,0), IIf(["ErrorType"]=1,1,0)
HAVING (((Error_Entry.TM)=[Forms]![Search_TM_Records]![TM]) AND ((Error_Entry.DateError) Between [Forms]![Search_TM_Records]![StartDateEntry] And [Forms]![Search_TM_Records]![EndDateEntry]));

THANK YOU !

 

[CJ_London]

You have placed it in the right pace, but added quotation marks for some reason - I've removed them here

SELECT Error_Entry.TM, Error_Entry.Shift, Error_Entry.ErrorDept, Error_Entry.DateError, Error_Entry.DateDiscovery, Error_Entry.DateDiscussion, Error_Entry.AuditType, Error_Entry.ErrorCause, Error_Entry.Comments, Error_Entry.LPN, Error_Entry.Action, Error_Entry.FormReturn, Error_Entry.SUPV, Error_Entry.ErrorType, IIf[COLOR=red]([ErrorType][/COLOR]=2,1,0) AS CompTotal, IIf[COLOR=red]([ErrorType[/COLOR]]=3,1,0) AS QualTotal, IIf[COLOR=red]([ErrorType]=[/COLOR]1,1,0) AS AccTotal
FROM Error_Entry
GROUP BY Error_Entry.TM, Error_Entry.Shift, Error_Entry.ErrorDept, Error_Entry.DateError, Error_Entry.DateDiscovery, Error_Entry.DateDiscussion, Error_Entry.AuditType, Error_Entry.ErrorCause, Error_Entry.Comments, Error_Entry.LPN, Error_Entry.Action, Error_Entry.FormReturn, Error_Entry.SUPV, Error_Entry.ErrorType, IIf(["ErrorType"]=2,1,0), IIf(["ErrorType"]=3,1,0), IIf(["ErrorType"]=1,1,0)
HAVING (((Error_Entry.TM)=[Forms]![Search_TM_Records]![TM]) AND ((Error_Entry.DateError) Between [Forms]![Search_TM_Records]![StartDateEntry] And [Forms]![Search_TM_Records]![EndDateEntry]));

 

[Banderberg]

Access didn't like this and I kept getting error message that the expression was too complex, etc. And I tried everything...here's what finally worked:

IFF([ErrorType]="2", 1, 0)

Will that cause issue? parans around a number?

 

[CJ_London]

This means that your errortype field in your table is a text field and not numeric