Calculate percent return for different stocks

R

ryguy7272

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!!
 
D

Daryl S

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;
 
R

ryguy7272

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''.


Daryl S said:
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


ryguy7272 said:
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!!
Click to expand...
Click to expand...
 
D

Daryl S

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


ryguy7272 said:
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''.


Daryl S said:
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


ryguy7272 said:
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!!
Click to expand...
Click to expand...
Click to expand...
 
R

ryguy7272

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''.


Daryl S said:
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


ryguy7272 said:
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''.


Daryl S said:
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!!
Click to expand...
Click to expand...
Click to expand...
 
D

Daryl S

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


ryguy7272 said:
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''.


Daryl S said:
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


ryguy7272 said:
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!!
Click to expand...
Click to expand...
Click to expand...
 
R

ryguy7272

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


ryguy7272 said:
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''.


Daryl S said:
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!!
Click to expand...
Click to expand...
Click to expand...
 
R

ryguy7272

I spent a couple hours on this; still can't figure it out. Now I'm thinking
that I just need two tables (as opposed to three), and somehow I need to use
just those two tables to do the start date, end date, and percent change in
stock price. I'm not sure if this is hard, and that's why I'm struggling, or
easy, and I'm just not seeing the solution. I'm thinking it's kind of hard...

Any thoughts Daryl?

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


ryguy7272 said:
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


ryguy7272 said:
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!!
Click to expand...
Click to expand...
Click to expand...
 
D

Daryl S

Ryan -

You are getting a cartesian product because there is nothing to tell it how
to relate SharePrices and SharePrices_2. If you always want the start and
end dates to be 21*(365/250) days apart, you can add this WHERE clause:

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
WHERE
[SharePrices_2].[PriceDate])=DateAdd("d",Round(21*(365/250),0),[SharePrices].[PriceDate])
GROUP BY SharePrices.StockSymbol, SharePrices.StockPrice,
SharePrices_2.StockPrice;

--
Daryl S


ryguy7272 said:
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


ryguy7272 said:
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!!
Click to expand...
Click to expand...
Click to expand...
 
R

ryguy7272

Thanks for all the help Daryl. Unfortunately, now I am back to the place I
was a couple days ago. When I run the Query, I get prompted for a date. I
enter a date and wait a few seconds and no records are returned. I tried
this many time; I never get any records returned. That's why I got rid of
the where but then ended up with the Cartesian Product. I think I should get
about 20,000 records returned. I'm trying to calculate all % Returns in this
Query, and for three others with slightly different dates (90-days, 180-days,
and 365-days). Then I will pull these 4 Queries into a 5th Query, called
qrySummary and summarize everything there. This was working fine when I was
calculating averages for 30-days, 90-days, 180-days, and 365-days. However,
I just found out I have to calculate % Returns for those periods, not average
returns. There MUST be a way to get those 20,000-something records into this
30-day % Return Query, right.

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


Daryl S said:
Ryan -

You are getting a cartesian product because there is nothing to tell it how
to relate SharePrices and SharePrices_2. If you always want the start and
end dates to be 21*(365/250) days apart, you can add this WHERE clause:

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
WHERE
[SharePrices_2].[PriceDate])=DateAdd("d",Round(21*(365/250),0),[SharePrices].[PriceDate])
GROUP BY SharePrices.StockSymbol, SharePrices.StockPrice,
SharePrices_2.StockPrice;

--
Daryl S


ryguy7272 said:
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!!
Click to expand...
Click to expand...
Click to expand...
 
R

ryguy7272

Thanks for everything Daryl! I ended up using a slightly different technique
that was easier for me. I finally got this working; thrilled about that!
Also, I learned a few new things while working on these queries; very
thrilled about that!!
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


ryguy7272 said:
Thanks for all the help Daryl. Unfortunately, now I am back to the place I
was a couple days ago. When I run the Query, I get prompted for a date. I
enter a date and wait a few seconds and no records are returned. I tried
this many time; I never get any records returned. That's why I got rid of
the where but then ended up with the Cartesian Product. I think I should get
about 20,000 records returned. I'm trying to calculate all % Returns in this
Query, and for three others with slightly different dates (90-days, 180-days,
and 365-days). Then I will pull these 4 Queries into a 5th Query, called
qrySummary and summarize everything there. This was working fine when I was
calculating averages for 30-days, 90-days, 180-days, and 365-days. However,
I just found out I have to calculate % Returns for those periods, not average
returns. There MUST be a way to get those 20,000-something records into this
30-day % Return Query, right.

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


Daryl S said:
Ryan -

You are getting a cartesian product because there is nothing to tell it how
to relate SharePrices and SharePrices_2. If you always want the start and
end dates to be 21*(365/250) days apart, you can add this WHERE clause:

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
WHERE
[SharePrices_2].[PriceDate])=DateAdd("d",Round(21*(365/250),0),[SharePrices].[PriceDate])
GROUP BY SharePrices.StockSymbol, SharePrices.StockPrice,
SharePrices_2.StockPrice;

--
Daryl S


ryguy7272 said:
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''.


:

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!!
Click to expand...
Click to expand...
Click to expand...
 
D

Daryl S

Ryan -

Glad it's working. Do share your tricks with the rest of the us...

--
Daryl S


ryguy7272 said:
Thanks for everything Daryl! I ended up using a slightly different technique
that was easier for me. I finally got this working; thrilled about that!
Also, I learned a few new things while working on these queries; very
thrilled about that!!
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


ryguy7272 said:
Thanks for all the help Daryl. Unfortunately, now I am back to the place I
was a couple days ago. When I run the Query, I get prompted for a date. I
enter a date and wait a few seconds and no records are returned. I tried
this many time; I never get any records returned. That's why I got rid of
the where but then ended up with the Cartesian Product. I think I should get
about 20,000 records returned. I'm trying to calculate all % Returns in this
Query, and for three others with slightly different dates (90-days, 180-days,
and 365-days). Then I will pull these 4 Queries into a 5th Query, called
qrySummary and summarize everything there. This was working fine when I was
calculating averages for 30-days, 90-days, 180-days, and 365-days. However,
I just found out I have to calculate % Returns for those periods, not average
returns. There MUST be a way to get those 20,000-something records into this
30-day % Return Query, right.

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


Daryl S said:
Ryan -

You are getting a cartesian product because there is nothing to tell it how
to relate SharePrices and SharePrices_2. If you always want the start and
end dates to be 21*(365/250) days apart, you can add this WHERE clause:

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
WHERE
[SharePrices_2].[PriceDate])=DateAdd("d",Round(21*(365/250),0),[SharePrices].[PriceDate])
GROUP BY SharePrices.StockSymbol, SharePrices.StockPrice,
SharePrices_2.StockPrice;

--
Daryl S


:

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''.


:

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!!
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

Two different queries return same results 4
Calculate Percent Return for Stocks 3
Calculate Average Returns Based on Dates? 12
Avg. 1-month, 3-month, 6-month & 12-month Stock Returns 2
Calculate stock's return bet. 2 dates 1
Rosh Hashanah and Yom Kippur Holiday Dates Revisited 1
FWT Newsletter - Weekly - October 4, 2004 2
FWT Newsletter - Weekly - November 1, 2004 0

Top