Query to show records if no value in tables...

[aladrach]

Use criteria on a potentially null field?

OK. So I have the following query:

SELECT dbo_salesordera.RefNumber, dbo_salesorderlinedetail.ItemRef_FullName, Sum(dbo_invoicelinedetail.Quantity) AS SumOfQuantity, dbo_salesorderlinedetail.Quantity, dbo_invoicelinedetail.ItemRef_FullName, dbo_salesorderlinedetail.ClassRef_FullName, dbo_salesorderlinedetail.Desc, dbo_salesordera.BillAddress_Addr1, dbo_salesordera.BillAddress_Addr2, dbo_salesordera.BillAddress_Addr3, dbo_salesordera.BillAddress_Addr4, dbo_salesordera.BillAddress_City, dbo_salesordera.BillAddress_State, dbo_salesordera.BillAddress_PostalCode, dbo_salesordera.BillAddress_Note, dbo_salesordera.ShipAddress_Addr1, dbo_salesordera.ShipAddress_Addr2, dbo_salesordera.ShipAddress_Addr3, dbo_salesordera.ShipAddress_Addr4, dbo_salesordera.ShipAddress_City, dbo_salesordera.ShipAddress_State, dbo_salesordera.ShipAddress_PostalCode, dbo_salesordera.ShipAddress_Note, dbo_salesordera.PONumber, dbo_salesordera.TermsRef_FullName, dbo_salesordera.ShipMethodRef_FullName, dbo_salesordera.FOB, dbo_salesordera.CustomField3, dbo_salesordera.Other, dbo_salesordera.CustomField1, dbo_salesordera.TxnDate, dbo_salesordera.CustomField3, dbo_salesorderlinedetail.CustomField3
FROM (((dbo_salesordera INNER JOIN dbo_salesorderlinedetail ON dbo_salesordera.TxnID = dbo_salesorderlinedetail.IDKEY) LEFT JOIN dbo_linkedtxndetail ON dbo_salesorderlinedetail.IDKEY = dbo_linkedtxndetail.IDKEY) LEFT JOIN dbo_invoice ON dbo_linkedtxndetail.TxnID = dbo_invoice.TxnID) LEFT JOIN dbo_invoicelinedetail ON dbo_invoice.TxnID = dbo_invoicelinedetail.IDKEY
GROUP BY dbo_salesordera.RefNumber, dbo_salesorderlinedetail.ItemRef_FullName, dbo_salesorderlinedetail.Quantity, dbo_invoicelinedetail.ItemRef_FullName, dbo_salesorderlinedetail.ClassRef_FullName, dbo_salesorderlinedetail.Desc, dbo_salesordera.BillAddress_Addr1, dbo_salesordera.BillAddress_Addr2, dbo_salesordera.BillAddress_Addr3, dbo_salesordera.BillAddress_Addr4, dbo_salesordera.BillAddress_City, dbo_salesordera.BillAddress_State, dbo_salesordera.BillAddress_PostalCode, dbo_salesordera.BillAddress_Note, dbo_salesordera.ShipAddress_Addr1, dbo_salesordera.ShipAddress_Addr2, dbo_salesordera.ShipAddress_Addr3, dbo_salesordera.ShipAddress_Addr4, dbo_salesordera.ShipAddress_City, dbo_salesordera.ShipAddress_State, dbo_salesordera.ShipAddress_PostalCode, dbo_salesordera.ShipAddress_Note, dbo_salesordera.PONumber, dbo_salesordera.TermsRef_FullName, dbo_salesordera.ShipMethodRef_FullName, dbo_salesordera.FOB, dbo_salesordera.Other, dbo_salesordera.CustomField1, dbo_salesordera.TxnDate, dbo_salesordera.CustomField3, dbo_salesorderlinedetail.CustomField3, dbo_salesordera.CustomField3
HAVING (((dbo_salesordera.RefNumber)=[Order Number?]) AND ((dbo_invoicelinedetail.ItemRef_FullName)=[dbo_salesorderlinedetail.ItemRef_FullName]) AND ((dbo_salesorderlinedetail.Desc) Is Not Null And (dbo_salesorderlinedetail.Desc)<>"Discount Applied"))
ORDER BY dbo_salesorderlinedetail.ClassRef_FullName, dbo_salesorderlinedetail.Desc;

I know it's something simple. When I run the query, if no corresponding record exists in the linkedtxndetail or invoice tables, it would still show the information from the salesorder tables which will always exist. What do I need to do to fix this?

 

[aladrach]

Anyone? HEEELLPP!!

 

[aladrach]

I found out what causes the problem: it is my criteria:
(dbo_invoicelinedetail.ItemRef_FullName)=[dbo_salesorderlinedetail.ItemRef_FullName])

This is what I use to select the specific matching record. If I remove this, I see all tied records from the Invoice. But it appears this is what is causing the problems. Any suggestions??