Merging records into one record (1 Viewer)

[MrsNewy]

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

 

[plog]

I look at data. That means I glossed over your code and explanation (mostly because you drowned the page in it) and went striaght to your file. But your data isn't representative enough. Looking at the 2 sets of data you posted, the desired results can be accomplished with this simple WHERE clause:

WHERE RecordTypeCode='R'

The desired results are simply the first row of the starting data. But I'm pretty sure math of some sort needs to be done with the other records (don't try to explain it to me, if you want me to understand, show it to me with data). So I can't see what needs to be done with the dataset you provided.

But I think your real problem is that you aren't putting this into a properly structured table structure. My advice is to set up the proper structure for this data and then APPEND it to that structure. With that, you can then can use aggregate queries to arrive at the results you want.

 

[MrsNewy]

I look at data. That means I glossed over your code and explanation (mostly because you drowned the page in it) and went striaght to your file. But your data isn't representative enough. Looking at the 2 sets of data you posted, the desired results can be accomplished with this simple WHERE clause:

WHERE RecordTypeCode='R'

The desired results are simply the first row of the starting data. But I'm pretty sure math of some sort needs to be done with the other records (don't try to explain it to me, if you want me to understand, show it to me with data). So I can't see what needs to be done with the dataset you provided.

But I think your real problem is that you aren't putting this into a properly structured table structure. My advice is to set up the proper structure for this data and then APPEND it to that structure. With that, you can then can use aggregate queries to arrive at the results you want.

The problem is that when the record type is N or L or C, I need the values to add to the record where record type is R. I need to know which employee charged a project and task code I don't want to merge all the project and task code values. only when the project, task, employee id and cost center are equal should it merge into one specific record.

 

[plog]

Can you post a more representative file to demonstrate? Include 2 sets like you did before, what you start with and what you hope to end with.

 

[MrsNewy]

Can you post a more representative file to demonstrate? Include 2 sets like you did before, what you start with and what you hope to end with.

Here is a sample set

 

[plog]

From what I see, this has nothing to do with the WHERE clause. It's about adding a GROUP BY clause and using aggregate functions on the fields you want to add together. Check out this link:

https://support.office.com/en-gb/ar...-a-query-430a669b-e7fd-4c4b-b154-8c8dbbe41c8a


Here's the quick version: In Design View of your query, click the Sigma/Summation symbol in the ribbon. This adds a new row beneath your fields that all say "Group By". Under the fields that should be added together, change the "Group By" to "Sum". Then, also change the one underneath [Record Type Code] to "Max". Run that and it should produce what you want.