Newbie with data retrieval questions

[Box]

Thanks in advance. I'm experienced with Excel but new to Access and have
some basic questions. Could really use some basic guidance to get to where
I'm going.

I have 3 tables, their format isn't set in stone yet, but they are 1. a
list of S&P 500 Index closing prices by date, 2. a list of S&P 500 Total
Return Index by date (different from #1 in that it includes dividends), and
3. a list of all the S&P 500 options (e.g. the options that are traded on
the entire index at the CBOE) closing prices. #3 is thousands of lines long
and each line is the following format...

Trade date, option expiration date (month and year), option strike price,
option type (call or put), that option's close price.

Each of these variable will have many duplicates, scores of distinct options
traded on Aug. 31, 2005; some expired in Sept., some in December, etc., there
are scores of option strike prices, each expiration and strike has puts and
call, etc. but there is a distinct record for each distinct option on a
single day once you've 'answered' all the variables.

What I want to be able to do is to get the following info. for every date
over a 30 day period: the S&P 500 Index close, the S&P 500 Total Return
Index close and the close for 4 specific, unchanging options (each option is
defined by an expiration month and year(this is in a single field so that
options expiring in Sept. 2005 are distinct from those expiring in Sept.
2006) , strike price, and type (call or put). I'll also need to do some
basic math to arrive at a daily index value based on the six closing prices.

At the end of the 30 days I'll identify 4 new specific options and repeat
for the next 30 day historical period.

I can manipulate the data and get it into Access tables. I can creat
queries, forms, etc. but don't really know how to wring out the precise data
I need without wading through a bunch of other stuff. I could really use
some guidance on the best way to retrieve just the 6 values I'll need for any
given date. The best format for the results would be...

Date, S&P Index close, S&P Total Return Index close, Option1 close, Option2
close, Option3 close, Option4 close

Next Date, S&P Index close, S&P Total Return Index close, Option1 close,
Option2 close, Option3 close, Option4 close

Next Date, etc.

I've thought about advanced filtering, forms, queries, pages, etc. and I'm
looking for a little 'local knowledge' before I spend a ton of time going
down blind alleys.

Thanks a bunch. I realize this is different than most questions.

B. Box

 

[Box]

Already I'm learning.

The first table could have: date, S&P 500 Index Close, S&P Total Return
Index Close

The second table could have all the option data. The options aren't on
individual stocks in the S&P, rather they are on the S&P Index itself (ticker
symbol SPX).

I've pasted an example of the data below...

75 12/02/2002 43300 Call 500 3/2003
75 12/02/2002 40 Put 500 3/2003
75 12/02/2002 120 Put 500 6/2003
75 12/02/2002 43390 Call 500 6/2004
75 12/02/2002 590 Put 500 6/2004
75 12/02/2002 260 Put 500 9/2003
75 12/02/2002 43520 Call 500 12/2002
75 12/02/2002 3 Put 500 12/2002
75 12/02/2002 43180 Call 500 12/2003
75 12/02/2002 450 Put 500 12/2003
75 12/02/2002 38920 Call 510 6/2004
75 12/02/2002 1000 Put 510 6/2004
75 12/02/2002 28 Put 600 1/2003

75 is the data vendor contract ID, 12/02/2002 is the trade date, the next
number is the closing price for that option (without any decimal, divide by
100 to get the actual price but that's easy to fix), the next is the option
type (put or call) the next is the strike price for that option and the final
column is the expiration for the option (all these options expire on the
third Friday of the listed month/year). A trader would describe the last
option listed by saying the "Jan. oh three, six hundred put closed at 28
cents"

There may be a total of approx. 100 prices for every trade date. Following
the info. for 12/02/2002 would be all the prices for 12/03/2003. For every
trading day in the month of Jan. 2002 we may want to know the daily closing
prices for the March 2002 900 call; the March 2002, 950 call; the June 2002
900 call; and the June 2002 950 call; in addition to the S&P 500 Index and
S&P 500 Total Return Index values. For every day in Feb. the 4 options would
change. I'd like to be able to input (manually is fine) the four options
we're looking for and have the following data spit out in approx. this format.

Trade date, S&P Index close, S&P Total Return Index close, Option1 close,
Option2 close, Option3 close, Option4 close NEW LINE
Trade date +1, S&P Index close, S&P Total Return Index close, Option1 close,
Option2 close, Option3 close, Option4 close NEW LINE
Trade date +2, etc.

Note sure what Access feature to use to get that information. Just using
some sort of search for each nugget for each day (total of 6) multiplied by
approx. 260 trading days per year multiplied by 15 years of data, well I'd
just be really old. Inputing the required options once for each month and
having data spit out in workable format would be great, since there are only
about 180 months.

Thanks,

BB