If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
importing data
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? |
#2
|
|||
|
|||
importing data
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" wrote: 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? |
#3
|
|||
|
|||
importing data
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. "Richard" wrote: 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" wrote: 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? |
#4
|
|||
|
|||
importing data
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 john wrote: 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. "Richard" wrote: 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" wrote: 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? |
Thread Tools | |
Display Modes | |
|
|