如何合并重复的行 [英] How to merge duplicate rows

问题描述

我有一系列包含重复的MemberSS字段的行.这是由于在更改其计划(月列)时会插入新行.

I have a series a rows which have duplicate MemberSS fields. This is due to the fact that a new row is inserted when a change is made to their plan (month columns).

我需要通过以下方式合并重复的行(假设到新表中?)

I need to merge the duplicate rows (assuming into new table?)in the following way:

1. MonthsCover需要求和(两个值之和).
2. 月份列需要将其值合并到一个新行中，以便填写所有月份.

我将如何做到这一点(我还很陌生)?

How would I accomplish this (I'm pretty new to access)?

这是我最初尝试做的事情:

Here is initially what I have tried to do:

SELECT [Copy Of EmployeesDependents].ID, [Copy Of EmployeesDependents].[Member SSN], Sum([Copy Of EmployeesDependents].[Months Covered]) AS [SumOfMonths Covered], Max([Copy Of EmployeesDependents].Jan) AS MaxOfJan, Max([Copy Of EmployeesDependents].Feb) AS MaxOfFeb, Max([Copy Of EmployeesDependents].Mar) AS MaxOfMar, Max([Copy Of EmployeesDependents].Apr) AS MaxOfApr, Min([Copy Of EmployeesDependents].May) AS MinOfMay, Max([Copy Of EmployeesDependents].June) AS MaxOfJune, Max([Copy Of EmployeesDependents].July) AS MaxOfJuly, Max([Copy Of EmployeesDependents].Aug) AS MaxOfAug, Max([Copy Of EmployeesDependents].Sept) AS MaxOfSept, Max([Copy Of EmployeesDependents].Oct) AS MaxOfOct, Max([Copy Of EmployeesDependents].Nov) AS MaxOfNov
FROM [Copy Of EmployeesDependents]
GROUP BY [Copy Of EmployeesDependents].ID, [Copy Of EmployeesDependents].[Member SSN]
HAVING ((([Copy Of EmployeesDependents].[Member SSN]) In (SELECT [Member SSN] FROM [Copy Of EmployeesDependents] As Tmp GROUP BY [Member SSN] HAVING Count(*)>1 )))
ORDER BY [Copy Of EmployeesDependents].[Member SSN];

推荐答案

如果只有一个记录集的每个月列都有一个值，则连接相对简单:

If you only ever have one recordset that has a value in every month-column then the concatenating is relatively simple:

SELECT MemberSS, SUM(MonthsCover), MAX(Jan), MAX(Feb)
FROM EmployeesDependents
GROUP BY EmployeesDependents.MemberSS;

如果最多两个记录集的值在同一月列中，则可以使用FIRST(Jan) & LAST(Jan)或更好的值

If there are two recordsets at most with values in the same month column you could use FIRST(Jan) & LAST(Jan) or better

FIRST(Jan) & IIF(FIRST(Jan)>'' AND LAST(Jan)>'',' - ','') & LAST(Jan)

因此对于具有两个值的情况，您将得到ES - EE.如果每个MemberSS的一列上有两个以上非空字段，则需要访问中不存在的GROUP_CONCAT，但是您可以.

so you'd get ES - EE for the cases where you have two values. If you have more than two non-empty fields on a column per MemberSS you'd need a GROUP_CONCAT which doesn't exist in access, but which you could emulate.

关于是否需要将其添加到新表的问题:这使事情变得更加复杂，因为每次运行查询时，它将所有记录添加到新表中-并且您可以消除重复项稍后或扩展查询，以便考虑上一次运行中已经完成了哪些MemberSS-或者您可以在每次查询之前清除表并完全重建它-这取决于您的用例. SQL将是这样的:

For the question if you'd need to add it to a new table: It makes things more complicated, because it would add all the records to the new table every time you run the query - and you'd either eliminate duplicates later or extend the query so it takes into account which MemberSS are already completed in a previous run - or you could just clear the table before every run of your query and rebuild it entirely - that depends on your use case. The SQL would be something like this:

INSERT INTO concatTable (MemberSS, MonthsCover, Jan, Feb)
SELECT MemberSS, SUM(MonthsCover), MAX(Jan), MAX(Feb)
FROM EmployeesDependents
GROUP BY EmployeesDependents.MemberSS;

一种完全不同的方法是使用VBA，遍历每个记录集，对每个字段进行所需的操作(连接，求和...)，然后将其写回到一个记录中，然后删除另一个记录.

A completely different approach would be to use VBA, iterate over every recordset, do with every field what you like (concatenate, sum ...) and then write it back into one record and delete the other.

这篇关于如何合并重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！