Query n records per value (1 Viewer)

[steveheath]

I have a table with many records per account manager. I have another table with the account managers name and a value. I need to get 'n' records back where 'n' is the value.
Without knowing much about coding can this be done in a query via the query builder or sql view. I have tried Select Top

![field] but it does not like this.

thnaks

 

[David R]

Short answer: probably.
Long Answer: I don't quite understand your question. You want to get the top N values back for each manager (like top employee performance appraisals or something similar?), or for the company as a whole you want to know which managers have the top N <somethings>?

A little more data will help, but I think you can do what you're asking. Please post back with more information.

David R

 

[steveheath]

hi, re: The access problem.
I have one table that has a persons name in it for many records. + some other fields.
I then have another table with that persons name in and a value . say 100

What i want to do is somehow get 100 records from the first table for that person. ie. the second table only contains 1 entry per person.


hence

table 1 table 2
smith ..... smith 2
smith .....
smith .....

output (from table 1)

smith.....
smith.....


basically i have a whole file of data allocated to people. This month i only want so many records per person so i created a table with that persons name in and another field with the number of records i want for that month hence smith might have 300 records altogether but i want 50.

I have tried the TOP 50 value which works fine, but i need an automatic way to do this as there are over 100 perople with different volumes required.(hence making a table of name and number required).

Is there a way to read the value field from table 2 to make the TOP number i need??


Thanks in anticipation.
Regards
Steve

 

[David R]

http://www.access-programmers.co.uk/ubb/Forum3/HTML/000528.html may get you along the lines that you need to.