Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2008

    Unanswered: How can I achieve this on an Access table?

    I have a problem and don't know how to solve it. Hope you can give me some advice:

    I have an Access table like the one below.(apologies for the distorted table format but I don't know how to fix it)

    I want to choose three and only three staffers from each department according to their salary, years of experience and appraisal rating in descending order. That means I will pick the ones with the highest salary first. If salary isn't a differentiating criterion, I go to pick the most experienced ones, and if it still doesnt differentiate the staff, I go to pick the ones with better appraisal ratings.

    For example, in the following table, for dept 1, I will select Staff D (highest salary), C (A,B and C have equal salary but C is more experienced than B and A) and B (salary and experience equal to those of A, but better appraisal than A).

    The output should be a table with 3 staff records from each department.

    Now my question is how can I achieve this? Can I just do it with SQL?

    Dept Staff Salary Experience Appraisal rating(Higher the better)
    1 A 50000 4 2
    1 B 50000 4 3
    1 C 50000 5 5
    1 D 60000 3 4
    1 E 10000 1 2
    2 F ...
    2 G ...
    2 H ...
    2 I ...
    3 ... ...
    3 ...
    3 ...
    Last edited by kennyming; 07-14-08 at 23:46.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    what do you want to see if there are 4 staffers in a department with the same top salary, experience, and appraisal?

    i can do this with sql easily if you allow ties (and i would recommend that you do)

    otherwise, you'll cut someone off who should really be included | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2008
    Actually, I want to cut one in case there's a tie. I may keep the one with a smaller staff ID.

    Can you tell me how to do it with SQL?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts