• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:


Why doesn't my qry show all records from vPortfolio?
Its only returning those where it finds a matching record in vPortfolioTransaction.
  • 4
  • 2
1 Solution
Dale FyeCommented:
I cannot open your database (company policy), but generally it is because you used an INNER JOIN rather than a left join.  I think what you want would look something like:

SELECT P.ID, P.Field2, P.Field3, PT.Field1
FROM vPortfolio as P
LEFT JOIN vPortfolioTransaction as PT
ON P.ID = PT.Portfolio_ID
ShawnGrayAuthor Commented:
Here's the sql view; seems correct to me but still missing records from vPortfolio:

SELECT vPortfolioTransaction.PortfolioID, vPortfolioTransaction.TradeDate, vPortfolioTransaction.TradeAmount, vPortfolioTransaction.TransactionCode
FROM vPortfolioTransaction RIGHT JOIN vPortfolio ON vPortfolioTransaction.PortfolioID = vPortfolio.PortfolioID
WHERE (((vPortfolioTransaction.TradeDate)=#9/25/2013#) AND ((vPortfolioTransaction.TransactionCode)="lo"))
ORDER BY vPortfolioTransaction.PortfolioID;
ShawnGrayAuthor Commented:
If I remove all criteria its ok.
When I add
WHERE (((vPortfolioTransaction.TransactionCode)="lo"))

I removes the [PortfolioID]s that don't have 'lo'
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

ShawnGrayAuthor Commented:
Guess I missed something obvious:

WHERE (((vPortfolioTransaction.TransactionCode)="lo")) OR (((vPortfolioTransaction.TransactionCode) Is Null))
Dale FyeCommented:
Why doesn't my qry show all records from vPortfolio?  How would you know, you don't have any fields from vPortfolio in your SELECT statement.

Then you need to use a subquery on your vPortfolioTransaction table.  Try:

SELECT vPortfolio.PortfolioID
            , sub.PortfolioID
            , sub.TradeDate
            , sub.TradeAmount
            , sub.TransactionCode
FROM vPortfoilo
SELECT PortfolioID, TradeDate, TradeAmount, TransactionCode
FROM vPortfolioTransaction
WHERE TradeDate=#9/25/2013# AND TransactionCode="lo") as Sub
ON vPortfolio.PortfolioID = sub.PortfolioID
ORDER BY vPortfolio.PortfolioID;
ShawnGrayAuthor Commented:
Missed an "OR"' clause in my sql stmt
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now