Merging records into one record

Good morning.



I have a complex issue going on in my database. My database was working fine before there was an update to my external data source.

This was my original Append Query



INSERT INTO RLCR4000_CODED ( FY, PayPeriod, ProjCode, DelTask, RegHrs, OTHrs, TotalHrs, FYTDTtlHrs, ProjDesc, TtlCost, FYTDRegHrs, FYTDOTHrs, FYTDTtlCost, TaskDesc, EmpName, ORG, EmpId, CostCtr, PPBegDate, PPEndDate, Level2Nodes, Level3Nodes, Level4Nodes, Level5Nodes, Level6Node )

SELECT RLCR4000_pp5_RAW.[Fiscal Year], RLCR4000_pp5_RAW.[Pay Period], RLCR4000_pp5_RAW.[Delphi Project Code], RLCR4000_pp5_RAW.[Delphi Task], RLCR4000_pp5_RAW.[Regular Hours], RLCR4000_pp5_RAW.[Overtime Hours], RLCR4000_pp5_RAW.[Total Hours], RLCR4000_pp5_RAW.[FYTD Total Hours], RLCR4000_pp5_RAW.[Project Description], RLCR4000_pp5_RAW.[Total Cost], RLCR4000_pp5_RAW.[FYTD Regular Hours], RLCR4000_pp5_RAW.[FYTD Overtime Hours], RLCR4000_pp5_RAW.[FYTD Total Cost], RLCR4000_pp5_RAW.[Task Description], RLCR4000_pp5_RAW.[Emp Name], RLCR4000_pp5_RAW.ORG, RLCR4000_pp5_RAW.[Emp Id], RLCR4000_pp5_RAW.[Cost Center Code], RLCR4000_pp5_RAW.[PP Begin Date (Month DD, YYYY)], RLCR4000_pp5_RAW.[PP End Date (Month DD, YYYY)], RLCR4000_pp5_RAW.[Level 2 Nodes], RLCR4000_pp5_RAW.[Level 3 Nodes], RLCR4000_pp5_RAW.[Level 4 Nodes], RLCR4000_pp5_RAW.[Level 5 Nodes], RLCR4000_pp5_RAW.[Level 6 Nodes]

FROM RLCR4000_pp5_RAW;





The data source changed and the result is now I have a bunch of "Near duplicate" records. I built this query and found the duplicates:



SELECT [RLCR4000_-_Labor_Cost_by_Organi].DelphiProjectCode, [RLCR4000_-_Labor_Cost_by_Organi].DelphiTask, [RLCR4000_-_Labor_Cost_by_Organi].EmpId, [RLCR4000_-_Labor_Cost_by_Organi].CostCenterCode, [RLCR4000_-_Labor_Cost_by_Organi].RecordTypeCode, [RLCR4000_-_Labor_Cost_by_Organi].FiscalYear, [RLCR4000_-_Labor_Cost_by_Organi].PayPeriod, [RLCR4000_-_Labor_Cost_by_Organi].RegularHours, [RLCR4000_-_Labor_Cost_by_Organi].OvertimeHours, [RLCR4000_-_Labor_Cost_by_Organi].TotalHours, [RLCR4000_-_Labor_Cost_by_Organi].FYTDTotalHours, [RLCR4000_-_Labor_Cost_by_Organi].ProjectDescription, [RLCR4000_-_Labor_Cost_by_Organi].TotalCost, [RLCR4000_-_Labor_Cost_by_Organi].FYTDRegularHours, [RLCR4000_-_Labor_Cost_by_Organi].FYTDOvertimeHours, [RLCR4000_-_Labor_Cost_by_Organi].FYTDTotalCost, [RLCR4000_-_Labor_Cost_by_Organi].TaskDescription, [RLCR4000_-_Labor_Cost_by_Organi].EmpName, [RLCR4000_-_Labor_Cost_by_Organi].ORG, [RLCR4000_-_Labor_Cost_by_Organi].PPBeginDate, [RLCR4000_-_Labor_Cost_by_Organi].PPEndDate, [RLCR4000_-_Labor_Cost_by_Organi].Level2Nodes, [RLCR4000_-_Labor_Cost_by_Organi].Level3Nodes, [RLCR4000_-_Labor_Cost_by_Organi].Level4Nodes, [RLCR4000_-_Labor_Cost_by_Organi].Level5Nodes, [RLCR4000_-_Labor_Cost_by_Organi].Level6Nodes, [RLCR4000_-_Labor_Cost_by_Organi].Level7Nodes, [RLCR4000_-_Labor_Cost_by_Organi].Level8Nodes, [RLCR4000_-_Labor_Cost_by_Organi].Level9Nodes, [RLCR4000_-_Labor_Cost_by_Organi].EmployeeCommonId

FROM [RLCR4000_-_Labor_Cost_by_Organi]

WHERE ((([RLCR4000_-_Labor_Cost_by_Organi].DelphiProjectCode) In (SELECT [DelphiProjectCode] FROM [RLCR4000_-_Labor_Cost_by_Organi] As Tmp GROUP BY [DelphiProjectCode],[DelphiTask],[EmpId],[CostCenterCode] HAVING Count(*)>1 And [DelphiTask] = [RLCR4000_-_Labor_Cost_by_Organi].[DelphiTask] And [EmpId] = [RLCR4000_-_Labor_Cost_by_Organi].[EmpId] And [CostCenterCode] = [RLCR4000_-_Labor_Cost_by_Organi].[CostCenterCode])))

ORDER BY [RLCR4000_-_Labor_Cost_by_Organi].DelphiProjectCode, [RLCR4000_-_Labor_Cost_by_Organi].DelphiTask, [RLCR4000_-_Labor_Cost_by_Organi].EmpId, [RLCR4000_-_Labor_Cost_by_Organi].CostCenterCode;





:banghead: Now I am stuck. I need to merge the records into one record set when the following fields are equal

[DelphiProjectCode],[DelphiTask],[EmpId],[CostCenterCode]

AND it must SUM the values in these fields:

RegularHours

OvertimeHours

TotalHours

FYTDTotalHours

TotalCost

FYTDRegularHours

FYTDOvertimeHours

FYTDTotalCost

TotalCost



Could someone help me get started with the next part of my query. NOTE:

There is a field called "RECORDTYPECODE" that was inserted in the external data source. It has one of four values : R, L, N, C



Ideally this is what I want

WHERE ((([RLCR4000_-_Labor_Cost_by_Organi].DelphiProjectCode) In (SELECT [DelphiProjectCode] FROM [RLCR4000_-_Labor_Cost_by_Organi] As Tmp GROUP BY [DelphiProjectCode],[DelphiTask],[EmpId],[CostCenterCode] HAVING Count(*)>1 And [DelphiTask] = [RLCR4000_-_Labor_Cost_by_Organi].[DelphiTask] And [EmpId] = [RLCR4000_-_Labor_Cost_by_Organi].[EmpId] And [CostCenterCode] = [RLCR4000_-_Labor_Cost_by_Organi].[CostCenterCode])))

Then R+L+N+C=

NOTE and in some cases the fields are null



I have attached a sample of the data. And the second tab is desired result. I have over 9000 near duplicates so it is imperative I create a formula to merge them

Attached Files

merge.xls (24.5 KB)

Merging records into one record