April15Hater
Accountant
- Local time
- Today, 17:26
- Joined
- Sep 12, 2008
- Messages
- 349
Hey guys! Hope you can help pull me out of a bind again.
What I'm trying to do is show all fields of all the records in tblQuoteDetail with a QuoteID of 7. Then show the 'cost' field (stored in tblQuoteDetailContractor) for all related records with ContractorID = 1. If the record does not exist in tblQuoteDetailContractor, I'd like it to just show a 0.
What I'm trying to do is show all fields of all the records in tblQuoteDetail with a QuoteID of 7. Then show the 'cost' field (stored in tblQuoteDetailContractor) for all related records with ContractorID = 1. If the record does not exist in tblQuoteDetailContractor, I'd like it to just show a 0.
Code:
SELECT tblItem.ItemID, tblItem.Code, tblItem.ServiceName, tblQuoteDetail.Unit,
Left([FirstName],1) & ". " & [LastName] AS ContractorName,
tblQuoteDetailContractor.Cost, tblContractor.ContractorID
FROM tblContractor RIGHT JOIN ((tblItem INNER JOIN (tblQuote INNER JOIN
tblQuoteDetail ON tblQuote.QuoteID = tblQuoteDetail.QuoteID) ON tblItem.ItemID
= tblQuoteDetail.ItemID) LEFT JOIN tblQuoteDetailContractor ON tblQuoteDetail.
QuoteDetailID = tblQuoteDetailContractor.QuoteDetailID) ON tblContractor.
ContractorID = tblQuoteDetailContractor.ContractorID
WHERE tblContractor.ContractorID = 1 AND tblQuote.QuoteID = 7
GROUP BY tblItem.ItemID, tblItem.Code, tblItem.ServiceName,
tblQuoteDetail.Unit, Left([FirstName],1) & ". " & [LastName],
tblQuoteDetailContractor.Cost, tblContractor.ContractorID;