displaying all records in this query 1

[marksnow]

Hi

I have a form which displays some of the information contained in a quote. The problem is that if there is no description entered for the quote it won't display the quote record at all...

The SQL I have is:

SELECT tblQuote.Date, tblQuote.QuoteID, tblQuote.Status, tblCompany.CompanyName, tblTycoStaff.Name, First(tblItems.Description) AS FirstOfDescription, tblQuote.From, tblQuote.CompanyNo, tblQuote.ContactNo, tblQuote.SalesEngineerNo, tblQuote.FollowupDate
FROM tblTycoStaff INNER JOIN ((tblCompany INNER JOIN tblQuote ON tblCompany.CompanyId = tblQuote.CompanyNo) INNER JOIN tblItems ON tblQuote.QuoteID = tblItems.QuoteId) ON tblTycoStaff.StaffId = tblQuote.From
GROUP BY tblQuote.Date, tblQuote.QuoteID, tblQuote.Status, tblCompany.CompanyName, tblTycoStaff.Name, tblQuote.From, tblQuote.CompanyNo, tblQuote.ContactNo, tblQuote.SalesEngineerNo, tblQuote.FollowupDate
ORDER BY tblQuote.Date DESC , tblQuote.QuoteID DESC;

I have tried many things to get around it not displaying a record if the description is NULL but with no luck.

Any ideas??

 

[swampBoogie]

tblCompany left outer JOIN tblQuote ON 
 tblCompany.CompanyId = tblQuote.CompanyNo) left outer JOIN tblItems ON tblQuote.QuoteID = tblItems.QuoteId)

 

[marksnow]

I have tried the above and it comes up with Join expression not supported!!!

Any other ideas?

 

[Golom]

SwampBoogie's query should be right but I think he has used SQL-Server syntax and not MS/Access syntax. Instead of

Left Outer Join just use LEFT JOIN

 

[marksnow]

Still saying it is not supported My SQl is as follows, changed the section as above.

SELECT tblQuote.Date, tblQuote.QuoteID, tblQuote.Status, tblCompany.CompanyName, tblTycoStaff.Name, First(tblItems.Description) AS FirstOfDescription, tblQuote.From, tblQuote.CompanyNo, tblQuote.ContactNo, tblQuote.SalesEngineerNo, tblQuote.FollowupDate
FROM tblTycoStaff INNER JOIN ((tblCompany LEFT JOIN tblQuote ON tblCompany.CompanyId = tblQuote.CompanyNo) LEFT JOIN tblItems ON tblQuote.QuoteID = tblItems.QuoteId) ON tblTycoStaff.StaffId = tblQuote.From
GROUP BY tblQuote.Date, tblQuote.QuoteID, tblQuote.Status, tblCompany.CompanyName, tblTycoStaff.Name, tblQuote.From, tblQuote.CompanyNo, tblQuote.ContactNo, tblQuote.SalesEngineerNo, tblQuote.FollowupDate
ORDER BY tblQuote.Date DESC , tblQuote.QuoteID DESC;

 

[Golom]

After much searching I found out that Access allows LEFT and RIGHT joins inside INNER joins but not the other way around. My inference (i.e. fancy word for guess) is that a clause like

ON tblTycoStaff.StaffId = tblQuote.From

attempts to create a key-match between a table (tblTycoStaff) participating in an INNER JOIN and another one (tblQuote) participating in a LEFT JOIN. For some reason Access isn't smart enough to handle that.

I flipped the joins around in an attempt to isolate tblItems (the source for the description field) so that it could be LEFT joined ... but no luck.

The best I could do was to create a query that does the LEFT join separately like this.

SELECT tblQuote.Date, tblQuote.QuoteID, tblQuote.Status, First(tblItems.Description) AS FirstOfDescription, tblQuote.From, tblQuote.CompanyNo, tblQuote.ContactNo, tblQuote.SalesEngineerNo, tblQuote.FollowupDate

FROM tblItems LEFT JOIN tblQuote ON tblItems.QuoteId = tblQuote.QuoteID

GROUP BY tblQuote.Date, tblQuote.QuoteID, tblQuote.Status, tblQuote.From, tblQuote.CompanyNo, tblQuote.ContactNo, tblQuote.SalesEngineerNo, tblQuote.FollowupDate

and named it "QData"

Then I modified your query to use that as a table source like this:

SELECT Q.Date, Q.QuoteID, Q.Status, C.CompanyName, T.Name, First(Q.Description) AS FirstOfDescription, Q.From, Q.CompanyNo, Q.ContactNo, Q.SalesEngineerNo, Q.FollowupDate

FROM tblTycoStaff As T INNER JOIN (tblCompany As C INNER JOIN Qdata As Q ON C.CompanyId = Q.CompanyNo) ON T.StaffId = Q.From

GROUP BY Q.Date, Q.QuoteID, Q.Status, C.CompanyName, T.Name, Q.From, Q.CompanyNo, Q.ContactNo, Q.SalesEngineerNo, Q.FollowupDate
ORDER BY Q.Date DESC , Q.QuoteID DESC;

No guarantees but at least Access was able to save both of them without nasty messages.

 

[marksnow]

The above didn't quiet work but all your help has definately helped me solve it. My end solution is:

SELECT tblQuote.Date, tblQuote.QuoteID, tblQuote.Status, tblCompany.CompanyName, tblTycoStaff.Name, First(tblItems.Description) AS FirstOfDescription, tblQuote.From, tblQuote.CompanyNo, tblQuote.ContactNo, tblQuote.SalesEngineerNo, tblQuote.FollowupDate
FROM (tblTycoStaff INNER JOIN (tblCompany INNER JOIN tblQuote ON tblCompany.CompanyId = tblQuote.CompanyNo) ON tblTycoStaff.StaffId = tblQuote.From) LEFT JOIN tblItems ON tblQuote.QuoteID = tblItems.QuoteId
GROUP BY tblQuote.Date, tblQuote.QuoteID, tblQuote.Status, tblCompany.CompanyName, tblTycoStaff.Name, tblQuote.From, tblQuote.CompanyNo, tblQuote.ContactNo, tblQuote.SalesEngineerNo, tblQuote.FollowupDate
ORDER BY tblQuote.Date DESC , tblQuote.QuoteID DESC;


Thanks heaps for your help ppl.