|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
January 4th, 2010, 05:08 PM
posted to microsoft.public.access.queries
|
|||
|
|||
Query Needed
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 whe 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. 
|
|
#2
January 4th, 2010, 05:25 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Query Needed
Hi Carl,
Sounds like you could use a Query By Form (QBF). Hope that helps... "carl" wrote: 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 whe 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.
|
|
#3
January 4th, 2010, 05:36 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Query Needed
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 ---- HTH Dale "carl" wrote: 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 whe 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.
|
|
#4
January 4th, 2010, 05:41 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Query Needed
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; -- Build a little, test a little. "carl" wrote: 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 whe 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.
|
|
#5
January 4th, 2010, 06:00 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Query Needed
On Mon, 4 Jan 2010 09:08:01 -0800, carl
wrote: A sample of my data table looks as so: I am trying to find a query (if it is possible) to show records whe 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) -- John W. Vinson [MVP]
|
|
#6
January 4th, 2010, 06:13 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Query Needed
carl wrote:
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 whe 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 -- Marsh MVP [MS Access]
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
