SQL Not Working - "At Most One Record Can Be Returned by this Subq

  • Thread starter Thread starter ScottMsp
  • Start date Start date
S

ScottMsp

What is wrong with this SQL? I am trying to use a Median function and trying
to have the median calculate for each JobCode.

SELECT tEmployeeMasterCopy.JobCode, (SELECT
Median("tEmployeeMasterCopy","HourlyRate") FROM tEmployeeMasterCopy as M1
WHERE M1.JobCode = tEmployeeMasterCopy.JobCode) AS MedianByJobCode
FROM tEmployeeMasterCopy
GROUP BY tEmployeeMasterCopy.JobCode;

Thanks in advance.
 
It seems that

(SELECT
Median("tEmployeeMasterCopy","HourlyRate")
FROM tEmployeeMasterCopy as M1
WHERE M1.JobCode = tEmployeeMasterCopy.JobCode)


returns more than one record. Is there only ONE record per JobCode?



Vanderghast, Access MVP
 
Vanderghast,

There is more than one record per job code.

20,000 records and approximately 1,800 different job codes.

What am I doing wrong?

Thanks in advance.
 
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
 
Try this modification. A sub-query used in the select clause must return only
one value and one record. Your sub-query has in theory the potential to
return multiple records. Use First or one of the other aggregate SQL
operators (Max, Min, Avg, or Last) to limit the rows returned.

SELECT tEmployeeMasterCopy.JobCode
, (SELECT First(Median("tEmployeeMasterCopy","HourlyRate"))
FROM tEmployeeMasterCopy as M1
WHERE M1.JobCode = tEmployeeMasterCopy.JobCode) AS MedianByJobCode
FROM tEmployeeMasterCopy

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top