How to Combine Separate Records by Selected Fields

Question

A bit of a confusing scenario, but any insight is greatly appreciated! I am trying to combine separate records if the ID from a [POSITION] table matches the ID in a different record also from the [POSITION] table. (I have listed the Tables below)

Essentially, I am using a field (ModAction) within the [POSITION_MOD] Table to decide if a particular ModNumber and ModDate is a Mod which "Adds" a Position, or a Mod which "Removes" A position.

Example of Current Query Results (Simplified)

+-------------+-----------+-------------+------------+--------------+
| Position_ID |  AddedMod |  AddedDate  | RemovedMod | RemovedDate  |
+-------------+-----------+-------------+------------+--------------+
|           1 | 100       | 2019/09/17  | Null       | Null         |
|           1 | Null      | Null        | 150        | 2019/05/08   |
+-------------+-----------+-------------+------------+--------------+

As you can See, two separate records are created when a position has both its Added Mod and its Removed Mod. Ideally, I will be able to combine these two records to be as follows:

ID = 1, AddedMod = 100, AddedDate = 9/17/2019 RemovedMod = 150, RemovedDate = 8/5/2019

For Example: Lets say in the Mod Table I have the following records: Mod_ID = 1, ModNumber = 100, ModDate = 9/17/2019 Mod_Id = 2 ModNumber = 200, ModDate = 8/8/2019

Each of these Mods can add/ remove various different positions. Modnumber 100 can Add Position 5, Add Position 7, and Remove Position 2. The ModAction field is used to Identify whether the Mod associated with the Position is Adding or whether it is Removing the Position.

In the query I have 4 Calculated fields: AddedModNumber, AddedModDate, RemovedModNumber, and RemovedModDate.

I calculate these fields within the Query as Follows (Full Query is Listed Later)

AddedMod: IIf([ModAction]='Added',[ModNumber],'')
AddedDate: IIf([ModAction]='Added',[ModDate],'')
RemovedMod: IIf([ModAction]='Removed',[ModNumber],'')
RemovedDate: IIf([ModAction]='Removed',[ModDate],'')

This gets the ModAction value for the record, then decides which field the Mod should be inserted into (The AddedMod and AddedDate, or the RemovedMod and RemovedDate)

Tables & Database Structure:

[POSITION]: Position_ID, SkillLevel, Category, location, status

[CANDIDATE_POSITION] *(M-M Join between CANDIDATE& POSITION): Position_ID, CANDIDATE_ID, StartDate, EndDate

[CANDIDATE]: CANDIDATE_ID, LastName, FirstName

[MOD]: Mod_ID, ModNumber, ModDate

[POSITION_MOD] (M-M Join Table between POSITION & MOD): Position_ID, Mod_ID, ModAction

I have done quite a bit of searching to try to solve this problem, and have noticed that "Pivot queries" may be a possibility. I am not positive as to how to use these in this situation/ if there is a better solution.

The database structure and functional requirements are what make this so challenging... but this is the most practical way to normalize the data I have found because of the Many-To-Many between a Mod and a Position.

SELECT POSITION.Position_ID AS ID
, Position.Position_ID
, Position.TO
, Candidate.FirstName
, Candidate.LastName
, POSITION.Category
, POSITION.Location
, POSITION.SkillLevel
, CANDIDATE_POSITION.StartDate
, IIf([ModAction]='Added',[ModNumber],'') AS AddedMod
, IIf([ModAction]='Removed',[ModNumber],'') AS RemovedMod
, IIf([ModAction]='Added',[ModDate],'') AS AddedDate
, IIf([ModAction]='Removed',[ModDate],'') AS RemovedDate
FROM ([POSITION] 
LEFT JOIN (CANDIDATE RIGHT JOIN CANDIDATE_POSITION 
     ON CANDIDATE.Candidate_ID = CANDIDATE_POSITION.Candidate_ID) 
     ON POSITION.Position_ID = CANDIDATE_POSITION.Position_ID) 
LEFT JOIN (MOD RIGHT JOIN POSITION_MOD ON [MOD].Mod_ID = POSITION_MOD.Mod_ID) 
     ON POSITION.Position_ID = POSITION_MOD.Position_ID;

Two separate records are returned when a specific position has both its AddedMod/ AddedDate, and its RemovedMod/ RemovedDate. I understand why this is happening, but can't figure out the work-around to get the query working correctly. A simplified query output is listed below

Position_ID = 1, TO = 5, FirstName = John, LastName = Doe, Category = Developer, Location = USA, SkillLevel = 1, StartDate = 1/1/2017, AddedMod = 100, RemovedMod = Null, AddedDate = 7/7/2018, RemovedDate = Null

Position_ID = 1, TO = 5, FirstName = John, LastName = Doe, Category = Developer, Location = USA, SkillLevel = 1, StartDate = 1/1/2017, AddedMod = Null, RemovedMod = 300, AddedDate = Null, RemovedDate = 9/9/2018

The Desired Output

Position_ID = 1, TO = 5, FirstName = John, LastName = Doe, Category = Developer, Location = USA, SkillLevel = 1, StartDate = 1/1/2017, AddedMod = 100, RemovedMod = 300, AddedDate = 7/7/2018, RemovedDate = 9/9/2018

if possible format your data as table, you can use this tool senseful.github.io/text-table. Then you select the text and press control-k to format the thext as code — Juan Carlos Oropeza
If you want one output record per Position_ID, you need to GROUP BY Position_ID and to include a summary operation (such as Max()) on all output fields that are not Position_ID. — Hellion
@Hellion Hi Hellion, Thank you for your response! Are you proposing something like this: SELECT POSITION.Position_ID AS ID , Position.Position_ID , Max(Position.TO , Max(Candidate.FirstName) . . . , Max(CANDIDATE_POSITION.StartDate) , Max(IIf([ModAction]='Added',[ModNumber],'') AS AddedMod) , Max(IIf([ModAction]='Removed',[ModNumber],'') AS RemovedMod) , Max(IIf([ModAction]='Added',[ModDate],'') AS AddedDate) , Max(IIf([ModAction]='Removed',[ModDate],'') AS RemovedDate) FROM ([POSITION] Left Join . . . ON . . . GROUP BY Position.Position_ID; — jake maze

Juan Carlos Oropeza Juan Carlos Oropeza · Accepted Answer · 2019-09-17T15:07:53

If your query is already working and only have 2 results for each id, then you use your query as a sub query for an aggregation SELECT.

SQL DEMO

SELECT `Position_ID`
      , MAX(`Name`) as Name 
      , MAX(`AddedMod`) as AddedMod
      , MAX(`AddedDate`) as AddedDate
      , MAX(`RemovedMod`) as RemovedMod
      , MAX(`RemovedDate`) as RemovedDate
FROM ( .. YourQuery .. ) as t
GROUP BY `Position_ID`

OUTPUT

| Position_ID | Name | AddedMod |  AddedDate | RemovedMod | RemovedDate |
|-------------|------|----------|------------|------------|-------------|
|           1 | Jhon |      100 | 2019/09/17 |        150 |  2019/05/08 |

How to Combine Separate Records by Selected Fields

1 Answers