Query Needed

[carl]

A sample of my data table looks as so:

Underlying ID StrikePrice
AAPL APVA10C155.00 155
AAPL APVM10P155.00 155
AAPL APVA10C160.00 160
AAPL APVM10P160.00 160
AAPL APVA10C165.00 165
AAPL APVM10P165.00 165
AAPL APVD10C160.00 160
AAPL APVP10P160.00 160
AAPL APVD10C165.00 165
AAPL APVP10P165.00 165
AAPL APVD10C155.00 155
AAPL APVP10P155.00 155
AAPL APVB10C155.00 155
AAPL APVN10P155.00 155
AAPL APVB10C160.00 160
AAPL APVN10P160.00 160
AAPL APVB10C165.00 165
AAPL APVN10P165.00 165
GS GPYA10C155.00 155
GS GPYM10P155.00 155
GS GPYA10C160.00 160
GS GPYM10P160.00 160
GS GPYA10C165.00 165
GS GPYM10P165.00 165
GS GPYD10C155.00 155
GS GPYP10P155.00 155
GS GPYD10C160.00 160
GS GPYP10P160.00 160
GS GPYD10C165.00 165
GS GPYP10P165.00 165
GS GPYB10C160.00 160
GS GPYN10P160.00 160
GS GPYB10C165.00 165
GS GPYN10P165.00 165
GS GPYB10C155.00 155
GS GPYN10P155.00 155

I am trying to find a query (if it is possible) to show records where:

Underlying=AAPL and StrikePrice is between 150 to 160 (inclusive)

OR

Underlying=GS and StrikePrice is between 160 and 165 (inclusive)

My actual query will have more Underlying (total of 50) with associated
StrikePrice ranges.

The output would like like this:

Underlying ID StrikePrice
AAPL APVA10C155.00 155
AAPL APVM10P155.00 155
AAPL APVD10C155.00 155
AAPL APVP10P155.00 155
AAPL APVB10C155.00 155
AAPL APVN10P155.00 155
GS GPYA10C160.00 160
GS GPYM10P160.00 160
GS GPYA10C165.00 165
GS GPYM10P165.00 165
GS GPYD10C160.00 160
GS GPYP10P160.00 160
GS GPYD10C165.00 165
GS GPYP10P165.00 165
GS GPYB10C160.00 160
GS GPYN10P160.00 160
GS GPYB10C165.00 165
GS GPYN10P165.00 165


Thank you in advance.

 

[theDBguy]

Hi Carl,

Sounds like you could use a Query By Form (QBF).

Hope that helps...

 

[Dale Fye]

Carl,

If I understand the last part of your post correctly, you will eventually
have 50 different values of field [Underlying] with associated ranges. If
this is the case, I would recommend you create a new table (tbl_Qry_Ranges)
with fields (Underlying, RangeStart, RangeEnd). Fill in the values of
Underlying, RangeStart and RangeEnd in this table, then write your query
something like:

SELECT *
FROM yourTable as T
INNER JOIN tbl_Qry_Ranges as Q
ON T.Underlying = Q.Underlying
and T.StrikePrice >= Q.RangeStart
and T.StrikePrice <= Q.RangeEnd

You cannot create this join entirely in the query design grid, but you could
create it with equal joins between all of these fields, then go to the SQL
view and change the equalities to inequalities.

You could also write it like:

SELECT *
FROM yourTable as T
INNER JOIN tbl_Qry_Ranges as Q
ON T.Underlying = Q.Underlying
WHERE T.StrikePrice >= Q.RangeStart
AND T.StrikePrice <= Q.RangeEnd

or

SELECT *
FROM yourTable as T
INNER JOIN tbl_Qry_Ranges as Q
ON T.Underlying = Q.Underlying
WHERE T.StrikePrice between Q.RangeStart AND Q.RangeEnd

 

[KARL DEWEY]

You need to create a table of your Underlying with StrikePrice ranges like
this --
tblWithStrikePriceRange --
Underlying LowPrice HighPrice
AAPL 150 160
GS 160 165

Use this query --
SELECT YourTable.Underlying, ID, StrikePrice
FROM YourTable, tblWithStrikePriceRange
WHERE YourTable.Underlying = tblWithStrikePriceRange.Underlying AND
tblWithStrikePriceRange.StrikePrice Between tblWithStrikePriceRange.LowPrice
AND tblWithStrikePriceRange.HighPrice;

 

[John W. Vinson]

A sample of my data table looks as so:

I am trying to find a query (if it is possible) to show records where:

Underlying=AAPL and StrikePrice is between 150 to 160 (inclusive)

OR

Underlying=GS and StrikePrice is between 160 and 165 (inclusive)

SELECT Underlying, ID, StrikePrice
FROM yourtable
WHERE (Underlying = "AAPL" AND StrikePrice BETWEEN 150 AND 160)
OR (Underlying = "GS" AND StrikePrice BETWEEN 160 AND 165)

 

[Marshall Barton]

A sample of my data table looks as so:

Underlying ID StrikePrice
AAPL APVA10C155.00 155
AAPL APVM10P155.00 155
AAPL APVA10C160.00 160
AAPL APVM10P160.00 160
AAPL APVA10C165.00 165
AAPL APVM10P165.00 165
AAPL APVD10C160.00 160
AAPL APVP10P160.00 160
AAPL APVD10C165.00 165
AAPL APVP10P165.00 165
AAPL APVD10C155.00 155
AAPL APVP10P155.00 155
AAPL APVB10C155.00 155
AAPL APVN10P155.00 155
AAPL APVB10C160.00 160
AAPL APVN10P160.00 160
AAPL APVB10C165.00 165
AAPL APVN10P165.00 165
GS GPYA10C155.00 155
GS GPYM10P155.00 155
GS GPYA10C160.00 160
GS GPYM10P160.00 160
GS GPYA10C165.00 165
GS GPYM10P165.00 165
GS GPYD10C155.00 155
GS GPYP10P155.00 155
GS GPYD10C160.00 160
GS GPYP10P160.00 160
GS GPYD10C165.00 165
GS GPYP10P165.00 165
GS GPYB10C160.00 160
GS GPYN10P160.00 160
GS GPYB10C165.00 165
GS GPYN10P165.00 165
GS GPYB10C155.00 155
GS GPYN10P155.00 155

I am trying to find a query (if it is possible) to show records where:

Underlying=AAPL and StrikePrice is between 150 to 160 (inclusive)

OR

Underlying=GS and StrikePrice is between 160 and 165 (inclusive)

My actual query will have more Underlying (total of 50) with associated
StrikePrice ranges.

The output would like like this:

Underlying ID StrikePrice
AAPL APVA10C155.00 155
AAPL APVM10P155.00 155
AAPL APVD10C155.00 155
AAPL APVP10P155.00 155
AAPL APVB10C155.00 155
AAPL APVN10P155.00 155
GS GPYA10C160.00 160
GS GPYM10P160.00 160
GS GPYA10C165.00 165
GS GPYM10P165.00 165
GS GPYD10C160.00 160
GS GPYP10P160.00 160
GS GPYD10C165.00 165
GS GPYP10P165.00 165
GS GPYB10C160.00 160
GS GPYN10P160.00 160
GS GPYB10C165.00 165
GS GPYN10P165.00 165

You need another table with fields for the Underlying, its
low strike price and high strike price. Then you can use
SQL view to create a query like

SELECT Underlying, ID, StrikePrice
FROM yourtable As T INNER JOIN StrikePriceRange As R
ON T.Underlying = R.Underlying
And T.StrikePrice >= R.LowStrikePrice
And T.StrikePrice < R.HighStrikePrice