Include company names with empty fields 1

[straatlang]

I want to create query from three Tables Marketprices, security and currency ?


Marketprices

SECURITY PRICEDAT PRICE
ABC 12/12/02 10
ABC 13/12/02 11
ABC 14/12/02 10
DEF 15/12/02 20
GHJ 13/12/02 21

Securities

SECURITY NAME
ABC ABC Company
DEF DEF Company
GHI GHI Company
JKL JKL Company
MNO MNO Company

Currency

SECURITY CURR
ABC EUR
DEF USD
GHI USD
JKL USD
STU USD
ZZZ EUR


The result should be the last know price and pricedate on every security but i also want to see every security and the name of the company and currency when available in the report including the ones without a date and price field.

Expected result

SECURITY DATE PRICE CURR
ABC 14/12/02 10 EUR
DEF 15/12/02 20 USD
GHI 16/12/02 25 USD
JKL --/--/-- -- USD
MNO --/--/-- -- --
STU --/--/-- -- USD
ZZZ --/--/-- -- EUR

The SQL i have so far (thanks to swampBoogie) is:

select mp.security, mp.pricedate, mp.price, s.name from marketprices mp inner join secnames s on mp.security = s.security where pricedate =(select max(pricedate) from marketprices where security = mp.security)

 

[jdegeorge]

Hi:

I created a database with the table field structures you put in your example. I then created a query linking the SECURITY table to the MARKETPRICES and CURRENCY tables, where SECURITY information shows even if there isn't a MARKETPRICE record.

Create a new query. Click on SQL VIEW and enter this text:

SELECT Securities.SECURITY, Securities.NAME, Marketprices.PRICEDAT, Marketprices.PRICE, Currency.CURRENCY
FROM [Currency] RIGHT JOIN (Securities LEFT JOIN Marketprices ON Securities.SECURITY = Marketprices.SECURITY) ON Currency.SECURITY = Securities.SECURITY;

Click on DESIGN VIEW and you'll see what I mean.

Hope this helps.

Jim

"Get it right the first time, that's the main thing..."

 

[jdegeorge]

Here's what the results would look like:

SECURITY NAME PRICEDAT PRICE CURRENCY
ABC ABC Company 09/12/2002 10 EUR
ABC ABC Company 10/12/2002 11 EUR
ABC ABC Company 12/12/2002 10 EUR
DEF DEF Company 12/12/2002 20 USD
GHI GHI Company USD
JKL JKL Company USD
MNO MNO Company

"Get it right the first time, that's the main thing..."

 

[straatlang]

Perfect. You have got it right the first time.

Thank you very much.


Regards


Straatlang

 

[jdegeorge]

Straatlang:

You're very welcome! Thanks for the star...it's my first!

Jim

"Get it right the first time, that's the main thing..."