Access question
Discussion
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!
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!
A subquery should work for this.
Have a look at the "Top N records per group" at http://allenbrowne.com/subquery-01.html
Have a look at the "Top N records per group" at http://allenbrowne.com/subquery-01.html
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 Have a look at the "Top N records per group" at http://allenbrowne.com/subquery-01.html
Cheers for the help chaps 
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
Plus, I'm pretty competent in writing in Excel, but when it comes to Access I am utterly clueless!
Thanks again
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
Plus, I'm pretty competent in writing in Excel, but when it comes to Access I am utterly clueless! Thanks again
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff


