Append new/changed data from excel (No Duplicates)

C

crazdandconfusd

New member

Local time

Today, 11:32

Joined

Aug 14, 2011

Messages

5

• Sep 12, 2012

• #1

I've researched this and couldn't find any information. I'm able to import new data from excel just fine, but I can't import updated data from excel due to duplicates not being allowed for a particular field. Is there a way to keep from importing duplicate records based on one field, but still import data from other fields where the information is different from the excel file?

 

John Big Booty

AWF VIP

Local time

Tomorrow, 01:32

Joined

Aug 29, 2005

Messages

8,262

• Sep 12, 2012

• #2

The way I would approach this task is import the Excel sheet into a temporary table. Then use a Find Unmatched query to append the relevant data to your working table.

 

John Big Booty

AWF VIP

Local time

Tomorrow, 01:32

Joined

Aug 29, 2005

Messages

8,262

• Sep 12, 2012

• #3

Here's a DB in which I use that technique. Check the code behind the On Click event of the Continue button on FRM_Import.

Copy both files to your desk top or the same folder.

 

Attachments

• Import.zip

  1.1 MB · Views: 4,049

Last edited: Feb 7, 2013

John Big Booty

AWF VIP

Local time

Tomorrow, 01:32

Joined

Aug 29, 2005

Messages

8,262

• Sep 12, 2012

• #4

In the code you will notice, that there are a couple of steps that massage the data prior to appending it to the working table.

 

C

crazdandconfusd

New member

Local time

Today, 11:32

Joined

Aug 14, 2011

Messages

5

• Sep 12, 2012

• #5

I was able to perform the unmatched query, but got lost with the Event for the Continue button since I'm a novice and don't understand a lot of code. I've attached the database I have with just the table and fieldnames. The first field jmli_rqst_no is the field that is unique and won't allow duplicates, but I wanted to import the other fields with updated information. This is the last hurdle in order to make the database more functional. Thank you for any help with this.

 

Attachments

• Parts Tracker.zip.zip

  150 KB · Views: 207

John Big Booty

AWF VIP

Local time

Tomorrow, 01:32

Joined

Aug 29, 2005

Messages

8,262

• Sep 13, 2012

• #6

Your DB has only one table and on the face of things would seem to have a rather De-Normalised structure. You might want to read up on Data Normalisation and possibly work through a tutorial or two. You may then want to reconsider your data structure and redesign your DB, this in turn may make your task a little easier.