Subquery help PLEASE! :P

[Accessor]

Hi all. I'm about to throw in the towel on this subquery. I've checked it and
rechecked it. OK, as an overview, I have two tables, one of names of
industries (automotive, energy, etc) named tblIndustries. I have another
table of stock market company symbols, called tblSymbolsMain. This table has
an industry type as a field for each stock symbol. I've written a function
(that works great) called MultiDayPerfClose that returns a performance in
terms of percentage of a stock. What I'd like to do is get an average of
performance returns for each industry. I'm using 5 days for now. So for each
industry, I need the average performance based on the performance of all of
the stocks of that industry. When I run the query below, I get prompted with
a message box asking for "tblIndusties.Industry". I just click ok (I don't
enter a parameter) and the query runs without error but the return is totally
blank. Here's what I've got for a query:

SELECT tblIndustries.Industry, (SELECT Avg(Q1.DayPerformance) FROM (SELECT
tblSymbolsMain.Symbol, MultiDayPerfClose(tblSymbolsMain.[Symbol],5) AS
DayPerformance FROM tblSymbolsMain WHERE tblSymbolsMain.Industry =
tblIndustries.Industry) AS Q1) AS DayPerf
FROM tblIndustries;

Any help out there? Thanks in advance...

Kevin

 

[Sylvain Lafontaine]

You don't have one subquery, you have two: the second subquery is imbricated
in the first one. I'm not sure if Access is capable of doing; this is why
it's asking the value for the parameter tblIndustries.Industry because it
doesn't recognize that this value should be taken from the second outer
query.

You should rewrite your query by using one or more Join.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[John Spencer]

You might try:
SELECT tblIndustries.Industry
, avgPerformance
FROM tblIndustries INNER JOIN
(SELECT Industry, Avg(Q1.DayPerformance) as AvgPerformance
FROM
(SELECT Industry,
, MultiDayPerfClose(tblSymbolsMain.[Symbol],5) AS DayPerformance
FROM tblSymbolsMain
) AS Q1) AS DayPerfd
ON TblIndustries.Industry = DayPerfd.Industry

Although you probably could just use
SELECT Industry
, (SELECT Avg(MultiDayPerfClose(tblSymbolsMain.[Symbol],5)) AS TheAvg
FROM tblSymbolsMain
WHERE tblSymbolsMain.Industry = tblIndustries.Industry) as TheAverage
FROM TblIndustries



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