Help with query

[udigold1]

Hi there,
I want to know if there's a way to brake-down to components a data table according to weight?
I have a portfolio of equities and etf's (table 1) , and I want that every ETF in it will be 'opened' according to the weights (table 3) of its related index (table 2) , so I'll get a table like in the 'Desired Query'.
Is that doable?

Thanks in advance


Table 1

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Portfolio components[/TD]
[TD]Balance[/TD]
[/TR]
[TR]
[TD]SPDR[/TD]
[TD]10,000[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[TD]5,000[/TD]
[/TR]
[TR]
[TD]MSFT[/TD]
[TD]4,000[/TD]
[/TR]
[TR]
[TD]QQQ[/TD]
[TD]3,000[/TD]
[/TR]
</tbody>[/TABLE]

Table 2

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Portfolio components[/TD]
[TD]Linked Index[/TD]
[/TR]
[TR]
[TD]SPDR[/TD]
[TD]S&P500[/TD]
[/TR]
[TR]
[TD]QQQ[/TD]
[TD]Nasdaq100[/TD]
[/TR]
</tbody>[/TABLE]

Table 4
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Index[/TD]
[TD]Sector[/TD]
[TD]Weight[/TD]
[/TR]
[TR]
[TD]S&P500[/TD]
[TD]Technology[/TD]
[TD]20%[/TD]
[/TR]
[TR]
[TD]S&P500[/TD]
[TD]Energy[/TD]
[TD]40%[/TD]
[/TR]
[TR]
[TD]S&P500[/TD]
[TD]Consumer Staples[/TD]
[TD]30%[/TD]
[/TR]
[TR]
[TD]S&P500[/TD]
[TD]Consumer Discretionary[/TD]
[TD]10%[/TD]
[/TR]
[TR]
[TD]Nasdaq100[/TD]
[TD]Technology[/TD]
[TD]15%[/TD]
[/TR]
[TR]
[TD]Nasdaq100[/TD]
[TD]Energy[/TD]
[TD]15%[/TD]
[/TR]
[TR]
[TD]Nasdaq100[/TD]
[TD]Consumer Staples[/TD]
[TD]30%[/TD]
[/TR]
[TR]
[TD]Nasdaq100[/TD]
[TD]Consumer Discretionary[/TD]
[TD]40%[/TD]
[/TR]
</tbody>[/TABLE]


Desired Query result

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Portfolio components[/TD]
[TD]Balance[/TD]
[/TR]
[TR]
[TD]SPDR -Technology[/TD]
[TD]2,000[/TD]
[/TR]
[TR]
[TD]SPDR -Energy[/TD]
[TD]4,000[/TD]
[/TR]
[TR]
[TD]SPDR -Consumer Staples[/TD]
[TD]3,000[/TD]
[/TR]
[TR]
[TD]SPDR -Consumer Discretionary[/TD]
[TD]1,000[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[TD]5,000[/TD]
[/TR]
[TR]
[TD]MSFT[/TD]
[TD]4,000[/TD]
[/TR]
[TR]
[TD]QQQ -Technology[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]QQQ -Energy[/TD]
[TD]450[/TD]
[/TR]
[TR]
[TD]QQQ -Consumer Staples[/TD]
[TD]450[/TD]
[/TR]
[TR]
[TD]QQQ -Consumer Discretionary[/TD]
[TD]900[/TD]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

Yes, you would join the two tables (with an inner join in sql or by dragging one field to the other from the table windows in design view of the query).

 

[welshgasman]

I think your QQQ are way out?
450
450
900
1200

 

[udigold1]

you are right I miss copied it.

 

[welshgasman]

So I would join Table 1 with Table 2, Table 2 to Table 4 and create a calculated field of table4.weight * Table1.balance, concatenate Table2.PortfolioComponents and Table4.Sector

HTH

 

[udigold1]

Hi, I did the following select statement but it only returned results for stocks that appear in table 2.

SELECT [Table1].[balance]*[table4].[weight] AS Expr1, Table1.id_num
FROM (Table1 INNER JOIN Table2 ON Table1.id_num = Table2.id_num) INNER JOIN Table4 ON Table2.index_id = Table4.index_id;

 

[welshgasman]

Try changing the join type, the default I believe is only records that match in both tables.
Otherwise I'd try a UNION with records that are not in table 2.

You might have to do two queries then, the first to get everything in table 1 and two. Then a second to link 2 and 4.

HTH

Hi, I did the following select statement but it only returned results for stocks that appear in table 2.

SELECT [Table1].[balance]*[table4].[weight] AS Expr1, Table1.id_num
FROM (Table1 INNER JOIN Table2 ON Table1.id_num = Table2.id_num) INNER JOIN Table4 ON Table2.index_id = Table4.index_id;

 

[udigold1]

Got it! thank you!
it's a union with a left join

SELECT Table1.id_num, Table1.shovi*table3.weight AS Expr1
FROM Table1 INNER JOIN (Table3 INNER JOIN Table2 ON Table3.index_id = Table2.index_id) ON Table1.id_num = Table2.id_num
union all
SELECT Table1.id_num, Table1.shovi 
FROM Table1 
LEFT JOIN Table2 ON Table1.id_num = Table2.id_num
WHERE (((Table2.id_num) Is Null));