0

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 needs to sum (be a sum of the two values).
  2. 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)?

enter image description here

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];
MissioDei
  • 809
  • 2
  • 12
  • 19

2 Answers2

0

This will roll up and sum for you...

Select [Member SS], SUM(MonthsCover) from yourTable Group by [Member SS];

or you can combine that with an append query:

INSERT INTO newTable SELECT [Months SS], Sum(MonthsCover) FROM yourTable GROUP BY [Member SS];

nicomp
  • 4,344
  • 4
  • 27
  • 60
0

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.

Community
  • 1
  • 1