Select query question

[Christina Wise via AccessMonster.com]

I am trying to correlate/pull historical data from one table based on another.
Specifically, I have one table [50Avg] that has the following fields:

ID Ticker TradeDate DateYesterday
5766 AACE 4/21/04 4/20/04
6121 AAI 6/12/03 6/11/03

I have another table [Main2], which contains historical stock price data that
has the following fields:

ID Ticker TradeDate Close
5766 AACE 4/20/04 27.75
5766 AACE 4/21/04 28.19
5766 AACE 4/22/04 29.35
6121 AAI 6/11/03 19.23
6121 AAI 6/12/03 20.35
6121 AAI 6/13/03 21.25

I am trying to write a query that will return for each stock:
ID Ticker TradeDateDateYesterday PriceYesterday.
5766 AACE 4/21/04 4/20/04 27.75
6121 AAI 6/12/03 6/11/03 19.23
I did joins between the tables for the fields ID and ticker. The IDs in both
tables are the same.
I also wrote the following select query for the PriceYesterday column:
PriceYesterday: (SELECT[Close]FROM[Main2]Where[Main2].[TradeDate]=[50Avg].
[DateYesterday])

When I try to run the query, it tells me “At most one record will be returned
by this query.” I think it’s probably a problem with the joins, but cannot
figure out what I’m doing wrong. Any advice would really be appreciated.

Christina

 

[Van T. Dinh]

1. I think is is more efficient to use a Query with SQL like:

SELECT [50Avg].ID, [50Avg].Ticker,
[50Avg].TradeDate, [50Avg].[DateYesterday], [Main2].[Close]
FROM [50Avg] LEFT JOIN [Main2]
ON [50Avg].ID = [Main2].ID
AND [50Avg].[DateYesterday] = [Main2].[TradeDate]

Change the LEFT JOIN to INNER JOIN if required.

2. Your Tables are incorrectly structure. If ID uniquely determines Ticker,
then you should not have both in the 2 Tables. In addition in Table
[50Avg], [YesterdayDate] is more or less a derived / calculated value from
[TradeDate] and therefore [YesterdayDate] should not be stored in the Table,
either.