Updating Records with most recent data (1 Viewer)

[Moxioron]

Good morning.

I have put together an import VBA that imports a text file and drops it into a table. There are 10 fields that I am importing and I have the primary key set on five fields to prevent duplicates from being imported.

Everything is working fine with the exception of what I want done with duplicate data.

The primary keys I have set do in fact prevent duplicates, but there are situations where I want the most recent records I am importing to replace the older records.

The data I am importing are new credit cards booked. Occassionally a cardholder will lose or have their card compromised in which case a new card is issued. In this case nine out of the ten fields match with what I already have (the card number being the only field that doesn't match).

What I want to do is have the most recent data, with the new card number, replace the data already on the table.

Is this something I could build into an update or an append query?

Thanks for your help.

 

[MikeLeBen]

Since you mentioned only one field is different, and that field is the card number, I take it you don't have a date field.

You'd use an update query, but how do you discriminate between newer and older data?
If you only ever add new data and there's no risk you import older tables for whatever reason, then you can use a rule like the one in the following pseudo-code:

If (NEWrs.Fields("customerID") = oldrs.Fields("customerID)) And (NEWrs.Fields("cardNbr") <> olrdrs.Fields("cardNbr")) Then
      DoCmd.RunSQL Update query
end if

 

[Moxioron]

Thanks for responding, I appreciate it.

Yes in fact there is a 'report date' that I bring in. I was excited to see your solution until i realized that in some cases, I found one, the older report date has the newer card number.

So I will have to do a duplicate query and delete the closed cards.

Thanks again.

 

[MikeLeBen]

Glad I could be of help