Top N query question

[LAgnor]

I am trying to write queries that pull the top 25 and bottom 25 records for each of four regions. This can be easily done using a Top N query. My problem is that when there are fewer than 50 records in a region my boss wants to see them grouped together (eg Top 47), and the bottom 25 query would then be ignored. Is there a way to specify "N" based on the number of records?

 

[jdraw]

?? Not sure I understand the issue.
If you count the records in the file/table/set, you'll know upfront what Top N and Bottom N
make sense.
If you only have 25 records, top 30 doesn't make much sense.

You could use percent %.

 

[LAgnor]

To clarify:
I may have 47 records or I may have 357 from each region. The purpose of the queries is to automate the procedure so clerical staff can run it and won't have to provide a value for N.

 

[namliam]

you have to make some VBA to determine what query to run...

Alternatively you have to make the one query exclude records from one query to the other.... I can understand you have 47 record with top 25 and bottom 25 you will have 3 overlap so you dont want those repord 2 times.

 

[LAgnor]

Is there a way to specify the value of "N"? For example, if a prior query finds all the records in one region and there are 47 records. Could that 47 be automatically input as "N" for the following query?

 

[namliam]

No not really, that is... if you have a top 25 that you run first time....
THen on the bottom 25 you "outer join" the first to make sure you dont have any dups in there....

You might even be able to do a union query...
Select ...
union
Select ...

That should also exclude any dups for you, though a bit slower.

 

[WayneRyan]

LAgnor,

You could also --> Select Top 10 Percent ...

Wayne