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

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

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

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

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

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