UnMatched query

  • Thread starter Thread starter Leif
  • Start date Start date
L

Leif

I'm tried to create an unmatched query to determine what
records need to be added to a table. On one side I have a
query that runs against a linked table (records to bring
in). On the other side I have a local table.

The problem I'm having is that fields that have a null
value on both sides I want to consider a match. However,
in SQL, a comparision involving a null value always
returns a null.

I tried adding an nz function on both sides of the equi-
join. However, that makes the query VERY LONG. Without
the nz the query takes 1 minute and 20 seconds. With the
nz I killed the query after it ran more than 1 hour. Of
course, nz also does not allow me to use design mode for
the query anymore.

Any suggestions?
 
A guess that the following idea might get you started.

SELECT TableA.PK, TableB.PK
FROM TableA INNER JOIN TableB
 
John,

Thanks for your reply. Actually I'm using an outer join
since this is for an unmatched query.

I tried your suggestion, however, I got the same result, a
very long running query. However, you did give me an
idea. Since I was getting all the correct records plus
some false unmatches I placed additional checks in the
WHERE clause, instead of the FROM clause, to remove the
false unmatches. That seems to do the trick. I'm getting
the correct results now, and my query runs in 6 seconds.
If you are interested below is my query:

SELECT qryImportDocs.AFC, qryImportDocs.[JEG Proj],
qryImportDocs.[PrintedDoc#], qryImportDocs.[ApcDoc#],
qryImportDocs.Rev, qryImportDocs.Issued_For,
qryImportDocs.Trans_No, qryImportDocs.DocType,
qryImportDocs.DocSubType, qryImportDocs.Title1,
qryImportDocs.Title2, qryImportDocs.Title3,
qryImportDocs.Comments
FROM qryImportDocs LEFT JOIN DrawingList ON (qryImportDocs.
[JEG Proj] = DrawingList.ContractorPrjNo) AND
(qryImportDocs.[PrintedDoc#] = DrawingList.PDN) AND
(qryImportDocs.[ApcDoc#] = DrawingList.APC) AND
(qryImportDocs.Rev = DrawingList.Revision) AND
(qryImportDocs.Issued_For = DrawingList.IssuedFor) AND
(qryImportDocs.Trans_No =
DrawingList.ContractorTransmittal)
WHERE (((nz([JEG Proj]))<>nz([ContractorPrjNo])) AND ((nz
([printedDoc#]))<>nz([PDN])) AND ((nz([ApcDoc#]))<>nz
([APC])) AND ((nz([rev]))<>nz([Revision])) AND ((nz
([Issued_For]))<>nz([IssuedFor])) AND ((nz([Trans_No]))<>nz
([ContractorTransmittal])) AND
((DrawingList.ContractorPrjNo) Is Null));

Thanks for your help.

Regards
Leif
 
Back
Top