Append Non-Duplicate II

[Guest]

Hello again,

To recap I am trying to create a query that will append new and "updated"
records from an "import" table to a "transaction" table. The import table
will contain "duplicate" records with matching values in each of the fields
for a unique key with an additional field containing the date of the import.
It will also contain records for a unique key with 1 to many fields with
non-matching values as well as records with a new unique key.

In the previous thread we built two simple sample queries with a limited
number of fields. The first query selects from the transactions table the
most recent transaction for each of the existing unique key values. The
second query then compares the weekly import records to the results of the
first query and returns only the records with new unique key or a change in
any one of the data fields.

Once we got that to work the next step was to modify it to fit the larger
production tables and imports. I did this and took a small sample, 45
records, of production data to populate both the import and the transactions
tables. I then changed the values in each of the 30 data fields; changing
only one field per record. I also added 4 new records. I expected to append
34 records but instead the query appended all 49 records. I tested each
criteria in isolation and discovered the 7 date fields were returning more
than just the record with the one changed value.

I tested this with the simple sample and got all kinds of weird results
after adding a date field. Why are the date fields pulling more records than
expected?

sample_selectLastUpdated

SELECT sample_Transactions.ObjectID,
sample_Transactions.Value1,
sample_Transactions.Value2,
sample_Transactions.Memo1,
sample_Transactions.Date1,
sample_Transactions.UpdatedOn
FROM sample_Transactions
WHERE (((sample_Transactions.UpdatedOn)=
(Select Max(X.UpdatedOn)
FROM sample_Transactions AS X
WHERE X.ObjectID = sample_Transactions.ObjectID)));

sample_appendquery

INSERT INTO sample_Transactions ( ObjectID,
Value1,
Value2,
Memo1,
Date1
UpdatedOn )
SELECT SI.ObjectID,
SI.Value1,
SI.Value2,
SI.Memo1,
SI.Date1,
SI.UpdatedOn
FROM sample_Import AS SI
LEFT JOIN sample_selectLastUpdated AS SLU
ON (SI.ObjectID=SLU.ObjectID)
AND (SI.Value1=SLU.Value1)
AND (SI.Value2=SLU.Value2)
AND (Left(SI.Memo1,255)=Left(SLU.Memo1,255))
AND (Right(SI.Memo1,255)=Right(SLU.Memo1,255))
AND (SI.Date1)=(SLU.Date1)
WHERE (((SLU.ObjectID) Is Null));

I'd appreciate any advice you can give.
Thanks in advance,

owp^3

 

[Guest]

Sorry, found a couple of typos in the simple sample. Corrections as follows:

sample_selectLastUpdated

SELECT sample_Transactions.ObjectID,
sample_Transactions.Value1,
sample_Transactions.Value2,
sample_Transactions.Memo1,
sample_Transactions.Date1,
sample_Transactions.UpdatedOn
FROM sample_Transactions
WHERE (((sample_Transactions.UpdatedOn)=
(Select Max(X.UpdatedOn)
FROM sample_Transactions AS X
WHERE X.ObjectID = sample_Transactions.ObjectID)));


sample_appendquery

INSERT INTO sample_Transactions ( ObjectID,
Value1,
Value2,
Memo1,
Date1,
UpdatedOn )
SELECT SI.ObjectID,
SI.Value1,
SI.Value2,
SI.Memo1,
SI.Date1,
SI.UpdatedOn
FROM sample_Import AS SI
LEFT JOIN sample_selectLastUpdated AS SLU
ON (SI.ObjectID=SLU.ObjectID)
AND (SI.Value1=SLU.Value1)
AND (SI.Value2=SLU.Value2)
AND (Left(SI.Memo1,255)=Left(SLU.Memo1,255))
AND (Right(SI.Memo1,255)=Right(SLU.Memo1,255))
AND (SI.Date1=SLU.Date1)
WHERE (((SLU.ObjectID) Is Null));

Thanks

 

[Guest]

Problem solved!!

It seems that null does not equal null and I needed to use the Nz function
in the query to temporarily assign a value that would result in a true.

See the discussion in "outer Join and NULL values...?!?!!!"

Thanks for letting me talk it out. Sometimes that is all it takes.

owp^3