Re: [MS_AccessPros] Lookup Table Query

 

Tim-

This would be easier if your Upside column just had numbers in it (>= can be assumed).

SELECT Ticker, Quality, Upside, (SELECT PositionSize FROM MyTable WHERE Stocks.Quality = MyTable.Quality And MyTable.Upsize = (SELECT Max(Upside) FROM MyTable As T2 WHERE T2.Quality = Stocks.Quality AND T2.Upside <= Stocks.Upside)) As PSize

FROM Stocks;

Access might not like a subquery embedded in a subquery, so another way to do it is:

SELECT Stocks.Ticker, Stocks.Quality, MyTable.PositionSize

FROM Stocks INNER JOIN MyTable

ON Stocks.Quality = MyTable.Quality

WHERE MyTable.Quality = (SELECT Max(Quality) FROM MyTable As T2 WHERE T2.Quality = Stocks.Quality AND T2.Upside <= Stocks.Upside);

Note that I've used "MyTable" as the name of your Quality / Upside / PositionSize table and have assumed that you have removed the >= from the Upside column and just have a numeric column with a percentage value in it.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

On Feb 8, 2014, at 6:36 PM, <timdbui@gmail.com> <timdbui@gmail.com> wrote:

Would any of you please help me with this query?  I have been able to created via a Switch Function, but I wonder if there is a more elegant way to solve this problem:

I have a position matrix table that has 3 columns: Quality (ranging from 1 to 4), Upside (> =10%,>=20%, and >=30%) and the third column named PositionSize that has a series of sized corresponding with the Quality and Upside.

So, If I have list of stocks (about 100 tickers) that have Quality Rating and Upside (for example, IBM has a Quality Rating of 3 and upside of 15%).

How can I write a query to look up the Position Size for the list of these 100 stocks? For the IBM example, since it has a quality of 3 and upside of 15%, the return value should be 1.5.

I have no knowledge of SQL, so would you please show me how to write the code in Access query?

Thanks so much!

Tim

Quality	Upside	PositionSize
1	>= 10%	-
1	>= 20%	0.25
1	>= 30%	0.50
2	>= 10%	0.75
2	>= 20%	1.00
2	>= 30%	1.25
3	>= 10%	1.50
3	>= 20%	1.75
3	>= 30%	2.00
4	>= 10%	2.25
4	>= 20%	2.50
4	>= 30%	3.00

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (2)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___