Since the expression returns a 'table', the outermost statement is illegal.
Remember that a field can contain only ONE value (one row, one column), or
what we can call 'a scalar', but your actual result want to look like:
JobCode MedianByJobCode ' fields
------------ -----------------------------------
Code1 ??? --- need a table here --- ???
Code2 ??? --- need a table here --- ???
.... ...
and you 'need a table here' since the (SELECT ... ) returns... many rows
(one column), which is NOT a scalar, not something a field can contain.
Sure, all the values (in the many rows) are all the same, right? ***IF***
So, you can pick up just ONE of them.
Instead of:
(SELECT Median("tEmployeeMasterCopy","HourlyRate")
FROM tEmployeeMasterCopy as M1
WHERE M1.JobCode = tEmployeeMasterCopy.JobCode)
you can use:
(SELECT FIRST(Median("tEmployeeMasterCopy","HourlyRate"))
FROM tEmployeeMasterCopy as M1
WHERE M1.JobCode = tEmployeeMasterCopy.JobCode)
or MIN, or MAX, or LAST, even AVG if you want, in this case (if my
assumption is right).
That would work, but will be excessively slow. Further more, even if you use
just one row from your (SELECT ... ) , your program is still computing ALL
these rows, over and over, before eliminating them all, but one. That is a
hint that something very inefficient occurs.
Indeed, maybe your Median procedure should accept a third argument, the
JobCode, and your query would become:
SELECT JobCode,
Median("tEmployeeMasterCopy","HourlyRate", "JobCode=" & JobCode)
AS MedianByJobCode
FROM tEmployeeMasterCopy
GROUP BY JobCode;
Note that if JobCode is ALPHA numerical, rather than being numerical, the
syntax is likely to be:
Median("tEmployeeMasterCopy","HourlyRate", "JobCode=""" & JobCode &
"""" )
Assuming your function Median will see the third argument as DLookup, or
DCount, or DSum, DMax, ... would see it.
Vanderghast, Access MVP
