Trying to show 0's where the record doesn't exists (1 Viewer)

[April15Hater]

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.

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;

 

[ajetrumpet]

hello again Mr. Accountant.

I am not the expert on right joins, but I know that the EXIST() function is your friend. For example, if I was going to pull records out of a baby table that is based on a parent table and the connected was "ID", and I wanted to display a '0' in every field on the child record if there wasn't any to speak of, I would most likely do this:

SELECT 

IIF(dcount("ID", "secondtable") = 0, "00000", [field1]) as field1
IIF(dcount("ID", "secondtable") = 0, "00000", [field2]) as field2
IIF(dcount("ID", "secondtable") = 0, "00000", [field3]) as field3
IIF(dcount("ID", "secondtable") = 0, "00000", [field4]) as field4

JOINS HERE

and so on...

so...replace your table and field name in the SELECT statement with an IIF() and lookup the value that you want (or DCOUNT it) from it's source based on the ID of the joined record you are currently querying. display a 0 is true and the field if false.

 

[April15Hater]

Hey! Thanks for the input. After leading me in that direction, I think I tinkered enough to get it to work. But that 'exist' clause was the missing ingredient, but QBE makes it look a little funny:

SELECT IIf(IsNull(DLookUp("Cost","tblQuoteDetailContractor","QuoteDetailID = " & 
[tblQuoteDetail].[QuoteDetailID] & " and ContractorID = " & [forms]![frmStep3]!
[lstContractorSel])),0,DLookUp("Cost","tblQuoteDetailContractor",
"QuoteDetailID = " & [tblQuoteDetail].[QuoteDetailID] & " and ContractorID = 1")) 
AS Cost

FROM (Same as Above)

WHERE (((Exists (SELECT * FROM tblQuote WHERE QuoteID =  
forms!frmStep3!txtQuoteID))<>False))

GROUP BY Blah Blah