I was using this:
SELECT SharePrices.StockSymbol, SharePrices.StockPrice AS StartPrice,
SharePrices_2.StockPrice AS EndPrice,
DateAdd("d",-21*(365/250),[Forms]![frmMstr]![cboEnd]) AS Date30End,
DateAdd("d",0,[Forms]![frmMstr]![cboEnd]) AS Date30Start,
(SharePrices_2.StockPrice-SharePrices.StockPrice)/SharePrices.StockPrice AS
Pct_Return
FROM (tblStocksGroup INNER JOIN SharePrices ON tblStocksGroup.Ticker =
SharePrices.StockSymbol) INNER JOIN SharePrices AS SharePrices_2 ON
tblStocksGroup.Ticker = SharePrices_2.StockSymbol
WHERE
((([SharePrices].[PriceDate])=DateAdd("d",0,[Forms]![frmMstr]![cboEnd])) AND
(([SharePrices_2].[PriceDate])=DateAdd("d",-21*(365/250),[Forms]![frmMstr]![cboEnd])))
GROUP BY SharePrices.StockSymbol, SharePrices.StockPrice,
SharePrices_2.StockPrice;
Then, did away with the last two fields, because I just realized that I
don’t need to be prompted for the following two parameters:
‘SharePrices.PriceDate’ and ‘SharePrices_2.PriceDate’
Now, I am getting a Cartesian Product (query returns 4,615,022 records)!
What I want to do is return all records in this query; total of 28,832.
Now, I am working with this:
SELECT SharePrices.StockSymbol, SharePrices.StockPrice AS StartPrice,
SharePrices_2.StockPrice AS EndPrice,
DateAdd("d",-21*(365/250),[Forms]![frmMstr]![cboEnd]) AS Date30Start,
DateAdd("d",0,[Forms]![frmMstr]![cboEnd]) AS Date30End,
(SharePrices_2.StockPrice-SharePrices.StockPrice)/SharePrices.StockPrice AS
Pct_Return
FROM (tblStocksGroup INNER JOIN SharePrices ON tblStocksGroup.Ticker =
SharePrices.StockSymbol) INNER JOIN SharePrices AS SharePrices_2 ON
tblStocksGroup.Ticker = SharePrices_2.StockSymbol
GROUP BY SharePrices.StockSymbol, SharePrices.StockPrice,
SharePrices_2.StockPrice;
Everything else seems fine; start and end dates, and the percent return (I
formatted it in Grid View). I just have to figure out the relationships to
get rid of the Cartesian Product. I tried left joins and right joins.
Nothing seems to be working thus far. I'm going to keep trying things
though; must be some simple solution that I'm not seeing yet. Any ideas on
how to handle this? I think this is the last step here.
Thanks for everything!!
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
Daryl S said:
Ryan -
It means that in a query with totals, you need to tell Access what to do
with each field - you either 'group by' the field or you calculate something
with it (e.g. count, max, sum, etc.), which are the aggregate functions. In
design mode, make sure the totals record shows Group By for the SharePrices
field (we re-title is AS StartPrice). Or in the SQL, change the last line
from
GROUP BY SharePrices.StockSymbol;
to
GROUP BY SharePrices.StockSymbol, SharePrices.StockPrice;
If you have any other issues, post your SQL, as it will be easier and faster
for us to resolve issues...
--
Daryl S
:
Thanks again Daryl. I can see it in Design View (before I couldn't even get
that). However, I still can't run the Query. I get a message that reads
'You tried to execute a query that does not include the specified expression
'StartPrice' as part of an aggregate function'. What does this mean? Dang!
I wish I knew this stuff better. I've worked with queries a lot in the past,
but I didn't do many calculations...just setting up relationships and doing
some union queries and some crosstab queries. Anyway, what do you think?
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
:
Ryan -
My bad. The criteria should be with SharePrices_2.StockSymbol (since we
are aliasing as SharePrices_2), not SharePrices. Like this:
INNER JOIN SharePrices AS SharePrices_2 ON
tblStocksGroup.Ticker = SharePrices_2.StockSymbol
Try fixing that and go again.
--
Daryl S
:
Thanks for workign on this Daryl. Unfortunately the Query wouldn't build.
Maybe it is word wrap? I tried a few things, but couldn't salvage it. As
you posted it, the Query fails here:
tblStocksGroup.Ticker=SharePrices.StockSymbol
I geat a message saying 'JOIN expression not supported'.
I guess that's the second join on the SharePrices table that you were
referring to.
Any thoughts on how to rtesolve this?
Thanks!
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
:
Ryan -
You need to get the stock prices for two dates, so you need to join to the
SharePrices table twice, and limit your share prices to just those dates.
Here is your query altered (but not tested). I question if you have the
correct 'start' and 'end', as it looks to me like the 'end' date is earlier
than the 'start' date. If so, switch those calculation is each place they
appear.
SELECT SharePrices.StockSymbol, SharePrices.StockPrice AS StartPrice,
SharePrices_2.StockPrice AS EndPrice,
DateAdd("d",-21*(365/250),[Forms]![frmMstr]![cboEnd]) AS Date30End,
DateAdd("d",0,[Forms]![frmMstr]![cboEnd]) AS Date30Start,
(SharePrices_2.StockPrice - SharePrices.StockPrice)/SharePrices.StockPrice
AS Pct_Return
FROM (tblStocksGroup INNER JOIN SharePrices ON tblStocksGroup.Ticker =
SharePrices.StockSymbol) INNER JOIN SharePrices AS SharePrices_2 ON
tblStocksGroup.Ticker =
SharePrices.StockSymbol
WHERE SharePrices.PriceDate = DateAdd("d",0,[Forms]![frmMstr]![cboEnd])
AND SharePrices_2.PriceDate =
DateAdd("d",-21*(365/250),[Forms]![frmMstr]![cboEnd])
GROUP BY SharePrices.StockSymbol;
--
Daryl S
:
I’m trying to calculate the percent return for different stocks. I need to
get the ([End Price]-[Begin Price])/[Begin Price].
Here is my (non-working) query:
SELECT SharePrices.StockSymbol, SharePrices.StockPrice,
DateAdd("d",-21*(365/250),[Forms]![frmMstr]![cboEnd]) AS Date30End,
DateAdd("d",0,[Forms]![frmMstr]![cboEnd]) AS Date30Start
FROM tblStocksGroup INNER JOIN SharePrices ON tblStocksGroup.Ticker =
SharePrices.StockSymbol
GROUP BY SharePrices.StockSymbol, SharePrices.StockPrice,
DateAdd("d",-21*(365/250),[Forms]![frmMstr]![cboEnd]),
DateAdd("d",0,[Forms]![frmMstr]![cboEnd]);
Essentially this is showing all dates, and all stock prices; just want to
see one line—the stock symbol and the calcualtion. What I want to do is find
the day 21 days from the day I select on frmMstr, find the day in frmMstr,
and then do the calculation I described above:
([End Price]-[Begin Price])/[Begin Price]
Thanks for the help with this!!