Multiple Joins in a Query Design

[FifthRing]

I am a novice with Access and am stumped on a query design.

I have a database with two tables. The first table has closing prices for
various stocks and the structure looks like such:
Ticker Date Price
C 1/4/10 3.4
C 1/5/10 3.53
C 1/6/10 3.64
YHOO 1/4/10 17.10
YHOO 1/5/10 17.23
YHOO 1/6/10 17.17

I have another table that looks like this:
Ticker Date1 Date2
C 1/5/10 1/6/10
YHOO 1/4/10 1/5/10

I want to build a query that will join the price when the dates and tickers
match in both tables. It would end up like this:
Ticker Date1 Date2
C 3.53 3.64
YHOO 17.10 17.23

I was able to do this with INNER JOIN one query at a time. I would add
prices for one date column to the last query and repeat for however many
dates columns I wanted to have. The problem is that I want to have up to 60
dates to look up. I want to have one query to show the results without
writing 60 separate queries.

Any ideas on how to solve this?

 

[KARL DEWEY]

You do not need the second table just use a crosstab query on the first table.

 

[FifthRing]

Thank you for your response. However I don't think that solves what I am
trying to accomplish.

My first table has many years worth of daily prices for many stocks. I just
showed a small sample of what it looks like.

The second table has dates I want to take a look at the stock prices. I
have 60 dates that I want to look at out of the years worth of data and the
dates are different for each stock. I just call them date1, date2, date3,
etc.

Here is a sample query that combines the tables for one date:

SELECT PriceHistory.Ticker, PriceHistory.[Adj Close] AS Price1, Q1.Date1,
Q1.Date2
FROM PriceHistory, Q1
WHERE (((PriceHistory.Ticker)=[q1].[ticker]) AND
((Q1.Date1)=[pricehistory].[date]));

I then would add another query to add the second date and price for that date:
SELECT PriceHistory.Ticker, Q2.Date1, Q2.Price1, Q2.Date2, PriceHistory.[Adj
Close] AS Price2
FROM PriceHistory RIGHT JOIN Q2 ON PriceHistory.Ticker = Q2.Ticker
WHERE (((PriceHistory.Ticker)=[q2].[ticker]) AND
((Q2.Date2)=[pricehistory].[date]));

I am trying to figure out the best way to do this in one query instead of
adding 60 queries to add 60 columns of prices with the corresponding dates.

Thank you.

 

[John W. Vinson]

The second table has dates I want to take a look at the stock prices. I
have 60 dates that I want to look at out of the years worth of data and the
dates are different for each stock. I just call them date1, date2, date3,
etc.

That's an incorrect design. What if you want *70* dates someday!?

"Fields are expensive, records are cheap". Rather than one row with 60 dates,
consider using one datefield with 60 rows. Then a very simple join will get
your result.

 

[FifthRing]

I agree. I know there must be a better way to get the prices on one table.
Adding them one query at a time is the only way I have figured out to do what
I want to accomplish.

I just started to learn Access, so I know I am missing the correct way to do
this.

 

[FifthRing]

Thank you! I finally figured out a plan of attack based on your suggestion
to add 60 rows instead of 60 columns like I was thinking.

That is completely new way of looking at things !

I love your quote: "Fields are expensive, records are cheap".

Thanks again.

 

[John W. Vinson]

Thank you! I finally figured out a plan of attack based on your suggestion
to add 60 rows instead of 60 columns like I was thinking.

That is completely new way of looking at things !

I love your quote: "Fields are expensive, records are cheap".

Thanks again.

You're welcome! Glad that I was able to help. Here are some deeper wells of
insight:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials