duplicate field

[slimjen]

All,


Using Access 2003. I have a database with 4 users who import records. When they run the import; it gives them a warning that "some records were not able to append due to key violations. It tells them how many but not what they are. The import may consist of up to 1000 records. They need a report that identifies what the duplicate records are. I know as a developer I can go into the BE table and do a comparision. But how do I set it up to give them a report identifying the specific duplicates so they can make the necessary changes to reimport? I hope I made it clear enough to understand. Thank you

[jzwp11]

I would try to figure out what causes the violations and scrub the data before import.

If that is not an option then...

Using your approach you would have to identify which records were not appended then clean those up and then append only those because if you try to append the whole group again you will end up with duplicated data.

The only way I know of to do a comparison type of report is to import the data into a separate table (probably a local table), do the append to the backend table. You would need some way to flag the records that are being appended so as to know when and from where they came. You would then create a query that pulls those records that were just appended using that flag. From there, you would use the unmatched query wizard to compare the import table records to the records held in the query for those recently appended. You would base your report on the query created by the unmatched query wizard.

[slimjen]

Thank you for the reply. I need to know how to go about doing this because when the append query runs; it does not let me know which records do not append. It just does not append. So how can I tell the database to put those records into a specific table. I can go from there to create a report based on a query using that table. But the key is to have the records that do not append go into that table.
Thanks

[jzwp11]

You said that your users are importing data. Where is the data coming from, a spreadsheet, textfile? How are they doing the import now?


I would do the following in a new database rather than your production database.

1. Duplicate the structure of the destination table of the production database in the new database. Add a new field to the table called flagfield. I would typically use a date field, you may have a different preference
2. Import the records from whatever source into a new table in the new database. Make sure the source is one that yielded the errors you described in your original post.
3. Create a SELECT query that pulls data from the import table. Add a calculated field (to use as a flag) with today's date. Use the function date()

SELECT field1, field2,...date() as flagfield
FROM importtablename

4. Change the query type to an append query and map the fields accordingly to the destination table.
5. Run the append query.
6. Assuming that you get the expected errors, then you would proceed with the following steps. If you do not get the errors, it means that your imported data has data that matches existing records in your production table via some field that is set to not to allow duplicates. You will want to check the field properties in your destination table to see if the restriction is valid
7.Create a query that pulls records from the destination table that have a the correct value in the flagfield (i.e. today's date)
8.Using the Query Wizard in Access, create a "Find unmatched query wizard" using the query from step 7 and your import table. Follow the steps in the wizard to get the query set up. The query will yield the records in the import table that do not exist in the destination table (i.e. the records that were not appended)
9. Create a report from the query created in step 8.

More importantly, you can now analyze the records that were not imported and figure out what caused the problem. You can then use that to clean up any data to be imported before the actual importation takes place.

[slimjen]

The users import from a spreadsheet using a form I set up for them. I use the VBA code below:

Public Function ImportText()
 On Error GoTo Err_ImportText
 
'Delete records from table
 
            DoCmd.RunSQL ("delete from tblSP_SHIPMENT_temp")
            DoCmd.RunSQL ("delete from tblDTF_SHIPMENT_temp")
 
'Import Text files into corresponding tables
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
   "tblSP_SHIPMENT_temp", "S:\NSC Public\FINANCE\ACFAST\UPLOADS\INVOICEREPORT\SMALLPACKAGEIMPORT.xls", True
'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
 
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
    "tblDTF_SHIPMENT_temp", "S:\NSC Public\FINANCE\ACFAST\UPLOADS\INVOICEREPORT\DUTIESANDTAXESIMPORT.xls", True
 
DoCmd.OpenQuery "qry_DailyInvoiceReportAppend"
DoCmd.OpenQuery "qry_DailyInvoiceReportAppend_DTF"
 
MsgBox "Import Completed"
 
Exit_ImportText:
    Exit Function
 
Err_ImportText:
    MsgBox Err.Description
    Resume Exit_ImportText
 
End Function

I'll try your suggestion.