How to create an update query that removes duplicates?

[kilou]

Hi,

I have a linked Excel file which contains many duplicative values. I need to get the unique values of 1 field and put them into an existing table with an update query. I could figure out how to create a select query that removes duplicates but I can't use this as the source to update an existing table since I always get the message: "Operation must use an updateable query". I can create a "create table" query but I'd need an update query since I need to define a primary key on the table that is to be updated.

Is there anyway to do that....and is it possible to do it in 1 phase rather than creating a select query that filters out duplicate and then another query that would update my table?

Thanks a lot

 

[neileg]

It's not clear what you want but I suspect you need an append query, not an update query.

 

[kilou]

No I really need an update query not append. The problem is the following: I need to feed my database with an Extract from Oracle that is updated daily (the Oracle extract is an Excel spreadsheet that is linked in my database. I don't have rights to directly access Oracle via ODBD). However this extract contains some duplicates because I'm working at a "Lot level" while the extract is at the "sublot level" (so one lot can have more than one sublot). Basically I want to extract an updated list of unique batch nr and feed this list to a table in my Access database via an update query (the whole list must be updated daily). So the problem can be summarized as follow:

1. Remove duplicates from the Oracle extract
2. Import/update them into a table with predefined relationship and primary key so that it can be used in Access

So I've been able to create a select query that would give me the unique batch nr and related info but I cannot use it as a source for another update query. Doing so brings me the message "Operation must use an updateable query". Any idea?

EDIT: I can do a "Make table query" that creates a temporary table that has no duplicates and then use that temp table to update my actual table. However this means additional tables and queries. Is there a way to combine everything in VBA (like create the temp table with non duplcative values, then update my actual table, then delete the temp table) with SQL statements directly in VBA so that they don't appear as queries???

 

[jal]

No I really need an update query not append. The problem is the following: I need to feed my database with an Extract from Oracle that is updated daily (the Oracle extract is an Excel spreadsheet that is linked in my database. I don't have rights to directly access Oracle via ODBD). However this extract contains some duplicates because I'm working at a "Lot level" while the extract is at the "sublot level" (so one lot can have more than one sublot). Basically I want to extract an updated list of unique batch nr and feed this list to a table in my Access database via an update query (the whole list must be updated daily). So the problem can be summarized as follow:

1. Remove duplicates from the Oracle extract
2. Import/update them into a table with predefined relationship and primary key so that it can be used in Access

So I've been able to create a select query that would give me the unique batch nr and related info but I cannot use it as a source for another update query. Doing so brings me the message "Operation must use an updateable query". Any idea?

EDIT: I can do a "Make table query" that creates a temporary table that has no duplicates and then use that temp table to update my actual table. However this means additional tables and queries. Is there a way to combine everything in VBA (like create the temp table with non duplcative values, then update my actual table, then delete the temp table) with SQL statements directly in VBA so that they don't appear as queries???

I don't know the Query Grid - I only know SQL. I am guessing that in SQL View you could do something like this:


INSERT INTO DestTable SELECT Field1 FROM ExcelTable GROUP BY Field1


The Group By Clause should serve to remove duplicates in Field1. Therefore change the above names as needed and paste it into SQL View.

And yes, as the other poster told you, this would be an Append Query.

 

[neileg]

Let's get this clear, an append query adds new records, an update query alters existing records. You haven't explained how you want to apply a PK. Are you retaining the 'sublot' data and wanting to link this to the 'lot' data?

 

[kilou]

The PK would be the Lot number and it would be linked to sublots that would be stored in another table. Most of my database would use the Lot number level though and many tables would be linked through the Lot number primary key. I really need an update query and not an append one because data in Oracle are subject to change and I need these changes to be reflected in my Access database. Therefore I need unique lot numbers but I also need to update the information related to these lot numbers if they change in Oracle. I'm trying to figure out a way to do this easily without having to create temporary tables that would make the database grow for no reason.

Basically what I want is transform an Oracle Extract available as an Excel spreadsheet into a 3rd normal form database.

 

[jal]

Your situation now seems more complicated than I realized - I still don't undrstand it, but going back to what you said earlier:

EDIT: I can do a "Make table query" that creates a temporary table that has no duplicates and then use that temp table to update my actual table. However this means additional tables and queries. Is there a way to combine everything in VBA (like create the temp table with non duplcative values, then update my actual table, then delete the temp table) with SQL statements directly in VBA so that they don't appear as queries???

It almost sounds like you haven't done any VBA. I myself have done only a little of it. Here's how to get started, if you haven't done any VBA.

In the object pane, obviously, are objects such as tables, macros, modules, forms. Choose to create a new form. In Design view, you'll see the toolbox. Drop a Command Button on the form. Right click the button and choose Properties. Look for the Event called On_Click, and use the dropdown to select "Event Procedure". Hit the browse button (just to the right of the words Event Procedure) to open up a handler-sub:


Private Sub Command0_Click()

End Sub


This sub will fire every time the user clicks the button, so you can put your VBA code within this block. The basic command to run a query is DoCmd.RunSql stringSql. For example:


Private Sub Command0_Click()
DoCmd.SetWarnings False 'turns off annoying warning messages
DoCmd.RunSQl "SELECT * INTO TempTable FROM tABLE1" 'Creates a temptable

DoCmd.RunSQl "DROP TABLE TempTable" 'deletes the table

MsgBox("Task completed")

End Sub



Is this sort of what you were asking?

 

[neileg]

I'm still not clear on the data structure. However, I'm starting to wonder if you need a lot table at all. If this is simply derived from the sublot data, you could do this with a SELECT DISTICT query.

 

[gemma-the-husky]

if you have table A, linked to table B, with multiple items in table B, then how do you know which items in table B to delete

Are they all the same?

if so then process table B using a record set - based on a query sorted on the key which will determine duplicates


hence
this sort of code logic should process the query and delete the items. not tested, so copy the table before you try it!

lastkey = ""
while not recordset.eof
  thiskey = recordset!key
   if thiskey=lastkey then 'same key as last record ie a duplicate
     recordset.delete
   else
     lastkey=thiskey 'new record, so save the current key
   end if
   recordset.movenext
wend