Subery help please...

A

Accessor

Hello all. I'm struggling a bit with a query I'm trying to write. It's based
on two tables with a one to many relationship. The "one" side is a master
list of stock symbols. The many table is daily price and trading volume for
each stock. I'm trying to add several fields, starting with the symbol. I
then want to have fields get data from different dates (latest data point,
the day before, etc). I've pasted below what I've got so far. This query runs
but the last field (the subquery) is blank when executed. Any ideas? Maybe
the join? Please help!! :) Thanks in advance...

Kevin

SELECT DISTINCT tblSymbolsMain.Symbol, tblSymbolsMain.Company,
tblStocksPricingVol.PricingVolWebQueryDate AS LatestDate,
CDate(Workday([LatestDate],-1)) AS DatePriorToLatest,

(SELECT M2.LowPrice FROM tblStocksPricingVol AS M2 WHERE M2.Symbol = Symbol
AND M2.PricingVolWebQueryDate = DatePriorToLatest) AS DatePriorToLatestLowPr

FROM tblSymbolsMain INNER JOIN tblStocksPricingVol ON tblSymbolsMain.Symbol
= tblStocksPricingVol.Symbol
WHERE
(((tblStocksPricingVol.PricingVolWebQueryDate)=DMax("PricingVolWebQueryDate","tblStocksPricingVol","Symbol='" & [tblStocksPricingVol].[Symbol] & "'")));
 
J

John Spencer

Try specifying the tables in the subquery. If you don't the subquery
will use the M2 fields. DatePriorToLatest will probably need to
recapitulate the entire calculation.

(SELECT M2.LowPrice FROM tblStocksPricingVol AS M2
WHERE M2.Symbol = tblStocksPricingVol.Symbol
AND M2.PricingVolWebQueryDate =
CDate(Workday([tblStocksPricingVol.PricingVolWebQueryDate],-1)))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
A

Accessor

Hi John, thanks so much. I've tried that though. I'm newbie and a bit hung up
Try specifying the tables in the subquery. If you don't the subquery
will use the M2 fields. DatePriorToLatest will probably need to
recapitulate the entire calculation.

(SELECT M2.LowPrice FROM tblStocksPricingVol AS M2
WHERE M2.Symbol = tblStocksPricingVol.Symbol
AND M2.PricingVolWebQueryDate =
CDate(Workday([tblStocksPricingVol.PricingVolWebQueryDate],-1)))

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

Hello all. I'm struggling a bit with a query I'm trying to write. It's based
on two tables with a one to many relationship. The "one" side is a master
list of stock symbols. The many table is daily price and trading volume for
each stock. I'm trying to add several fields, starting with the symbol. I
then want to have fields get data from different dates (latest data point,
the day before, etc). I've pasted below what I've got so far. This query runs
but the last field (the subquery) is blank when executed. Any ideas? Maybe
the join? Please help!! :) Thanks in advance...

Kevin

SELECT DISTINCT tblSymbolsMain.Symbol, tblSymbolsMain.Company,
tblStocksPricingVol.PricingVolWebQueryDate AS LatestDate,
CDate(Workday([LatestDate],-1)) AS DatePriorToLatest,

(SELECT M2.LowPrice FROM tblStocksPricingVol AS M2 WHERE M2.Symbol = Symbol
AND M2.PricingVolWebQueryDate = DatePriorToLatest) AS DatePriorToLatestLowPr

FROM tblSymbolsMain INNER JOIN tblStocksPricingVol ON tblSymbolsMain.Symbol
= tblStocksPricingVol.Symbol
WHERE
(((tblStocksPricingVol.PricingVolWebQueryDate)=DMax("PricingVolWebQueryDate","tblStocksPricingVol","Symbol='" & [tblStocksPricingVol].[Symbol] & "'")));
Click to expand...
Click to expand...
 
J

John Spencer

Sorry, I had a bracketing error in what I suggested.

(SELECT M2.LowPrice FROM tblStocksPricingVol AS M2
WHERE M2.Symbol = tblStocksPricingVol.Symbol
AND M2.PricingVolWebQueryDate =
CDate(Workday([tblStocksPricingVol].[PricingVolWebQueryDate],-1)))

