Showing Median Salaries From Access Table

# Thread: Showing Median Salaries From Access Table

1. Senior Member
Join Date
Dec 1969
Posts
276

## Showing Median Salaries From Access Table

I have an Access table and I would like to run an SQL to show median salaries by Team. I have not found a good way to do so and am looking for advice on if it can be done. I have a table called main and here are the fields:<BR><BR>Name(has player names), Salary(each person&#039;s salary), Team(each team liste)<BR><BR>I want to try to do a listing of median salaries by Team. Is there a way to do this via SQL or is it just to complex?

2. Senior Member
Join Date
Dec 1969
Posts
6,476

## RE: Showing Median Salaries From Access Table

Not sure what you mean exactly?<BR><BR>Show 1 team, then list all names and their saleries...<BR>Then move on to next team etc...<BR><BR>Or just show a sum of all saleries for each team without the names?<BR>

3. Senior Member
Join Date
Dec 1969
Posts
276

## RE: Showing Median Salaries From Access Table

The median salary is the middle value:<BR><BR>i.e. if there are 5 salaries and they are \$50,000, \$70,000, \$90,000, \$92,000, \$100,000<BR><BR>then the median salary is \$90,000 but in my case I am looking at 50 to 60 numbers for each team.

4. Senior Member
Join Date
Dec 1969
Posts
6,476

## RE: Showing Median Salaries From Access Table

SELECT Team, Avg(Salery) AS AvgOfSalery<BR>FROM YourTableNameHere<BR>GROUP BY Team<BR><BR><BR><BR>The fieldname you reference in asp is now<BR>rs("AvgOfSalery")

5. Senior Member
Join Date
Dec 1969
Posts
276

## RE: Showing Median Salaries From Access Table

But that is the Average of Salary not the median. <BR><BR>The median is the middle of all the values not the average. What you have is good for average but not for median value.

6. Senior Member
Join Date
Dec 1969
Posts
6,476

## RE: Showing Median Salaries From Access Table

opps sorry my bad... didnt pickup on that...<BR><BR>So IF highest salery is 100,000<BR>and lowest salery in 50,000<BR><BR>Then the value you need calculated is 75,000 ?<BR>Not the salery closest to 75,000 Right?

7. Senior Member
Join Date
Dec 1969
Posts
276

## RE: Showing Median Salaries From Access Table

Yes you are right!!

8. Senior Member
Join Date
Dec 1969
Posts
6,476

## RE: Showing Median Salaries From Access Table

Would be slow from asp...<BR>Faster inside access....<BR><BR>Do you have access permissions to work inside the database itself??<BR><BR>because you can save a couple queries inside access...<BR><BR>They your asl sql query can call those queries instead of the table of data....<BR><BR>Are you able you make and save queries directly inside the database?<BR>

9. Senior Member
Join Date
Dec 1969
Posts
276

## RE: Showing Median Salaries From Access Table

Yes. I can do that to the database. No problem there.

10. Senior Member
Join Date
Dec 1969
Posts
6,476

## RE: Showing Median Salaries From Access Table

Only if needed....<BR>I was thinking of a different way....<BR><BR>Try this query from ASP, and if it is fast enough then stick with it....<BR>If not let me know and ill show you what to do....<BR><BR>NOTE: Replace TableName with your real tables name...<BR><BR><BR>SELECT Team, Max(Salery) AS MaxOfSalery, Min(Salery) AS MinOfSalery,<BR>((Max(Salery) - Min(Salery) ) / 2) AS MedianSalery<BR>FROM TableName<BR>GROUP BY Team<BR><BR><BR>Then writing to page<BR>Response.Write "TeamName = " & rs("Team") & "&#060;/br&#062;"<BR>Response.Write "Max Salery = " & rs("MaxOfSalery") & "&#060;/br&#062;"<BR>Response.Write "Min Salery = " & rs("MinOfSalery") & "&#060;/br&#062;"<BR>Response.Write "Median Salery = " & rs("MedianSalery") & "&#060;/br&#062;"<BR><BR><BR>That should work...<BR>Let me know.

#### Posting Permissions

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