Re: UnMatched query

From: Leif (anonymous_at_discussions.microsoft.com)
Date: 06/30/04 

Date: Tue, 29 Jun 2004 18:03:28 -0700

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
>-----Original Message-----
>A guess that the following idea might get you started.
>
>SELECT TableA.PK, TableB.PK
>FROM TableA INNER JOIN TableB
> ON TableA.FieldA = TableB.FieldB
> OR TableA.FieldA Is Null and TableB.FieldB Is Null
>
>
>
>
>Leif wrote:
>>
>> 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?
>.
>

Relevant Pages

  • Re: is it possible to do a compare in Access 2000
    ... And if you want a combined list of all that are in Table1 and not in table2 plus all that are in table2 and not in table1, you will need to use a UNION Query to combine the two unmatched queries. ... An unmatched query between your two "lists" will help identify those in one and not the other ... ... Technically speaking, if you are actually trying to "compare 2 databases", ...
    (microsoft.public.access.queries)
  • RE: After Unmatched Query Runs, Getting the Difference from Totals
    ... Month's table will have the information from the report I just "ran that day" ... Now, if it was just new items, that would be great - I already have a query ... Months].[Inside Labor]) Is Null ... I've also tried a new query that pulls this Unmatched Query and the Prior ...
    (microsoft.public.access.queries)
  • Re: Unmatched Query Mess
    ... I am going to separate the one field into ... records in the table vs. the query. ... created an "Unmatched Query" comparing the table and the ...
    (microsoft.public.access.queries)
  • Re: Delete query wont run!
    ... DELETE [Track FSMA Serial #s Used].* ... NOT IN (SELECT [qry Create New Track FSMA D]. ... > I'm running this delete query to delete rows from. ... > to use this unmatched query for the criteria for a delete query. ...
    (microsoft.public.access.queries)
  • Re: Finding Unmatched records from two tables
    ... IF just a few you can make your own unmatched query. ... assumes tableOne has the most records) and only matches in table two. ... >>> no one field that has unique data. ...
    (microsoft.public.access.queries)