Query: Joins with data from more than 1 row

[Karja]

Goodmorning. I've have a question about building a query. Basically I have two simple tables:

tbl_Securities
ID
Name

tbl_BloombergInfo
ID
ID_Security (foreignKey)
TypeOfData
Value

The first table consist of all kinds of securitycodes (stocks, bonds, options, cash) etc. etc. The second table consists of all kind of marketdata for a Bloombergterminal (a data vendor).

The type of marketinfo is described is the field [TypeofData] and could be anything (more than 100 types), i.e.: price, costs, outstanding, stock exchange code, currency, excersize price, interest etc. etc. The reason why it is stored in this way is that we receive the data in this particular format. Not extremely nice as some Security_Ids are stored many times in the Bloomberg table.

Now I want in the output a of query to be that in one row there are more [TypeOfData]. Is this possible or should I store the data in a different way? Here and example of what I am looking for:


Security, Price, costs, StockExchange
Security, Price, costs, StockExchange
etc..

Any help appreciated. Regards, Karja

 

[Remou]

Have s look at this:
faq701-4233

 

[Karja]

Thanks for your answer, but I guess this not exactly what I am looking for. I solved the problem by making a query with subqueries for the field I need from the 2nd table. Cheers!