Multiple Joins in a Query Design

  • Thread starter Thread starter FifthRing
  • Start date Start date
F

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?
 
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.
 
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.
Click to expand...

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.
 
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.
 
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.
 
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.
Click to expand...

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
 
Back
Top