Of course since you really don't need the brackets with properly formed names,
the following should work unless I've managed to add some other error (like
unbalanced parentheses).


(SELECT M2.LowPrice FROM tblStocksPricingVol AS M2
WHERE M2.Symbol = tblStocksPricingVol.Symbol
AND M2.PricingVolWebQueryDate =
CDate(Workday(tblStocksPricingVol.PricingVolWebQueryDate,-1)))


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

John said:
Try specifying the tables in the subquery. If you don't the subquery
will use the M2 fields. DatePriorToLatest will probably need to
recapitulate the entire calculation.

(SELECT M2.LowPrice FROM tblStocksPricingVol AS M2
WHERE M2.Symbol = tblStocksPricingVol.Symbol
AND M2.PricingVolWebQueryDate =
CDate(Workday([tblStocksPricingVol.PricingVolWebQueryDate],-1)))

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

Hello all. I'm struggling a bit with a query I'm trying to write. It's
based on two tables with a one to many relationship. The "one" side is
a master list of stock symbols. The many table is daily price and
trading volume for each stock. I'm trying to add several fields,
starting with the symbol. I then want to have fields get data from
different dates (latest data point, the day before, etc). I've pasted
below what I've got so far. This query runs but the last field (the
subquery) is blank when executed. Any ideas? Maybe the join? Please
help!! :) Thanks in advance...

Kevin

SELECT DISTINCT tblSymbolsMain.Symbol, tblSymbolsMain.Company,
tblStocksPricingVol.PricingVolWebQueryDate AS LatestDate,
CDate(Workday([LatestDate],-1)) AS DatePriorToLatest,
(SELECT M2.LowPrice FROM tblStocksPricingVol AS M2 WHERE M2.Symbol =
Symbol AND M2.PricingVolWebQueryDate = DatePriorToLatest) AS
DatePriorToLatestLowPr

FROM tblSymbolsMain INNER JOIN tblStocksPricingVol ON
tblSymbolsMain.Symbol = tblStocksPricingVol.Symbol
WHERE
(((tblStocksPricingVol.PricingVolWebQueryDate)=DMax("PricingVolWebQueryDate","tblStocksPricingVol","Symbol='"
& [tblStocksPricingVol].[Symbol] & "'")));
Click to expand...
Click to expand...
 
A

Accessor

Hi John, thanks for following up. I've taken your suggestion and started
over. You got me through the first subquery, but the next field gives the
error "the expression is typed incorrectly, or it is too complex to be
evaluated." The SQL is below with the offending subquery in the middle. This
is frustrating... I've seen articles mentioning this error arising with date
value fields? but can't find solutions or workarounds. Thanks for working
with me, here's what I've got...

SELECT tblStocksPricingVol.Symbol,
Count(tblStocksPricingVol.PricingVolWebQueryDate) AS CountOf,
(SELECT Max(PricingVolWebQueryDate) FROM tblStocksPricingVol AS M2 WHERE
M2.Symbol = tblStocksPricingVol.Symbol) AS LatestDate,

(SELECT M2.PricingVolWebQueryDate FROM tblStocksPricingVol AS M2 WHERE
M2.Symbol = tblStocksPricingVol.Symbol AND M2.PricingVolWebQueryDate =
Workday(tblStocksPricingVol.LatestDate,-1)) AS PriorDate

FROM tblStocksPricingVol
GROUP BY tblStocksPricingVol.Symbol
HAVING (((Count(tblStocksPricingVol.PricingVolWebQueryDate))>=6));

John Spencer said:
Sorry, I had a bracketing error in what I suggested.

(SELECT M2.LowPrice FROM tblStocksPricingVol AS M2
WHERE M2.Symbol = tblStocksPricingVol.Symbol
AND M2.PricingVolWebQueryDate =
CDate(Workday([tblStocksPricingVol].[PricingVolWebQueryDate],-1)))

Of course since you really don't need the brackets with properly formed names,
the following should work unless I've managed to add some other error (like
unbalanced parentheses).


(SELECT M2.LowPrice FROM tblStocksPricingVol AS M2
WHERE M2.Symbol = tblStocksPricingVol.Symbol
AND M2.PricingVolWebQueryDate =
CDate(Workday(tblStocksPricingVol.PricingVolWebQueryDate,-1)))


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

John said:
Try specifying the tables in the subquery. If you don't the subquery
will use the M2 fields. DatePriorToLatest will probably need to
recapitulate the entire calculation.

(SELECT M2.LowPrice FROM tblStocksPricingVol AS M2
WHERE M2.Symbol = tblStocksPricingVol.Symbol
AND M2.PricingVolWebQueryDate =
CDate(Workday([tblStocksPricingVol.PricingVolWebQueryDate],-1)))

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

Hello all. I'm struggling a bit with a query I'm trying to write. It's
based on two tables with a one to many relationship. The "one" side is
a master list of stock symbols. The many table is daily price and
trading volume for each stock. I'm trying to add several fields,
starting with the symbol. I then want to have fields get data from
different dates (latest data point, the day before, etc). I've pasted
below what I've got so far. This query runs but the last field (the
subquery) is blank when executed. Any ideas? Maybe the join? Please
help!! :) Thanks in advance...

Kevin

SELECT DISTINCT tblSymbolsMain.Symbol, tblSymbolsMain.Company,
tblStocksPricingVol.PricingVolWebQueryDate AS LatestDate,
CDate(Workday([LatestDate],-1)) AS DatePriorToLatest,
(SELECT M2.LowPrice FROM tblStocksPricingVol AS M2 WHERE M2.Symbol =
Symbol AND M2.PricingVolWebQueryDate = DatePriorToLatest) AS
DatePriorToLatestLowPr

FROM tblSymbolsMain INNER JOIN tblStocksPricingVol ON
tblSymbolsMain.Symbol = tblStocksPricingVol.Symbol
WHERE
(((tblStocksPricingVol.PricingVolWebQueryDate)=DMax("PricingVolWebQueryDate","tblStocksPricingVol","Symbol='"
& [tblStocksPricingVol].[Symbol] & "'")));
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Your problem is you probably cannot refer to the calculated field latest date
in the subquery-you would have to redo the entire latestdate subquery.
Another problem is that you can only return one value so you need to use Max
around M2.PricingVolWebQueryDate

Your easiest solution might be to leave the PriorDate subquery out of this
query. Then use this query as the source for another query. In which you can
use LatestDate in a subquery to calculate prior date.

There might be a better way to accomplish your goal.

For instance, your LatestDate calculation should only require

Max(PricingVolWebQUeryDate) as LatestDate

