Re: [MS_AccessPros] Query and Grouping Question

 

Tim-

This would be so much easier to do if your second table were properly designed!  Instead of columns in a repeating group (the four exposures), it should look like this:

Ticker, ExposureType

.. with one row per Ticker for each type of exposure.

You can get what you want by un-pivoting the second table with a UNION query:

SELECT Ticker, "Interest Rate Sensitive" As Exposure

FROM t_Exposure

WHERE InterestRateSensitive = True

UNION ALL

SELECT Ticker, "Oil Price Sensitive" As Exposure

FROM t_Exposure

WHERE OilPriceSensitive = True

UNION ALL

SELECT Ticker, "Eco Cyclical" As Exposure

FROM t_Exposure

WHERE EcoCyclical = True

UNION ALL

SELECT Ticker, "Government Spending" As Exposure

FROM t_Exposure

WHERE GovernmentSpending = True;

Now join that back with your t_Holding table to get sums of exposures.

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 Sep 25, 2014, at 6:04 PM, timdbui@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi, would any of you please help me to set up this query and grouping issue?

Say, I have a list of stocks and their position weights in table t_Holding (Col 1: Ticker, Col B: Weight (ABC 20%, CDF 15%, GHI 25% MNO 10%, and XYZ 30% to make a total of 100%)

In table t_Exposure I have Col 1: Ticker, Col 2: InterestRateSensitive, Col 3: OilPriceSensitivve, Col 4: EcoCyclical, Col 5: GovernmentSpending). This table consists of the same tickers in t_Holding table but the tickers have yes/no checks on some of the columns. 

Stock ABC have checks (yes/no) on Col 1 and Col 4

Stock CDF has checks on Col 2, col 3,

Stock CDF have checks on col 5

Stock GHI have check on col 2 and col 3

Stock  MNO have checks on Col 2 and col 5,

Stock XYZ have checks on col 4.

How can I group these so that I can see how my portfolio is exposed to different factors.

Of the example above, I would like to see the result grouped by:

InterestRateSensitive group includes stock ABC and a sum of exposure of 20%.

OilPriceSensitive group include stocks CDF, GHI, and MNO and a sum of exposure of 50% (15%+25%+10%)

EcoCyclical group include stocks CDF, GHI and a sum of exposure of 40% (15%+25%)

GovernmentSpending include stocks CDF and MNO and a sum of exposure of 25% (15%10%)

I don't know how to do a query that one stock can be in many "buckets"  (many-to-many query?).

Thanks so much in advance for your instruction!

Tim

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

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

.

__,_._,___