Access doesn't have a Median function (as you found). There are some workarounds. For example:
1. Sort Descending on the field whose median you want
2. Create a Rank calculation to generate the rank for each record
3. Filter the Rank calculation for the record with the middle ranking (as this is a sorted query, you will have the middle value in the field, ie the median).
The following SQL does the above, for a field called NetRevenue from tblProjectData:
Code:
SELECT tblProjectData.NetRevenue, (SELECT Count(*) FROM tblProjectData As P WHERE [NetRevenue] > [tblProjectData].[NetRevenue])+1 AS Rank
FROM tblProjectData
WHERE ((((SELECT Count(*) FROM tblProjectData As P WHERE [NetRevenue] > [tblProjectData].[NetRevenue])+1)=Int((SELECT COUNT(*) FROM tblProjectData)/2)))
ORDER BY tblProjectData.NetRevenue DESC;
Dump this into NotePad, replace the field and table names with your own, and place the result into a new query.
Note: If your field or table names have any spaces, use square brackets around the name -- eg,
YourTable is OK, as is [Your Table]. Your Table (without the brackets) will generate an error.
Denis
