missing record if no record in linked sub table

  • Thread starter Thread starter ChrisA
  • Start date Start date
C

ChrisA

I am using a query to create a report. However if there is no record in the
sub table QuoteDetails then I am missing a record. If I take this table out
then the record shows. Have tried changing the join types with no success.
Any suggestions on how to resolve this would be appreciated as I need to see
the record even if it does not have any sub records.
Thanks
Chris

SELECT ProjectQuotes.AtlasTechRep, Project.Status, ProjectQuotes.Quote,
ProjectQuotes.QuoteDate, ProjectQuotes.Application,
ProjectQuotes.SpecialNotes, Project.ProjectName, Project.ProjStreet1,
Project.ProjCity, ProjectQuotes.Surcharge, ProjectQuotes.Expenses,
ProjectQuotes.EngDocLog, ProjectQuotes.Labour, ProjectQuotes.Freight,
ProjectQuotes.validity, Project.StatusMemo, Project.StatusDate,
QuoteDetails.QuoteID
FROM (Project INNER JOIN ProjectQuotes ON Project.ProjectID =
ProjectQuotes.ProjectID) INNER JOIN QuoteDetails ON ProjectQuotes.QuoteID =
QuoteDetails.QuoteID
ORDER BY ProjectQuotes.AtlasTechRep, Project.ProjectName;
 
Chris,

Try:

SELECT PQ.AtlasTechRep, P.Status, PQ.Quote,
PQ.QuoteDate, PQ.Application,
PQ.SpecialNotes, P.ProjectName,
P.ProjStreet1, P.ProjCity, PQ.Surcharge,
PQ.Expenses, PQ.EngDocLog, PQ.Labour,
PQ.Freight, PQ.validity, P.StatusMemo,
P.StatusDate, QD.QuoteID
FROM (Project P LEFT JOIN ProjectQuotes PQ
ON P.ProjectID = PQ.ProjectID)
INNER JOIN QuoteDetails QD
ON PQ.QuoteID = QD.QuoteID
ORDER BY PQ.AtlasTechRep, P.ProjectName;

HTH
Dale
 
Back
Top