Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Banding of salaries (2000)

    I have a Salary field with the different salaries for staff members. I want to group them into 'bands' 0-10,000 10,001-20,000, etc., so that I can then count how many occur in each band and the maximum within each band. Do I need to create a separate table with the band limits (0,10000,20000,etc.) or can I achieve this in a Totals Query using Integer division by 10,000? Thanks, Andy.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Banding of salaries (2000)

    One possibility is the little-known Partition function: Partition([Salary], 1, 100000, 10000) will return a string with the salary band in the form 20001: 30000. The general form is Partition(value, start, stop, interval)

    - value: the value you want to assign to a band.
    - start: the first number in the first band.
    - stop: the last number in the last band.
    - interval: the size of the bands.

    Another possibility would be to use ([Salary] - 0.01) 10000. This will return 0 for salaries up to 10,000.00, 1 for salaries from 10,000.01 up to 20,000.00 etc. You could use a lookup table with descriptions of these values.

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Banding of salaries (2000)

    I've built a Totals Query using the Expression Int([Salary]/10000) to put each salary into a Band. I've then built a second Totals Query based on this one to Group By and then Count this new field. This tells me how many salaries occur within each band. How can I modify this to also find, for example, the Average salary within each band? Is it also possible to do this without creating two separate Queries? Andy.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Banding of salaries (2000)

    Just add Salary to your Totals query and set the Total option to Avg.

  5. #5
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Banding of salaries (2000)

    I've added the Salary field to the first query, and then generated the Average for this field in the second query. However, this is not the correct average?! Because some staff have the same salary, this figure is only represented once.. so that the average that is calculated ignores the duplicated values. I hope this makes sense? Andy.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Banding of salaries (2000)

    Put Salary in the first query, and average it.

  7. #7
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Banding of salaries (2000)

    Doing that yields the following error message "You tried to execute a query that does not include the specified expression 'Int([Salary]/10000)' as part of an aggregate function".

  8. #8
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Banding of salaries (2000)

    I'm still not convinced it's correct. Instead of the Average I've added Count. So I've got, for each salary, the Band it occurs in the Salary figure itself and how many times it repeats. A second query built on this Counts how many salaries occur within each Band. I've added to this the Expression Avg([Salary]*[CountOfSalary]). Would this generate the correct average??

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Banding of salaries (2000)

    Unless you're interested in how often each individual salary occurs, that count isn't very useful. A query to return the number of employees and the average salary in each band would look like this:

    SELECT Int([Salary]/10000) AS [Band], Avg([Salary]) AS [Average Salary], Count([Salary]) AS [Number of Employees]
    FROM tblWhatever
    GROUP BY Int([Salary]/10000);

  10. #10
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Banding of salaries (2000)

    Top marks.. and all in one query. Thanks, Andy.

Posting Permissions

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