Can a report list high AND low results?

[Timberwolf]

I've been asked to create a report that will return some information regarding hours worked on a project. This particular dB tracks writers' hours spent on each file in the project. (There is one field for total hours spent on a file, and only one writer works on each particular file.) We need a report to (1) list all the writers (this I can do), and (2) the average number of hours each writer spends working on a file (this I can also do), and (3) list the highest and lowest hours worked on file (which file took the most and which file took the least amount of time).

Is there a way to accomplish #3? If so, how? Thanks so much.

Ann

 

[Fornatian]

Ann,

I would think the way to do it would be to use a union query to pull together two queries.

Query 1:
Selects the top X hours worked

Query 2:
Selects the bottom X hours worked

Query 3:
Unions Queries 1 & 2 together.

The easy way to do this is to build queries 1 & 2 using the QBE grid then change to SQL view in query 2 and copy the SQL text. Then open query 1 and remove the ; from the end of the statement. Type Union on the next line down and paste the sql text from query 2 there.

I believe that should do it.

Ian

 

[Timberwolf]

Thanks, Ian. But let me clarify ... I need the highest and lowest values for each writer. Can it still be done?

Again, thanks!

 

[Timberwolf]

Just bumping this up.

 

[Fornatian]

to be honest that is a bit deep for me but i think you would need to use a 'group by' total query for 1 & 2 above selecting min and max in the relative queries to select the lowest time and the highest times respectively for each writer.

these queries could then be used in a third query if you made joins to your writers to put the min and max values side by side.

i must point out that these ideas are only speculative so they may not work.

i think you need someone with more totals query expertise.

Ian