Re: Access query problem

[Bob Field]

This is cleaned up a little bit but it probably still needs some work:

SELECT tabWORKORDER.INVOICE
FROM tabWORKORDER
LEFT JOIN tabBILLING ON tabWORKORDER.INVOICE = tabBILLING.INVOICE
JOIN tabBILLCODES ON tabBILLCODES.CODE = tabWORKORDER.BILLCODE
JOIN tabBILLCODESB ON tabBILLCODESB.CODE = tabWORKORDER.BILLCODE
WHERE (tabWORKORDER.PICKEDUP = "IsNull"
AND tabBILLCODES.REQPURCHASEDATE
AND tabWORKORDER.PURCHASEDATE Is Null
AND tabBILLCODESB.TYPE = tabBILLING.TYPE)
OR (tabWORKORDER.PICKEDUP Is Null
AND tabBILLCODESB.TYPE = tabBILLING.TYPE
AND tabBILLCODES.REQSOLDBY
AND IsNull(tabWORKORDER.SELCODE))
OR (tabWORKORDER.PICKEDUP Is Null
AND tabBILLCODESB.TYPE = tabBILLING.TYPE
AND tabBILLCODES.REQDEALER
AND DEALCODE Is Null)
OR (tabWORKORDER.PICKEDUP Is Null
AND tabBILLCODESB.TYPE = tabBILLING.TYPE
AND tabBILLCODESB.REQCODE
AND tabBILLING.CODE Is Null)
OR (tabWORKORDER.PICKEDUP Is Null
AND tabBILLCODESB.TYPE = tabBILLING.TYPE
AND tabBILLCODESB.REQCONTNUM
AND tabBILLING.CONTNUMBER Is Null)
OR (tabWORKORDER.PICKEDUP Is Null
AND tabBILLCODESB.TYPE = tabBILLING.TYPE
AND tabBILLCODESB.REQAUTH
AND tabBILLING.AUTHORIZATION Is Null)
OR (tabWORKORDER.PICKEDUP Is Null
AND tabBILLCODESB.TYPE = tabBILLING.TYPE
AND tabWORKORDER.SERIAL Is Null)
OR (tabWORKORDER.PICKEDUP Is Null
AND tabBILLCODESB.TYPE = tabBILLING.TYPE
AND tabWORKORDER.MODEL Is Null)
OR (tabWORKORDER.PICKEDUP Is Null
AND tabBILLCODESB.TYPE = tabBILLING.TYPE
AND tabBILLCODESB.REQREINV
AND tabWORKORDER.REINV = 0);

Some of these WHERE clauses look like they're actually join conditions and might clarify things if they could be coded as additional clauses to the appropriate join conditions. This looks straight out of the Access query builder. I find it produces terribly unreadable SQL, doesn't put joins in logical order, and likes lots of extra parentheses.