如何在删除重复项的同时合并Access中的两个表? [英] How do I merge two tables in Access while removing duplicates?

问题描述

我已经在线阅读了每种可能的解决方案，每次都得到不同的结果.

I have read through about every possible solution online, and I get a different result every time.

我有两个表:Clients和Patrons.它们都具有相同的结构:姓，名，地址，城市，州和邮政编码.客户有108,000个条目，而顾客只有42,000个条目.由于我没有150,000个客户，因此其中一些条目在两者之间重复.

I have two tables: Clients and Patrons. They both have the same structure: LastName, FirstName, Address, City, State, and Zip. Clients has 108,000 entries while Patrons has only 42,000 entries. And some of those entries are duplicated between the two as I don't have 150,000 clients.

我需要一个连贯的清单.我遇到的问题是我的某些客户端位于同一地址，所以我不能简单地删除重复的地址，因为这将删除合法的客户端.简·多伊(Jane Doe)说，我有一些客户的名字很通用，其中有两个在不同的地址，所以我不能只过滤掉重复的姓氏或名字.

I need one coherent list. The problem I am running into is that some of my clients reside at the same address, so I can't simply remove duplicate addresses as that will remove a legitimate client. And I have some clients with very common names, say Jane Doe, where there are a couple of them at different addresses, so I can't just filter out duplicate last or first names.

我正在使用Microsoft Access 2010.

I am using Microsoft Access 2010.

仅将唯一值设置为YES并没有帮助.

Simply turning unique values to YES isn't helping.

我已经搜索了Microsoft帮助文件，得到的结果是2到168,000，以及介于两者之间的大多数内容.

I have scoured the Microsoft help files, and I have gotten results of 2 to 168,000 and most everything in between.

如何获得没有重复的单个列表，而不必按字母顺序排列并逐行输入15万个条目?

How can I get a single list without duplicates without having to alphabetize it and go line by line for 150,000 entries??

推荐答案

UNION查询仅返回不同的行. (也有UNION ALL，但其中将包含重复的行，因此您在这里不希望使用它.)

A UNION query returns only distinct rows. (There is also UNION ALL, but that would include duplicate rows, so you don't want it here.)

尝试此查询.如果没有返回您想要的东西，请解释为什么不符合要求.

Try this query. If it doesn't return what you want, please explain why if falls short.

SELECT LastName, FirstName, Address, City, State, Zip
FROM Clients
UNION
SELECT LastName, FirstName, Address, City, State, Zip
FROM Patrons
ORDER BY LastName, FirstName;

您可能想要ORDER BY中的另一个或多个字段.我刚开始提供了一些东西.

You probably want another field or fields in the ORDER BY. I just offered something to start with.

这篇关于如何在删除重复项的同时合并Access中的两个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！