Compiling a report from multiple queries 1

[Ration]

Hello,

I am a new Access 2002 user trying to compile a report from multiple queries. I do Quality Assurance work in a call center. I need a report that counts the number of times a CSR scores a zero. There are 18 points of criteria that a CSR can score a zero on. The criteria are:

Call Opening
Hold
Transfer
Call Doc.
Using Customer Name
Call Close
Understandable Communications
Listening Basics
Pos. Response
Prof. Communications
Prof. Behaviors
Call Mgmt.
Appropriate Escalation
Issue ID
Issue Resolution
S.O.P.
Utilized System Resources

I have worked up a query for each of the above criteria that counts the number of zeros (by CSR & Customer ID where the 0 was scored) scored in each criteria.

The queries return the follwing:
Date Manager Name Customer Number Agent Name Call Open Misses
11/18/2005 UM 1 ********05294 Jam Jelly 1
12/8/2005 UM 1 ********43523 Jam Jelly 1
12/14/2005 UM 1 ********35577 Jam Jelly 1
12/28/2005 UM 1 ********04784 Jam Jelly 1
1/5/2006 UM 1 ********59924 Jam Jelly 1
1/20/2006 UM 1 ********5471 Jam Jelly 1
1/20/2006 UM 1 ********00937 Jam Jelly 1
1/20/2006 UM 1 ********35 Jam Jelly 1

Ok, now I have 18 queries like this one and I have to generate 1 report. I have tried linking all 18 criteria into one query but the query results are inaccurate as it will only count the customer numbers where a zero was scored in all 18 criteria.

Any help on streamlining this would be greatly appreciated!

Thanks,

Ration

 

[dhookom]

Would you mind sharing your table structures? Are your 18 points each a field or have you properly normalized them to be records?

I have a feeling you have committed "spreadsheet" but I am not sure. I think if your tables were properly structured you wouldn't have an issue creating your report/query.

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]

 

[Ration]

The tables are setup as follows:

CSR Listing Table - Has the CSR#, QA Auditor#, Mgr#, first name, last name, date of hire and a check box to signify if the csr is active or not.

Mgr Table - Has mgr id#, first name, last name and team name.

Call Monitor Table - The call monitoring form populates this table. The columns are: Monitor ID#, CSR ID#, Date Monitored, Date of call, Customer#, caller's name, Name of the customer's business, reason for call, the score for each of the 18 criteria (each has it's own column), call summary (text), positive notes (text), opportunities for improvement (text), Suggested Training Opportunities (from drop-down list), Failed Call? (check box) and Possible Incentive Call? (check box).

If the CSR scores a 0 in all of the 18 criteria, the Failed Call checkbox is checked and the information from the call monitoring form populates the Failed Call Table and the Call Monitor Table.

That's pretty much it. If it helps, I can copy and paste a few sample entries from any of the above tables.

Thanks a bunch for the help!

 

[dhookom]

You should normalize if at all possible. Assuming [Call Monitor] has a primary key of [Monitor ID], you should have a child table with one record per "monitor" per criteria. Consider reviewing how this table structure can be created by looking at "At Your Survey" found at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

If you can't normalize your structure you might be able to create a union query based on your [Call Monitor] table that looks like a normalized table.

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]

 

[Ration]

Hey,

I just wanted to pop-in and let everyone know that I was able to resolve the issue by using IIf. The following command allowed me to count the zeros:

Count A: Sum(IIf([Field 1]=0,1,0))

Hope this helps others!