Getting an outer join to work in access?

[timely]

Okay, I have tried all kind of ways but still no luck. Here is the code I have now. I need for all the products from tbl_ff_portfolio to show up with their percentages, combined with zcustrep to show how much they own already of a given product. What is not showing are the tickers of the products that are need from tbl_ff_portfolio but are not in zcustrep.



SELECT zcustrep.CORRNAME, tbl_ff_portfolio.[Fund Family ID], tbl_ff_portfolio.Portfolio, tbl_ff_portfolio.Ticker, tbl_ff_portfolio.Percent, zcustrep.mktvalue
FROM tbl_ff_portfolio LEFT JOIN zcustrep ON ( tbl_ff_portfolio.Ticker = zcustrep.mktcode)
Where tbl_ff_portfolio.[Fund Family ID]=8 AND tbl_ff_portfolio.Portfolio="7B"
GROUP BY zcustrep.CORRNAME, tbl_ff_portfolio.[Fund Family ID], tbl_ff_portfolio.Portfolio, tbl_ff_portfolio.Ticker, tbl_ff_portfolio.Percent, zcustrep.mktvalue;


CORRNAME Fund Family ID Portfolio Ticker Percent mktvalue
Daniel P. Cassidy 8 7B +ASVIX 15.00% 6044.71
Daniel P. Cassidy 8 7B +ATCIX 10.00% 0
Daniel P. Cassidy 8 7B +BPRXX 10.00% 3621.47
Daniel P. Cassidy 8 7B +BTTRX 15.00% 3209.09
Daniel P. Cassidy 8 7B +BTTTX 15.00% 3194.79
Daniel P. Cassidy 8 7B +TWVLX 10.00% 7477.07

This is an example report and I know on the profolio table that there is another ticker with 15% and one with 10% .

 

[tlbroadbent]

Remove the GROUP BY clause. The GROUP BY will eliminate records that are duplicates. I don't see the purpose of the GROUP BY as you are not aggregating any values. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.