Illustrate Data that Meets Specific Criteria and its Perc of Totals 1

[pamelarj]

I have to create a report (MS Access 2007) that illustrates the number of applications that failed a particular process. The datasource for the report is a query that lists all applications that passed and failed. Lets say that there were 1000 total: 25 failed and 975 passed. The report should list the 25 that failed and illustrate that the the 25 that failed is 2.5% of the total 1000.

Currently there's 3 Groups: Region, County and Marketing Rep.

The problem that I'm having is that the report is illustrating both passed and failed apps and I don't want the passed apps (as the report title is 'Applications that Failed the Pre-Scan Process'. Any suggestions?

 

[dhookom]

It sounds like all you need to have is the failed details with the total number processed. You could create a single record query that counts the total number processed. Add this single record query to your report's record source which is filtered to only those that fail. You can then count the failures and divide by the total number processed.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[pamelarj]

First I'm making a couple of assumptions: When you refer to the report's record source you're referring to the 'Record Source' property, on the 'Data' tab and (2) When you say 'filter on those who failed' you're suggesting I place something like the following in the Report's 'Filter' property also located on the 'Data' tab?

 [Status] = "Failed"

Let's say I have the following data:

Record Region    County   AppID   Rep      Status   Date
1      CRO       cty1     abc     pamela   Failed   5/20
2      CRO       cty1     def     pamela   Failed   5/20
3      CRO       cty1     ghi     pamela   Passed   5/20
4      CRO       cty1     jkl     pamela   Passed   5/20
5      CRO       cty1     mno     pamela   Passed   5/20
6      CRO       cty1     pqr     pamela   Passed   5/20
7      CRO       cty1     stu     pamela   Hold     5/20

If, based on your suggestion, my record source query only has one row returning a count of the 6 rows that Passed or Failed; wouldn't it need to be a summary query with only one field (Count([AppID])? If so, where would the report get the remaining fields that it needs for its groups (Region, County and Rep)? (As if I add those additional fields to the query, grouping by them in the query, it will return more than one row. But if I don't and the one-row query is the reports' record source, where will the report get the additional info from?)

 

[dhookom]

You could create a single record query that counts the total number processed

Query qtotNumberProcessed
[tt][blue]
SELECT Count(*) as NumberProcessed
FROM tblNoName
[/blue][/tt]
Then use a record source for your report like:
[tt][blue]
SELECT tblNoName.*, NumberProcessed
FROM tblNoName, qtotNumberProcessed
WHERE [Status] ="Failed";
[/blue][/tt]
This report will show all the failed records but has a field that you can use as the count of all records.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[pamelarj]

dhookom,

I guess I'm being thick because you're saying 'use a recordsource'. Where do I use the recordsource? In the recordsource property of the report?

 

[dhookom]

RecordSource and Record Source refer to the Record Source property of your report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[pamelarj]

Thanks dhookom,

I have to tweak it a bit to apply to my situation; however, I get it now.