Multiple Joins in a Query Design
in [General]
|
Prev: non editable record
Next: Project Gallery-- Newsletter
From: FifthRing on 16 Mar 2010 17:09 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?
From: KARL DEWEY on 16 Mar 2010 18:00 You do not need the second table just use a crosstab query on the first table. -- Build a little, test a little. "FifthRing" wrote: > 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?
From: FifthRing on 16 Mar 2010 18:47 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. "KARL DEWEY" wrote: > You do not need the second table just use a crosstab query on the first table. > > -- > Build a little, test a little. > > > "FifthRing" wrote: > > > 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?
From: John W. Vinson on 16 Mar 2010 19:07 On Tue, 16 Mar 2010 15:47:01 -0700, FifthRing <FifthRing(a)discussions.microsoft.com> wrote: >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. -- John W. Vinson [MVP]
From: FifthRing on 16 Mar 2010 19:52
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. "John W. Vinson" wrote: > On Tue, 16 Mar 2010 15:47:01 -0700, FifthRing > <FifthRing(a)discussions.microsoft.com> wrote: > > >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. > -- > > John W. Vinson [MVP] > . > |