And then your subquery for PriorDate might be as follows (which you might need
to add to the group by

(SELECT Max(M2.PricingVolWebQueryDate) FROM tblStocksPricingVol AS M2 WHERE
M2.Symbol = tblStocksPricingVol.Symbol AND M2.PricingVolWebQueryDate <
Max(tblStocksPricingVol.LatestDate) AS PriorDate

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi John, thanks for following up. I've taken your suggestion and started
over. You got me through the first subquery, but the next field gives the
error "the expression is typed incorrectly, or it is too complex to be
evaluated." The SQL is below with the offending subquery in the middle. This
is frustrating... I've seen articles mentioning this error arising with date
value fields? but can't find solutions or workarounds. Thanks for working
with me, here's what I've got...

SELECT tblStocksPricingVol.Symbol,
Count(tblStocksPricingVol.PricingVolWebQueryDate) AS CountOf,
(SELECT Max(PricingVolWebQueryDate) FROM tblStocksPricingVol AS M2 WHERE
M2.Symbol = tblStocksPricingVol.Symbol) AS LatestDate,

(SELECT M2.PricingVolWebQueryDate FROM tblStocksPricingVol AS M2 WHERE
M2.Symbol = tblStocksPricingVol.Symbol AND M2.PricingVolWebQueryDate =
Workday(tblStocksPricingVol.LatestDate,-1)) AS PriorDate

FROM tblStocksPricingVol
GROUP BY tblStocksPricingVol.Symbol
HAVING (((Count(tblStocksPricingVol.PricingVolWebQueryDate))>=6));

John Spencer said:
Sorry, I had a bracketing error in what I suggested.

(SELECT M2.LowPrice FROM tblStocksPricingVol AS M2
WHERE M2.Symbol = tblStocksPricingVol.Symbol
AND M2.PricingVolWebQueryDate =
CDate(Workday([tblStocksPricingVol].[PricingVolWebQueryDate],-1)))

Of course since you really don't need the brackets with properly formed names,
the following should work unless I've managed to add some other error (like
unbalanced parentheses).


(SELECT M2.LowPrice FROM tblStocksPricingVol AS M2
WHERE M2.Symbol = tblStocksPricingVol.Symbol
AND M2.PricingVolWebQueryDate =
CDate(Workday(tblStocksPricingVol.PricingVolWebQueryDate,-1)))


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

John said:
Try specifying the tables in the subquery. If you don't the subquery
will use the M2 fields. DatePriorToLatest will probably need to
recapitulate the entire calculation.

(SELECT M2.LowPrice FROM tblStocksPricingVol AS M2
WHERE M2.Symbol = tblStocksPricingVol.Symbol
AND M2.PricingVolWebQueryDate =
CDate(Workday([tblStocksPricingVol.PricingVolWebQueryDate],-1)))

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


Accessor wrote:
Hello all. I'm struggling a bit with a query I'm trying to write. It's
based on two tables with a one to many relationship. The "one" side is
a master list of stock symbols. The many table is daily price and
trading volume for each stock. I'm trying to add several fields,
starting with the symbol. I then want to have fields get data from
different dates (latest data point, the day before, etc). I've pasted
below what I've got so far. This query runs but the last field (the
subquery) is blank when executed. Any ideas? Maybe the join? Please
help!! :) Thanks in advance...

Kevin

SELECT DISTINCT tblSymbolsMain.Symbol, tblSymbolsMain.Company,
tblStocksPricingVol.PricingVolWebQueryDate AS LatestDate,
CDate(Workday([LatestDate],-1)) AS DatePriorToLatest,
(SELECT M2.LowPrice FROM tblStocksPricingVol AS M2 WHERE M2.Symbol =
Symbol AND M2.PricingVolWebQueryDate = DatePriorToLatest) AS
DatePriorToLatestLowPr

FROM tblSymbolsMain INNER JOIN tblStocksPricingVol ON
tblSymbolsMain.Symbol = tblStocksPricingVol.Symbol
WHERE
(((tblStocksPricingVol.PricingVolWebQueryDate)=DMax("PricingVolWebQueryDate","tblStocksPricingVol","Symbol='"
& [tblStocksPricingVol].[Symbol] & "'")));
Click to expand...
Click to expand...
Click to expand...
 
A

Accessor

Hi John. Thanks again. Sorry for the lag in response BTW. You're right, best
to break it down into a couple queries, that did the trick!

John Spencer said:
Your problem is you probably cannot refer to the calculated field latest date
in the subquery-you would have to redo the entire latestdate subquery.
Another problem is that you can only return one value so you need to use Max
around M2.PricingVolWebQueryDate

Your easiest solution might be to leave the PriorDate subquery out of this
query. Then use this query as the source for another query. In which you can
use LatestDate in a subquery to calculate prior date.

There might be a better way to accomplish your goal.

For instance, your LatestDate calculation should only require

Max(PricingVolWebQUeryDate) as LatestDate

