From: FifthRing on
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
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
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
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
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]
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: non editable record
Next: Project Gallery-- Newsletter