importing data

[john]

I have an excel spreadsheet I need to import into a database weekly. I
wanted to create a button on a form to automate this process. I created the
macro and everything works fine however if it's run twice or more it will
import the same data over and over. I'll end up with duplicate data. Does
anyone have any thoughts on how I can limit it to once?

 

[Richard]

The way I filter out dupes is to set a second primary key, example might be
phone numbers they are usually unique. When you try to import the same
spreadsheet you will recive a key violation notice, then you can opt out.


Richard E

 

[john]

The file I'm importing is an employee payroll file. It list every employee
and employee id along with payroll information for the week. The problem is
the only unique info is the name and employee number from record to record
however every week i need to import new data for the same employee. So if I
set the employee name or id as a unique field I won't be able to import next
weeks data. I thought of somehow using the datefield but everyone for that
week has the same date. Then I thought of merging the date field with the
employee id into a new field creating a unique record. I'm not sure if this
if the cleanest way.

 

[John Spencer]

You would create a compound unique index on the Employee Number AND the date
field. You don't need to combine the two fields, the index will take care of
not allowing duplicates. The only problem would be if an employee could have
more than one record on the same date. If that is possible then you would
need to implement a different method or possibly add another field or fields
to the index.

To create a multiple field unique index (Compound index)
--Open up the WorkingTable in design mode
--Select View: Index from the menu
--Enter a name for Index in first blank cell under Index Name
--Select one field in the index under Field Name
--Set Unique to Yes
--Move down one line and select the next FieldName
--Continue moving down and selecting fieldnames until all needed are included.
--Close the index window and close and save the table

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County