Access 2007: Delete Partially Duplicate Rows

I have data in an Excel spreadsheet that I import into Access 2007. There is a candidate key (CN). For those rows with the same CN, the data is different for all columns. Example below (real data has 100 columns and MsgNum might vary more often, haven't confirmed this pattern with other instances yet, so although I tried to select on it, the solution should probably ignore that the combination of CN and MsgNum could be unique).

Date       |        CN        |  MsgNum
2012-01-03     111-111-1111       101
2012-01-04     222-222-2222       101
2012-01-05     222-222-2222       202
2012-01-05     333-333-3333       101
2012-01-05     333-333-3333       202
2012-01-04     444-444-4444       101
2012-01-04     444-444-4444       101

I do not have access to SQL Server. All I have is Access 2007. I don't want to use Excel's remove duplicates procedure because the data that gets to me comes from Access before being exported to Excel, so I'm trying to find a solution to remove the duplicates through Access.

Using SQL in Query Design in Access, I have tried using a subquery in the WHERE clause that groups by the CN keeping those with a count of 1, but that removes all instances and does not keep at least one.

I tried selecting just two columns (CN and MIN(MsgNum))--grouping appropriately--and that gives me what I want, but when I run it with all the columns specified (100 columns in all), I get duplicates still.

I tried the Query Wizard Find Duplicates for a single column and to return the rest of the columns, that works in getting the duplicates isolated in a view. Since I cannot setup any primary keys, I am not sure how to join the tables. When running the previous MIN query with all columns, I get the same problem as before.

I was trying to setup something in the WHERE clause that compared the combination of two columns, but I read that that cannot be done. So, I am at a lost on how to solve this issue where there is a candidate key but the information in the records for the duplicates on this candidate key column are different. What I want to have done is what Excel 2007's Remove Duplicates procedure does where duplicates on one column can be removed and retain others.

链接地址: http://www.djcxy.com/p/54026.html

上一篇: Access 2007中的VBA6语法和OPOS打印机OCX驱动程序

下一篇: Access 2007：删除部分重复的行