Query / Append for Top N

[islu0102]

Good Morning,

I need to select the top 8 records from a large table using varying criteria. First, I would take all of the items that are, say "Red". If that didn't return 8 records, I would need to run an append query adding the difference from the other colors. How would i go about putting this together.

The first query is a simple "Top 8". It's the SQL for the append query that is getting me.

Thanks for any help you can give!

V

 

[pilsdumps]

You need to use a subquery to select the 8 records you wish to use and then use this a basis for running your update query by joining to it.

 

[islu0102]

Thank you for your response.

I understand your suggestion, but how do I get the subquery to only select enough records to reach 8 (if less than 8 were selected in the first query).

For example, if I need to compile a list of 8 items, of which some unknown number must be of one set of criteria and the remainder (if 8 is not reached from the first set) can have a much broader set of criteria, how do I structure the SQL to ask for 8 minus the count of the original set?

In other words, I'm looking for a list of 8 scores from a directory which must include everyone named Bob, and if there are not 8 Bobs, I can choose the remainder from the best scores by any other name. In the end, I must have total 8 names.

 

[DCrake]

Group by the field you are concerned about and sort descending then set it to top 8 so if there are five bobs, 2 fred and a sally it will say

Bob
Bob
Bob
Bob
Bob
fred
fred
sally

Then create a new query and group by the column

 

[islu0102]

Ok, but if there are 8 Alvins the query as stated will not return any Bobs at all, and the final result must return all of the Bobs, and be filled in, up to 8, with the rest.

 

[islu0102]

Maybe I'm not understanding the explanations (not a surprise, given my experience level! ). Here is a real-world example.

Let's say I wanted to compile a list of the top 10 pitchers from 1971 in the AL. I have chosen ERA as the only criteria (for the sake of simplicity). To make matters more complex, I am a (for this situation only) huge Detroit Tiger fan, so I am going to make sure that all of the Tiger pitchers in my spreadsheet of data make the list. Here is my SQL to create the first list (of just Tigers):

SELECT TOP 10 [71 Pitchers].Player, [71 Pitchers].Tm, [71 Pitchers].ERA INTO [Top 10 Pitchers]
FROM [71 Pitchers]
WHERE ((([71 Pitchers].Tm)="DET"))
ORDER BY [71 Pitchers].ERA;

Now, because I pulled the data from a random list of 100 pitchers from 1971, only 7 Tigers were returned. I'm trying to automate pulling this pitchers list (say, across all years of MLB, one year at a time). Since I will not know how many Tigers will show up each time I run it, I can't pre-set the next query to pull the remaining set, which in this case is 3 additional pitchers to round out to 10.

Is there a way to write an append query like so:

INSERT INTO [Top 10 Pitchers]
SELECT TOP 10 - (count([Top 10 Pitchers])
FROM [71 Pitchers]
ORDER BY [71 Pitchers].ERA;

which would add only the number of pitchers I need to fill out to 10?

BTW, I will need to run an append query, because I will have other queries that run between the original and the second (append).

Thank you, again.

 

[HGMonaro]

without actually trying it...

do your 1st query
count how many you got (open the table, check recordcount)
decide if you want more records (calculate 10-recordcount)
if yes, build your 2nd SQL statement using that variable as the 'TOP' value

 

[islu0102]

Thank you for your reply your reply, HGMonaro, but your response will not help me to automate the queries.

I know how to handle this situation manually. What I need to do is have a series of macros that run without my interaction. I am building a minor application that uses a series of macros with only one initiation. This series will pull data into sets of tables. For some of these sets, the queries will not select the full amount needed, but they will select the most important records (or, the records that must be in the set, regardless of how many). I will need another query to add to the original set of records for the remainder needed, under a broader set of criteria.

If I can't automate it, I may as well do the whole process manually.

 

[HGMonaro]

ohh, I don't mean to do it manually... that was just non-indented pseudo code for what I'd code. It may be possible to run as macros but creating code gives you much better control.

 

[islu0102]

Ah, well.

That's what i was afraid of! I'm not a coder at all. I suppose it's time to learn.

Thanks for your help.

 

[HGMonaro]

well to see if I could do what I suggested... (I've never used the TOP criteria before), here's a A97 db that does it using code. Have a look at the code in the Code module. The code is run using the macro 'Top 10 Pitchers'. To expand this into something useful, you'll need to take the plunge and learn VBA!

 

[islu0102]

Holy moly - that's exactly what I need the macro to do. I can't thank you enough!

Of course, you're right. I really do need to learn VBA. I use the program too much to not know how to use its most powerful features.

Thank you again!

 

[HGMonaro]

no probs!!!