And then your subquery for PriorDate might be as follows (which you might need
to add to the group by

(SELECT Max(M2.PricingVolWebQueryDate) FROM tblStocksPricingVol AS M2 WHERE
M2.Symbol = tblStocksPricingVol.Symbol AND M2.PricingVolWebQueryDate <
Max(tblStocksPricingVol.LatestDate) AS PriorDate

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi John, thanks for following up. I've taken your suggestion and started
over. You got me through the first subquery, but the next field gives the
error "the expression is typed incorrectly, or it is too complex to be
evaluated." The SQL is below with the offending subquery in the middle. This
is frustrating... I've seen articles mentioning this error arising with date
value fields? but can't find solutions or workarounds. Thanks for working
with me, here's what I've got...

SELECT tblStocksPricingVol.Symbol,
Count(tblStocksPricingVol.PricingVolWebQueryDate) AS CountOf,
(SELECT Max(PricingVolWebQueryDate) FROM tblStocksPricingVol AS M2 WHERE
M2.Symbol = tblStocksPricingVol.Symbol) AS LatestDate,

(SELECT M2.PricingVolWebQueryDate FROM tblStocksPricingVol AS M2 WHERE
M2.Symbol = tblStocksPricingVol.Symbol AND M2.PricingVolWebQueryDate =
Workday(tblStocksPricingVol.LatestDate,-1)) AS PriorDate

FROM tblStocksPricingVol
GROUP BY tblStocksPricingVol.Symbol
HAVING (((Count(tblStocksPricingVol.PricingVolWebQueryDate))>=6));

John Spencer said:
Sorry, I had a bracketing error in what I suggested.

(SELECT M2.LowPrice FROM tblStocksPricingVol AS M2
WHERE M2.Symbol = tblStocksPricingVol.Symbol
AND M2.PricingVolWebQueryDate =
CDate(Workday([tblStocksPricingVol].[PricingVolWebQueryDate],-1)))

Of course since you really don't need the brackets with properly formed names,
the following should work unless I've managed to add some other error (like
unbalanced parentheses).


(SELECT M2.LowPrice FROM tblStocksPricingVol AS M2
WHERE M2.Symbol = tblStocksPricingVol.Symbol
AND M2.PricingVolWebQueryDate =
CDate(Workday(tblStocksPricingVol.PricingVolWebQueryDate,-1)))


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

John Spencer wrote:
Try specifying the tables in the subquery. If you don't the subquery
will use the M2 fields. DatePriorToLatest will probably need to
recapitulate the entire calculation.

(SELECT M2.LowPrice FROM tblStocksPricingVol AS M2
WHERE M2.Symbol = tblStocksPricingVol.Symbol
AND M2.PricingVolWebQueryDate =
CDate(Workday([tblStocksPricingVol.PricingVolWebQueryDate],-1)))

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


Accessor wrote:
Hello all. I'm struggling a bit with a query I'm trying to write. It's
based on two tables with a one to many relationship. The "one" side is
a master list of stock symbols. The many table is daily price and
trading volume for each stock. I'm trying to add several fields,
starting with the symbol. I then want to have fields get data from
different dates (latest data point, the day before, etc). I've pasted
below what I've got so far. This query runs but the last field (the
subquery) is blank when executed. Any ideas? Maybe the join? Please
help!! :) Thanks in advance...

Kevin

SELECT DISTINCT tblSymbolsMain.Symbol, tblSymbolsMain.Company,
tblStocksPricingVol.PricingVolWebQueryDate AS LatestDate,
CDate(Workday([LatestDate],-1)) AS DatePriorToLatest,
(SELECT M2.LowPrice FROM tblStocksPricingVol AS M2 WHERE M2.Symbol =
Symbol AND M2.PricingVolWebQueryDate = DatePriorToLatest) AS
DatePriorToLatestLowPr

FROM tblSymbolsMain INNER JOIN tblStocksPricingVol ON
tblSymbolsMain.Symbol = tblStocksPricingVol.Symbol
WHERE
(((tblStocksPricingVol.PricingVolWebQueryDate)=DMax("PricingVolWebQueryDate","tblStocksPricingVol","Symbol='"
& [tblStocksPricingVol].[Symbol] & "'")));
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access query help, expression fields work seperately but not toget 3
SQL syntax error with vba 3
Subquery help PLEASE! :P 2
'Data Type Mismatch In Criteria Expression' error when using 'Count' in query Design; Help! 0
Grouping Top Values 1
Query help please 3
Help Please 5
can't find the right query 0

Top