don't import duplicates based on several fields

[icccapital]

I am using the transfertext method and a saved specification in vba to import
some data into a table. Right now if there is a duplicate record it will
import it anyway, but I would like the import to look at 3 fields in
particular and if the imported data has the same information as the table
data I would like it to overwrite the table data. The primary key is an
autonumber that isn't part of the import because it is just the next number.
But the three fields are clientCode, date and security. So if all three of
these match I want to take the new data basically. Is there a way to do this
on import or do I need to run a query after?

thanks

 

[Dale Fye]

I would import the data into a temporary table (table stored in a Temp.mdb
file that is linked to your application. Google on Access +temporary +table
for reasons for doing it this way) linked to your database. Then, I would
write an append query that links the temp table to the table you are
importing into and only imports those records that don't match the three
fields mentioned. Then, I would write an update query that links the temp
table to the table you are importing to, and Update the other fields in the
table where the 3 fields match.

HTH
Dale

 

[icccapital]

Thanks for the information, I have been working through the temporary table
idea and I think I finally have it where the data is being imported into the
temporary table that is linked to the temporary database.

I am now working on the append query and having some trouble with getting
the data to import only those records that don't match on all three fields.

Here is my most recent effort at the sql statement to perform this:

INSERT INTO
Appraisals(ClientCode,ReportDate,SecuritySymbol,SecurityName,Quantity,UnitCost,TotalCost,Price,MarketValue,PercentOfAssets,SecurityType,AssetClass,Sector,InterestRate,MaturityDate)
SELECT
tempAppraisals.ClientCode,tempAppraisals.ReportDate,tempAppraisals.SecuritySymbol,tempAppraisals.SecurityName,tempAppraisals.Quantity,tempAppraisals.UnitCost,tempAppraisals.TotalCost,tempAppraisals.Price,tempAppraisals.MarketValue,tempAppraisals.PercentOfAssets,tempAppraisals.SecurityType,tempAppraisals.AssetClass,tempAppraisals.Sector,tempAppraisals.InterestRate,tempAppraisals.MaturityDate FROM tempAppraisals,Appraisals
Where (tempAppraisals.ClientCode <> Appraisals.ClientCode) AND
(tempAppraisals.ReportDate <> Appraisals.ReportDate) AND
(tempAppraisals.SecuritySymbol <> Appraisals.SecuritySymbol);

But this doesn't append anything to the Appraisals table, my guess is
because there are records that it matches one or two of these criteria on and
I don't know exactly how it checks through each of these. Does it check them
simultaneously? Thanks for the help.

 

[icccapital]

My newest idea that I wanted to see if people thought was a good one was to
run the append query that would append all the data from the temp table into
the original table, then run a delete query on that table to get rid of the
duplicates:

DELETE Appraisals.*
FROM Appraisals
WHERE EXISTS(
SELECT NULL
FROM Appraisals T1
WHERE
(T1.ClientCode)=[Appraisals].[ClientCode] AND
(T1.ReportDate)=[Appraisals].[ReportDate] AND
(T1.SecuritySymbol)=[Appraisals].[SecuritySymbol]
GROUP BY
T1.ClientCode, T1.ReportDate, T1.SecuritySymbol
HAVING
[Appraisals].[ApprID] < MAX(T1.ApprID)
);

What does everyone think? good idea, bad idea, is there a better one?

 

[Dale Fye]

Responding to both of these last two posts. Personally, I think it is a
waste of computing time to import that data, then delete it. I've modified
your query a little, try this:

INSERT INTO Appraisals(ClientCode,ReportDate,SecuritySymbol,
SecurityName, Quantity,UnitCost,TotalCost,
Price,MarketValue,PercentOfAssets,

SecurityType,AssetClass,Sector,InterestRate,
MaturityDate)
SELECT tempAppraisals.ClientCode,tempAppraisals.ReportDate,
tempAppraisals.SecuritySymbol,tempAppraisals.SecurityName,
tempAppraisals.Quantity,tempAppraisals.UnitCost,
tempAppraisals.TotalCost,tempAppraisals.Price,
tempAppraisals.MarketValue,tempAppraisals.PercentOfAssets,
tempAppraisals.SecurityType,tempAppraisals.AssetClass,
tempAppraisals.Sector,tempAppraisals.InterestRate,
tempAppraisals.MaturityDate
FROM tempAppraisals LEFT JOIN Appraisals
On tempAppraisals.ClientCode = Appraisals.ClientCode
AND tempAppraisals.ReportDate = Appraisals.ReportDate
AND tempAppraisals.SecuritySymbol = Appraisals.SecuritySymbol
WHERE Appraisals.ClientCode = NULL

Basically, this is a "Find Unmatched" query that will select all records
from tempAppraisals that don't match Appraisals on all three of the join
fields.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[icccapital]

That's exactly what I was looking to do, thanks. Although I had it
originally as an outer join so I need to read up more on what that does.

But can you tell me what the Where appraisals.clientcode = NULL is doing?
Is it joining them when all 3 are equal and so Where clientcode = null would
be all the others? thanks again for the time.

 

[Dale Fye]

simple explaination using following SQL:

SELECT A.*, B.ID as B_ID
FROM A LEFT JOIN B ON A.ID = B.ID

This query maps the records in A to B based on the [ID] field.
In those instances where there is no match (an ID exists in A, but not in B)
the value in the field [B_ID] will be NULL. If there is a match, then the
value in [B_ID] will be the same is in A.ID.

To identify records that exist in A but not in B, you just add the WHERE
clause:

WHERE B.ID IS NULL

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[icccapital]

Good explanation, very helpful thanks.