If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculate percent return for different stocks
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!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#2
|
|||
|
|||
Calculate percent return for different stocks
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" wrote: 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!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#3
|
|||
|
|||
Calculate percent return for different stocks
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 he 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" wrote: 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" wrote: 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!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#4
|
|||
|
|||
Calculate percent return for different stocks
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" wrote: 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 he 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" wrote: 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" wrote: 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!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#5
|
|||
|
|||
Calculate percent return for different stocks
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" wrote: 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" wrote: 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 he 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" wrote: 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" wrote: 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!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#6
|
|||
|
|||
Calculate percent return for different stocks
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" wrote: 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" wrote: 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" wrote: 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 he 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" wrote: 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" wrote: 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!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#7
|
|||
|
|||
Calculate percent return for different stocks
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 he 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" wrote: 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" wrote: 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!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#8
|
|||
|
|||
Calculate percent return for different stocks
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 he 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" wrote: 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" wrote: 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!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#9
|
|||
|
|||
Calculate percent return for different stocks
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 he 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" wrote: 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" wrote: 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!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#10
|
|||
|
|||
Calculate percent return for different stocks
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 he 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" wrote: 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" wrote: 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!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
|
Thread Tools | |
Display Modes | |
|
|