top n of multiple fields & rows

[MHPGuy]

This is a bit confusing, so please bear with me.

I need to get the top 5 scores from an array of 35 scores in a table... I have an access database with a table called "qualityheader" that has 36 different fields in it to record a score for each of the 36 indicators. Some scores are 0 or 1, while others can be a 0-5 score. Each row represents one phone call. I'm trying to pull a list for each rep that has the top 5 scoring items over a period of a month.

So what I have now, basically, is a report with a GH1 of call center, GH2 of phone rep, details for each call. in GF2 I need to put a top 5 list of the issues they consistently scored well in.

The problem is that normally I'd say show me a top n of all scores for field x. This demands that I ask for a top n of all scores in fields 1 to 36.

Does this make sense? I'd appreciate any help anyone can give.

Michael Phipps
Technical Business Analyst
Mercy Health Plans

 

[synapsevampire]

There are a few approaches, probably the most efficient being to create this in Access and then expose the query to Crystal.

Another cheat that will allow Crystal to treat it as a more normalized data set would be to create a Union Query in Access to break the scores out into different rows:

select 'Score 1' ScoreType, field1, EmpID from table
union all
select 'Score 2' ScoreType, field2, EmpID from table
union all
select 'Score 3' ScoreType, field3, EmpID from table
etc...

This will provide a table layout that lends itself to TOP N in Crystal.

-k

 

[MHPGuy]

yep. I'm lost.

Can you help me out with some more details? It sounds like you are taking every score and making it a 1 column row for all users. Is that correct?

I think this may be way over my head, but I'm interested in your idea.

Michael Phipps
Technical Business Analyst
Mercy Health Plans

 

[synapsevampire]

Open a Query in Access and type in the fields required for th first score columna, and use an identifier for each row as I did ('Score 1' ScoreType), and then type Union All, then do score 2, etc.

I can't elaborate on this, I don't have your table structures.

It's much easier than you think, open Access and create a new query in the database.

-k