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
|
|||
|
|||
Two different queries return same results
I’m calculating average returns for stocks over two time periods; 30-days and
90-days. These two queries always return the same results for different time periods and I have no idea why. PARAMETERS [Forms]![frmMstr]![cboEnd] DateTime; SELECT Avg(SharePrices.StockPrice) AS AvgStockPrice30_Days, SharePrices.StockSymbol FROM tblStocksGroup INNER JOIN SharePrices ON tblStocksGroup.Ticker = SharePrices.StockSymbol WHERE (((SharePrices.DateTime)=DateAdd("d",-21*(365/250),[Forms]![frmMstr]![cboEnd]))) GROUP BY SharePrices.StockSymbol; PARAMETERS [Forms]![frmMstr]![cboEnd] DateTime; SELECT Avg(SharePrices.StockPrice) AS AvgStockPrice90_Days, SharePrices.StockSymbol FROM tblStocksGroup INNER JOIN SharePrices ON tblStocksGroup.Ticker = SharePrices.StockSymbol WHERE (((SharePrices.DateTime)=DateAdd("d",-63*(365/250),[Forms]![frmMstr]![cboEnd]))) GROUP BY SharePrices.StockSymbol; I copied the date from the Access table where it is stored, pasted it into Excel, created a pivot table and it calculates fine, but in Access it’s not calculating correctly. Can someone please tell me what I’m doing wrong? Thanks! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#2
|
|||
|
|||
Two different queries return same results
What does the data look like in [Forms]![frmMstr]![cboEnd]? When you open up
the combo box in design view, go to the Data tab and note the Bound Column. Next go to the Row Source and see what the data looks for in that Bound Column. It's possible it could be bound to a column that is something like an autonumber with a very low 'date' of 1 which is 12/31/1899. That would explain it. If you go over to the Format tab, you might see a Column Widths of something like 0";4" which would hid the first column making what you see the second column. If that isn't it does the SharePrices.DateTime field have data that would support the difference between the 30 and 90 days? In other words, can both be returning the same thing because that's all there is? -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "ryguy7272" wrote: I’m calculating average returns for stocks over two time periods; 30-days and 90-days. These two queries always return the same results for different time periods and I have no idea why. PARAMETERS [Forms]![frmMstr]![cboEnd] DateTime; SELECT Avg(SharePrices.StockPrice) AS AvgStockPrice30_Days, SharePrices.StockSymbol FROM tblStocksGroup INNER JOIN SharePrices ON tblStocksGroup.Ticker = SharePrices.StockSymbol WHERE (((SharePrices.DateTime)=DateAdd("d",-21*(365/250),[Forms]![frmMstr]![cboEnd]))) GROUP BY SharePrices.StockSymbol; PARAMETERS [Forms]![frmMstr]![cboEnd] DateTime; SELECT Avg(SharePrices.StockPrice) AS AvgStockPrice90_Days, SharePrices.StockSymbol FROM tblStocksGroup INNER JOIN SharePrices ON tblStocksGroup.Ticker = SharePrices.StockSymbol WHERE (((SharePrices.DateTime)=DateAdd("d",-63*(365/250),[Forms]![frmMstr]![cboEnd]))) GROUP BY SharePrices.StockSymbol; I copied the date from the Access table where it is stored, pasted it into Excel, created a pivot table and it calculates fine, but in Access it’s not calculating correctly. Can someone please tell me what I’m doing wrong? Thanks! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#3
|
|||
|
|||
Two different queries return same results
Thanks for the look Jerry. My row source looks like this:
SELECT DISTINCT SharePrices.DateTime FROM SharePrices ORDER BY SharePrices.DateTime; My data is bound to Column 1. There is 250 days of stock data in the SharePrices table. 250 days is equivalent to 1-calendar year (market isn't open on the weekends, and similarly, closed for 9 holidays). I don't know what's causing this. Pretty annoying, actually. I could swear this was working last week, and then I present it to a client yesterday he's asking, 'why are the returns all the same for 4 different time durations?' I'm trying to find average returns for 30-days, 90-days, 180-days, and 365-days. Something must have changed since I finished this up late last week. I can't imagine what it is though. Any other thoughts Jerry? Thanks for the help!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jerry Whittle" wrote: What does the data look like in [Forms]![frmMstr]![cboEnd]? When you open up the combo box in design view, go to the Data tab and note the Bound Column. Next go to the Row Source and see what the data looks for in that Bound Column. It's possible it could be bound to a column that is something like an autonumber with a very low 'date' of 1 which is 12/31/1899. That would explain it. If you go over to the Format tab, you might see a Column Widths of something like 0";4" which would hid the first column making what you see the second column. If that isn't it does the SharePrices.DateTime field have data that would support the difference between the 30 and 90 days? In other words, can both be returning the same thing because that's all there is? -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "ryguy7272" wrote: I’m calculating average returns for stocks over two time periods; 30-days and 90-days. These two queries always return the same results for different time periods and I have no idea why. PARAMETERS [Forms]![frmMstr]![cboEnd] DateTime; SELECT Avg(SharePrices.StockPrice) AS AvgStockPrice30_Days, SharePrices.StockSymbol FROM tblStocksGroup INNER JOIN SharePrices ON tblStocksGroup.Ticker = SharePrices.StockSymbol WHERE (((SharePrices.DateTime)=DateAdd("d",-21*(365/250),[Forms]![frmMstr]![cboEnd]))) GROUP BY SharePrices.StockSymbol; PARAMETERS [Forms]![frmMstr]![cboEnd] DateTime; SELECT Avg(SharePrices.StockPrice) AS AvgStockPrice90_Days, SharePrices.StockSymbol FROM tblStocksGroup INNER JOIN SharePrices ON tblStocksGroup.Ticker = SharePrices.StockSymbol WHERE (((SharePrices.DateTime)=DateAdd("d",-63*(365/250),[Forms]![frmMstr]![cboEnd]))) GROUP BY SharePrices.StockSymbol; I copied the date from the Access table where it is stored, pasted it into Excel, created a pivot table and it calculates fine, but in Access it’s not calculating correctly. Can someone please tell me what I’m doing wrong? Thanks! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#4
|
|||
|
|||
Two different queries return same results
I'm grasping at straws here, but could the SharePrices.DateTime field happen
to be a lookup at table level to yet another table? Other than that, maybe do a compact and repair. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "ryguy7272" wrote: Thanks for the look Jerry. My row source looks like this: SELECT DISTINCT SharePrices.DateTime FROM SharePrices ORDER BY SharePrices.DateTime; My data is bound to Column 1. There is 250 days of stock data in the SharePrices table. 250 days is equivalent to 1-calendar year (market isn't open on the weekends, and similarly, closed for 9 holidays). I don't know what's causing this. Pretty annoying, actually. I could swear this was working last week, and then I present it to a client yesterday he's asking, 'why are the returns all the same for 4 different time durations?' I'm trying to find average returns for 30-days, 90-days, 180-days, and 365-days. Something must have changed since I finished this up late last week. I can't imagine what it is though. Any other thoughts Jerry? Thanks for the help!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jerry Whittle" wrote: What does the data look like in [Forms]![frmMstr]![cboEnd]? When you open up the combo box in design view, go to the Data tab and note the Bound Column. Next go to the Row Source and see what the data looks for in that Bound Column. It's possible it could be bound to a column that is something like an autonumber with a very low 'date' of 1 which is 12/31/1899. That would explain it. If you go over to the Format tab, you might see a Column Widths of something like 0";4" which would hid the first column making what you see the second column. If that isn't it does the SharePrices.DateTime field have data that would support the difference between the 30 and 90 days? In other words, can both be returning the same thing because that's all there is? -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "ryguy7272" wrote: I’m calculating average returns for stocks over two time periods; 30-days and 90-days. These two queries always return the same results for different time periods and I have no idea why. PARAMETERS [Forms]![frmMstr]![cboEnd] DateTime; SELECT Avg(SharePrices.StockPrice) AS AvgStockPrice30_Days, SharePrices.StockSymbol FROM tblStocksGroup INNER JOIN SharePrices ON tblStocksGroup.Ticker = SharePrices.StockSymbol WHERE (((SharePrices.DateTime)=DateAdd("d",-21*(365/250),[Forms]![frmMstr]![cboEnd]))) GROUP BY SharePrices.StockSymbol; PARAMETERS [Forms]![frmMstr]![cboEnd] DateTime; SELECT Avg(SharePrices.StockPrice) AS AvgStockPrice90_Days, SharePrices.StockSymbol FROM tblStocksGroup INNER JOIN SharePrices ON tblStocksGroup.Ticker = SharePrices.StockSymbol WHERE (((SharePrices.DateTime)=DateAdd("d",-63*(365/250),[Forms]![frmMstr]![cboEnd]))) GROUP BY SharePrices.StockSymbol; I copied the date from the Access table where it is stored, pasted it into Excel, created a pivot table and it calculates fine, but in Access it’s not calculating correctly. Can someone please tell me what I’m doing wrong? Thanks! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#5
|
|||
|
|||
Two different queries return same results
Nope. Compact on Close is checked off. I'm going to ask a friend (SQL
expert) tonight and will report back with my findings. -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jerry Whittle" wrote: I'm grasping at straws here, but could the SharePrices.DateTime field happen to be a lookup at table level to yet another table? Other than that, maybe do a compact and repair. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "ryguy7272" wrote: Thanks for the look Jerry. My row source looks like this: SELECT DISTINCT SharePrices.DateTime FROM SharePrices ORDER BY SharePrices.DateTime; My data is bound to Column 1. There is 250 days of stock data in the SharePrices table. 250 days is equivalent to 1-calendar year (market isn't open on the weekends, and similarly, closed for 9 holidays). I don't know what's causing this. Pretty annoying, actually. I could swear this was working last week, and then I present it to a client yesterday he's asking, 'why are the returns all the same for 4 different time durations?' I'm trying to find average returns for 30-days, 90-days, 180-days, and 365-days. Something must have changed since I finished this up late last week. I can't imagine what it is though. Any other thoughts Jerry? Thanks for the help!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jerry Whittle" wrote: What does the data look like in [Forms]![frmMstr]![cboEnd]? When you open up the combo box in design view, go to the Data tab and note the Bound Column. Next go to the Row Source and see what the data looks for in that Bound Column. It's possible it could be bound to a column that is something like an autonumber with a very low 'date' of 1 which is 12/31/1899. That would explain it. If you go over to the Format tab, you might see a Column Widths of something like 0";4" which would hid the first column making what you see the second column. If that isn't it does the SharePrices.DateTime field have data that would support the difference between the 30 and 90 days? In other words, can both be returning the same thing because that's all there is? -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "ryguy7272" wrote: I’m calculating average returns for stocks over two time periods; 30-days and 90-days. These two queries always return the same results for different time periods and I have no idea why. PARAMETERS [Forms]![frmMstr]![cboEnd] DateTime; SELECT Avg(SharePrices.StockPrice) AS AvgStockPrice30_Days, SharePrices.StockSymbol FROM tblStocksGroup INNER JOIN SharePrices ON tblStocksGroup.Ticker = SharePrices.StockSymbol WHERE (((SharePrices.DateTime)=DateAdd("d",-21*(365/250),[Forms]![frmMstr]![cboEnd]))) GROUP BY SharePrices.StockSymbol; PARAMETERS [Forms]![frmMstr]![cboEnd] DateTime; SELECT Avg(SharePrices.StockPrice) AS AvgStockPrice90_Days, SharePrices.StockSymbol FROM tblStocksGroup INNER JOIN SharePrices ON tblStocksGroup.Ticker = SharePrices.StockSymbol WHERE (((SharePrices.DateTime)=DateAdd("d",-63*(365/250),[Forms]![frmMstr]![cboEnd]))) GROUP BY SharePrices.StockSymbol; I copied the date from the Access table where it is stored, pasted it into Excel, created a pivot table and it calculates fine, but in Access it’s not calculating correctly. Can someone please tell me what I’m doing wrong? Thanks! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#6
|
|||
|
|||
Two different queries return same results
Hi -
Grasping at straws a bit too, but does the client use the same control panel settings (Regional and Language Options) for short date as you do? It might be that. John ryguy7272 wrote: Nope. Compact on Close is checked off. I'm going to ask a friend (SQL expert) tonight and will report back with my findings. I'm grasping at straws here, but could the SharePrices.DateTime field happen to be a lookup at table level to yet another table? [quoted text clipped - 66 lines] Thanks! Ryan-- -- John Goddard Ottawa, ON Canada jrgoddard at cyberus dot ca Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201002/1 |
Thread Tools | |
Display Modes | |
|
|