Access question
Author
Discussion

Jonboy_t

Original Poster:

5,038 posts

214 months

Monday 17th September 2018
quotequote all
Probably an easy one for those of you with a bit of common sense!

I have a table of about 500,000 entries that correspond to around 300 different stores, so there could be 10,000 entries for store 1, 150 for store 2 and so on.

What I'm trying to do is to only show the top 50 entries for each store. I've run a query to sort it by store code and then by another field (value, in this case), so the next step, and the bit I'm stuck on, is to add in a column that starts with '1' on the first entry for that store code, '2' for the second and so on, all the way up to the last entry for that store code. Once I've got that, it'll then be just a simple query to only show numbers between '1' and '50' and I'll have my final record, but I just can't figure out a way of adding in this 'ranking' column.

Is there a Function to do this somewhere? I'm trying to avoid getting into script or coding on it because I'm an idiot and don't know how to do that!

Stupeo

1,343 posts

224 months

Monday 17th September 2018
quotequote all
A subquery should work for this.

Have a look at the "Top N records per group" at http://allenbrowne.com/subquery-01.html


Jonboy_t

Original Poster:

5,038 posts

214 months

Monday 17th September 2018
quotequote all
Stupeo said:
A subquery should work for this.

Have a look at the "Top N records per group" at http://allenbrowne.com/subquery-01.html
Cheers bud, will have a look tomorrow thumbup

gothatway

7,756 posts

201 months

Monday 17th September 2018
quotequote all
If you want to construct the query using query designer rather than working out the SQL for yourself, you might also want to look at >FMS Inc<

Jonboy_t

Original Poster:

5,038 posts

214 months

Tuesday 18th September 2018
quotequote all
Cheers for the help chaps thumbup

I've taken a different tack with it. Wrote a macro in Excel to sort the source data by store code and then value, then pop a new column A in (so the store code is in col B). It then does an =If formula that says "if the store code on this line is the same as the line above it, add 1 to the number in above this one, if not, put '1' here". I've then just done a code in Access to call that Macro to run and will import that data once the macro has run in Excel.

Probably not the best way to do it or most reliable, but it's only me that'll be using it so I can only blame myself if it goes tits up biggrin Plus, I'm pretty competent in writing in Excel, but when it comes to Access I am utterly clueless!

Thanks again