Numbering records by group in a query (1 Viewer)

[JDB]

I have a database with numerous nutrient lab values per food item and zero to 20 tests per food item; some 600 food items

I want to select the last 5 tests per food item which should be no problem using the "TOP " type statement.

After I have the "TOP 5" record I would like to create another field to number each record automatically with in the query so I can run a cross tab query to display these records 1 thru 5.

Is that possible?

Thanks

JDB

 

[Brianwarnock]

I am pretty sure that TOP. Works on. He whole file not by group.
You need a ranking query which I think will solve both problems. There are two ways to write them the sub query being more efficient than using Dcount. Search the forum on rank and you will get many hits, I think there is also a demo DB .

Brian

 

[JDB]

Thanks, I will search for ranking type operations.

JDB