Group and THEN select Top records

Hi,

is it possible to Group and THEN select the top records from a table ?


I got a tbl like this:

Group....Month....Prt-No....Qty
a..............Jan.......a1..........1
a..............Jan.......a1..........2
a..............Jan.......a1..........3
a..............Jan.......a1..........4
a..............Feb.......a1..........1
a..............Feb.......a1..........2
a..............Feb.......a1..........3
a..............Feb.......a1..........5
a..............Jan.......a2..........1
a..............Jan.......a2..........2
a..............Jan.......a2..........34
a..............Jan.......a2..........4
a..............Feb.......a2..........4
a..............Feb.......a2..........3
a..............Feb.......a2..........2
a..............Feb.......a2..........1
b..............Jan.......b1..........1
b..............Jan.......b1..........2
b..............Jan.......b1..........3
b..............Jan.......b1..........4


I'd like to get the top 3 records for every Group & Month.

The desired result would be:

a..............Jan.......a1..........2
a..............Jan.......a1..........3
a..............Jan.......a1..........4
a..............Feb.......a1..........2
a..............Feb.......a1..........3
a..............Feb.......a1..........5
a..............Jan.......a2..........2
a..............Jan.......a2..........34
a..............Jan.......a2..........4
a..............Feb.......a2..........4
a..............Feb.......a2..........3
a..............Feb.......a2..........2
b..............Jan.......b1..........2
b..............Jan.......b1..........3
b..............Jan.......b1..........4


The number of records per Group/Month combination may differ.

I wasn't able to do this via TOP-function or Group-funktion. I either got a error message or the querry simply selected the top3 records (regardles the month or group).

Is there a way to do this "newbie-style" ?
(i.e. in the design view / without vba,...)

TIA,
Barbarossa II

Create a totals query that groups the data as you wish. Then create a top values query that uses the totals query as its source

Click to expand...

I don't think we can pull the original records from a totals query.

hmmm,

my problem is that I'd like to draw the top 3 records with the highest quantities form each "group" (here group / Month / partNo combination).

As far as I understand your solution, I should built a query which simply groups and orders the data as I wish and take this query as the base for another query in which I use the "Top 3" 'function'.

By doing this, I get 3 records as result (the Top3), but I'd like to get the top 3 for e v e r y group/month/part-No combination: i.e.

top3 for group 'a' in Jan for partNo 'a1' ... plus
top3 for group 'a' in Feb for partNo 'a1' ... plus
top3 for group 'a' in Jan for partNo 'a2' ... plus
top3 for group 'a' in Feb for partNo 'a2' ... plus
top3 for group 'b' in Jan for partNo 'a1' ... plus
top3 for group 'b' in Feb for partNo 'a1'...


Thanks in Advance for your help,
Barbarossa II

I have attached a database using your sample data. The database was saved from Access 2000.

It contains two queries. Both queries use a subquery and a table alias. You will find it easier to type the SQL Statements in query SQL View than build the queries in Design View.

The first query returns the query results in no particular order.

The second query illustrates how you can use a lookup table to obtain ordered results involving a Month field in text i.e. Jan Feb Mar Apr etc.

Note. Running a subquery takes time if the table is fairly large (for a large table, VBA will be much faster).  When there is a tie in a group, more than three records will be returned.

Thanx EMP,

exactly what I need !!!


Barbarossa II