如何合并重复的行(How to merge duplicate rows)

如何合并重复的行(How to merge duplicate rows)

我有一系列具有重复的MemberSS字段的行。 这是因为在对其计划（月份列）进行更改时插入了新行。

我需要以下列方式合并重复的行（假设进入新表？）

MonthsCover需要求和（是两个值的总和）。 Month列需要将它们的值组合成一个新行，以便填充所有月份。

我将如何实现这一目标（我很难获得）？

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

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];

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:

MonthsCover needs to sum (be a sum of the two values). Month columns need to combine their values into one new row so that all of the months are filled in.

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];

最满意答案

如果您只有一个记录集在每个月 - 列中具有值，则连接相对简单：

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

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

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

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

对于你是否需要将它添加到新表的问题：它会使事情变得更复杂，因为它会在每次运行查询时将所有记录添加到新表中 - 并且您要么稍后消除重复或延长该查询因此它考虑了在之前的运行中已经完成了哪个MemberSS - 或者您可以在每次运行查询之前清除该表并完全重建它 - 这取决于您的用例。 SQL将是这样的：

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

一种完全不同的方法是使用VBA，迭代每个记录集，对每个字段做你喜欢的事情（连接，求和......）然后将它写回一个记录并删除另一个。

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;

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)

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.

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;

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.