Delete records from main table that appear in an Error Table (1 Viewer)

Hello all, this is my first post, so thank you for your help in advance.

We use an on-line survey to ask our students to let us know any new medical details they have. I download and convert to excel, and import in to a table called “Current Medical Details”. If a record already exists, the database automatically produces a “Paste Errors” table.

What I would like to do, is delete the records from the current Medical Details table using the records from the Paste Errors table, so that I can re-import the most up to date information.

The two unique fields are “F1” in Paste Errors and “1 Student number:” in Current Medical Details.

Thank you.

This can happen when the datatype used for one or more fields is wrongly interpreted by Access

Instead of pasting the spreadsheet, I would suggest linking to the Excel file then running an append query to import the data into your Access table. This will allow you to control the process

Not only is Colin's suggestion likely to be good, but it has the added feature that if you even temporarily link to that table, you can run a query against it to identify and remove the records you say you want to replace.

Gazzateer said:

Hello all, this is my first post, so thank you for your help in advance.

Click to expand...

Hi. Welcome to AWF!

Gazzateer said:

We use an on-line survey to ask our students to let us know any new medical details they have. I download and convert to excel, and import in to a table called “Current Medical Details”. If a record already exists, the database automatically produces a “Paste Errors” table.

Click to expand...

Another approach to handle this situation is to import the Excel converted data into a "staging" table in Access, so you can compare its contents against what's already in "Current Medical Details" table. You can then clean up the main or staging table first before actually adding the new data permanently.


Just a thought...

@isladogs, thank you for your suggestion, however for what ever reason, our database will not accept an append import of data which is why i have been using it this way.

i have paid meticulous attention to making sure the cell types are equal in access and excel, but still it won't work.

i should also point out, that each student has a unique number, which does not allow for duplicate records, which is why the error table is produced in the first place.

Thanks @theDBguy

The only things that would prevent an append query working are
1. Your database is read only which isn't the case as you can paste
2. The append query is badly designed

As DBG mentioned, the best solution is to
1. Link to your Excel file
2. Import to a staging or buffer table
3. Process the data if necessary then append to the final table and update any existing records if necessary.

I have an extended article on synchronising data on my website that may be useful to you http://www.mendipdatasystems.co.uk/improve-security-2/4594571073