Does Access Allow a single query that pulls sections of the same data?

[mrr2]

Hi All,


I am trying to run a query that pulls sections from a list of data (post-reccesion moves in the S&P500)

Here is what the query looks like, but access doesnt return anything - I wonder if it is possible?

Many thanks for any help!

SELECT tbl_SP_500.Period, tbl_SP_500.PX_LAST, tbl_SP_500.Period, tbl_SP_500.Period, tbl_SP_500.PX_LAST, tbl_SP_500.Period, tbl_SP_500.PX_LAST, tbl_SP_500.Period, tbl_SP_500.PX_LAST, tbl_SP_500.Period, tbl_SP_500.PX_LAST, tbl_SP_500.Period, tbl_SP_500.PX_LAST
FROM tbl_SP_500
WHERE (((tbl_SP_500.Period) Between #2/1/1961# And #2/1/1965#) AND ((tbl_SP_500.Period) Between #11/1/1970# And #11/1/1974#) AND ((tbl_SP_500.Period) Between #3/1/1975# And #3/1/1975#) AND ((tbl_SP_500.Period) Between #11/1/1982# And #11/1/1986#) AND ((tbl_SP_500.Period) Between #3/1/1991# And #3/1/1995#) AND ((tbl_SP_500.Period) Between #11/1/2001# And #11/1/2004#) AND ((tbl_SP_500.Period) Between #1/1/2009# And #1/1/2012#));

[pbaldy]

Perhaps you wanted "OR" instead of "AND"? A date can't be in the 60's and in the 70's and...

[mrr2]

Thanks pbaldy

I tried it like this, but found that access is not registering the differnet data ranges, and instead gives me the same values of dates and prices 7x, instead of 7 different time series from the same data

SELECT tbl_SP_500.Period, tbl_SP_500.PX_LAST, tbl_SP_500.Period, tbl_SP_500.Period, tbl_SP_500.PX_LAST, tbl_SP_500.Period, tbl_SP_500.PX_LAST, tbl_SP_500.Period, tbl_SP_500.PX_LAST, tbl_SP_500.Period, tbl_SP_500.PX_LAST, tbl_SP_500.Period, tbl_SP_500.PX_LAST
FROM tbl_SP_500
WHERE (((tbl_SP_500.Period) Between #2/1/1961# and #2/1/1965#) or ((tbl_SP_500.Period) Between #11/1/1970# and #11/1/1974#) or ((tbl_SP_500.Period) Between #3/1/1975# and #3/1/1975#) or ((tbl_SP_500.Period) Between #11/1/1982# and #11/1/1986#) or ((tbl_SP_500.Period) Between #3/1/1991# And #3/1/1995#) or((tbl_SP_500.Period) Between #11/1/2001# And #11/1/2004#) or ((tbl_SP_500.Period) Between #1/1/2009# And #1/1/2012#));

[orange]

Just curious -- why do you repeat the fields?

SELECT tbl_SP_500.Period, tbl_SP_500.PX_LAST, tbl_SP_500.Period, tbl_SP_500.Period, tbl_SP_500.PX_LAST, tbl_SP_500.Period, tbl_SP_500.PX_LAST,


How is tbl_SP_500.Period defined? What values?

[mrr2]

Just curious -- why do you repeat the fields?

SELECT tbl_SP_500.Period, tbl_SP_500.PX_LAST, tbl_SP_500.Period, tbl_SP_500.Period, tbl_SP_500.PX_LAST, tbl_SP_500.Period, tbl_SP_500.PX_LAST,


How is tbl_SP_500.Period defined? What values?

that is defined as date, Orange - i have a series from 1950 to current, and all i want to do is extract time periods when recssions ended and the corresponding price (px_last)

i.e. :

February 1961

November 1970

March 1975

November 1982

March 1991

November 2001

June 2009

[pbaldy]

Can you post the db, or a representative sample, and the result you're trying to get?

[mrr2]

Sure, the database was too large, here is the excel format and the query (that doesnt work):

sample sp500 8.20.12.zip

SELECT tbl_SP_500.Period, tbl_SP_500.PX_LAST, tbl_SP_500.Period, tbl_SP_500.Period, tbl_SP_500.PX_LAST, tbl_SP_500.Period, tbl_SP_500.PX_LAST, tbl_SP_500.Period, tbl_SP_500.PX_LAST, tbl_SP_500.Period, tbl_SP_500.PX_LAST, tbl_SP_500.Period, tbl_SP_500.PX_LAST
FROM tbl_SP_500
WHERE (((tbl_SP_500.Period) Between #2/1/1961# and #2/1/1965#) or ((tbl_SP_500.Period) Between #11/1/1970# and #11/1/1974#) or ((tbl_SP_500.Period) Between #3/1/1975# and #3/1/1975#) or ((tbl_SP_500.Period) Between #11/1/1982# and #11/1/1986#) or ((tbl_SP_500.Period) Between #3/1/1991# And #3/1/1995#) or((tbl_SP_500.Period) Between #11/1/2001# And #11/1/2004#) or ((tbl_SP_500.Period) Between #1/1/2009# And #1/1/2012#));

All help is appreciated!

thanks!

[pbaldy]

Well, I would say the query works in that it returns what you've asked it to. Can you can draw a picture of what you expect?

[mrr2]

yes, like this - but for some reason, my access query keeps giving me the same thing over/over again

[pbaldy]

Like I said, it's giving you what you asked for: records whose dates fall within those periods of time. Structuring it like you want is a whole 'nother matter. Offhand I don't think you're going to get a query to return records like that, or not easily anyway. The best way I can think of is separate queries for each date range, with each feeding a subreport on a main report, stacked side-by-side.

[mrr2]

thank you pbaldy - I appreciate you looking into this for me.