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
|
|||
|
|||
Delete query
I have a table that has 20 fields and might contain duplicate information
based on 3 fields(ID, LoanNum, date). How do I create a delete query that would look at each record and based on those 3 fields delete 1 or more of the duplicates and leave the other? e.g., ID: 123 LoanNum: 44444 Date:01/01/2008 ID: 123 LoanNum: 44444 Date:01/01/2008 ID: 123 LoanNum: 44444 Date:03/01/2008 I would like to delete one of the duplicates and leave the other one there so end up with ID: 123 LoanNum: 44444 Date:01/01/2008 ID: 123 LoanNum: 44444 Date:03/01/2008 |
#2
|
|||
|
|||
Delete query
JOM wrote:
I have a table that has 20 fields and might contain duplicate information based on 3 fields(ID, LoanNum, date). How do I create a delete query that would look at each record and based on those 3 fields delete 1 or more of the duplicates and leave the other? e.g., ID: 123 LoanNum: 44444 Date:01/01/2008 ID: 123 LoanNum: 44444 Date:01/01/2008 ID: 123 LoanNum: 44444 Date:03/01/2008 I would like to delete one of the duplicates and leave the other one there so end up with ID: 123 LoanNum: 44444 Date:01/01/2008 ID: 123 LoanNum: 44444 Date:03/01/2008 Backup your database. Run a maketable query that inserts unique records into a new table. Delete all the records from your original table Run an append query to append the unique records into your original table Like this: Select ID,LoanNum,[Date],First(field1) As FirstField1, ... First(FieldN) As FirstFieldN Into NewTable From OriginalTable Group By ID,LoanNum,[Date] Delete * from OriginalTable Insert into originaltable (ID,LoanNum,[Date],field1,...fieldn) select ID,LoanNum,[Date],FirstField1, ... FirstFieldN From newtable -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
#3
|
|||
|
|||
Delete query
You cannot do that if the table has no primary key, because there is no way
to indicate which one to keep and which one(s) to delete. Either add a primary key, and use a subquery to indicate which one to keep. More about subqueries: http://allenbrowne.com/subquery-01.html Or it might be easier to depress the total button in query design view. Group By the fields that define "duplicate", and choose First for the other fields. Copy'n'paste the table in the database window to make a duplicate, choosing "structure only" when it asks if you want the data too. Then change this query to an Append query, and append to this new table. The new table contains the de-duplicated data. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JOM" wrote in message ... I have a table that has 20 fields and might contain duplicate information based on 3 fields(ID, LoanNum, date). How do I create a delete query that would look at each record and based on those 3 fields delete 1 or more of the duplicates and leave the other? e.g., ID: 123 LoanNum: 44444 Date:01/01/2008 ID: 123 LoanNum: 44444 Date:01/01/2008 ID: 123 LoanNum: 44444 Date:03/01/2008 I would like to delete one of the duplicates and leave the other one there so end up with ID: 123 LoanNum: 44444 Date:01/01/2008 ID: 123 LoanNum: 44444 Date:03/01/2008 |
Thread Tools | |
Display Modes | |
|
|