Access: shifting fields data based on the data present in the other fields

[Adria]

Hello,

Attached is my table data.
For ID=1 and Name=AAA, Code3 is blank. I would like to move Code4 data to Code3 as there is already data in Code1 and Code2. Now Code 3 should have value 1114 whereas Code4 should be blank.
For ID=2 and Name=BBB, Code1 should be 2222, Code2 should be 2223, Code3 should be 2224 and Code4 should be blank
For ID=3 and Name=CCC, Code1 should be 3333 where as Code2, Code3 and Code4 should be blank.
For ID=4 and Name=DDD, Code1 should be 4444 where as Code2, Code3 and Code4 should be blank.

Please advise how to check the blanks of the fields and move the data when present from one field to prior field when there is no data.

Thanks in advance.

[ItsMe]

Perhaps it would be easiest to review some of the rules on Normalization.

[June7]

Normalizing this data, although perhaps best, won't entirely solve this issue.

A CROSSTAB query can pivot normalized data, however, would need another field with a sequential identifier (1-4) for each [Name] grouping ('ranking' records, review http://allenbrowne.com/ranking.html).

This identifier might be able to be calculated and then a UNION query can normalize data and then use that query in CROSSTAB.

Here is an example that calculates sequential identifier for Code1 and Code2 (pretending there are only 2 Code_ fields to deal with).

Query1:
SELECT Table1.ID, Table1.Name, IIf([Code1] Is Null,Null,1) AS SeqID1, Table1.Code1, IIf([Code1] Is Null And Not [Code2] Is Null,1,IIf(Not [Code1] Is Null And Not [Code2] Is Null,2,Null)) AS SeqID2, Table1.Code2 FROM Table1;

Query2:
SELECT ID, SeqID1 AS SeqID, Code1 AS Code FROM Query1
UNI ON SELECT ID, SeqID2, Code2 FROM Query1;

Query3:
TRANSFORM Max(Query2.Code) AS MaxOfCode
SELECT Query2.ID
FROM Query2
GROUP BY Query2.ID
PIVOT Query2.SeqID;

As you can see, the sequential identifier calculation will become more complicated as more Code_ fields are added. This process might need VBA custom function. Which could then possibly negate the need for Query2 and Query3. Calling a VBA custom function can significantly slow down query. Even the logic for VBA code will not be simple. Code like:

Public Function GetCode(v1, v2, v3, v4, intC As Integer) As Variant
    Select Case intC
        Case 1
            GetCode = Nz(v1, Nz(v2, Nz(v3, v4)))
        Case 2
            If Not IsNull(v1) Then
                GetCode = Nz(v2, Nz(v3, v4))
            ElseIf IsNull(v1) And Not IsNull(v2) Then
                GetCode = Nz(v3, v4)
            End If
        Case 3
            If Not IsNull(v1) And Not IsNull(v2) Then
                GetCode = Nz(v3, v4)
            ElseIf IsNull(v1) And Not IsNull(v2) Then
                GetCode = v4
            End If
        Case 4
            If Not IsNull(v1) And Not IsNull(v2) And Not IsNull(v3) Then
                GetCode = v4
            End If
    End Select
End Function

Call code in query:
SELECT Table1.ID, Table1.Name, Table1.Code1, Table1.Code2, Table1.Code3, Table1.Code4, GetCode([Code1],[Code2],[Code3],[Code4],1) AS 1, GetCode([Code1],[Code2],[Code3],[Code4],2) AS 2, GetCode([Code1],[Code2],[Code3],[Code4],3) AS 3, GetCode([Code1],[Code2],[Code3],[Code4],4) AS 4
FROM Table1;

This is just a start. I know the code doesn't yet handle all variations. Still analyzing.

This code seems to work better but nope, still not dealing with all variations:

Public Function GetCode(v1, v2, v3, v4, intC As Integer) As Variant
Dim intP As Integer
intP = IIf(IsNull(v1), 0, 1) + IIf(IsNull(v2), 0, 1) + IIf(IsNull(v3), 0, 1) + IIf(IsNull(v4), 0, 1)
    Select Case intC
        Case 1
            GetCode = Nz(v1, Nz(v2, Nz(v3, v4)))
        Case 2
            GetCode = Choose(intP, Null, Nz(v2, Nz(v3, v4)), v2, v2)
        Case 3
            GetCode = Choose(intP, Null, Null, Nz(v3, v4), v3)
        Case 4
            GetCode = Choose(intP, Null, Null, Null, v4)
    End Select
End Function

Another attempt that seems to really work:

Public Function GetCode(v1, v2, v3, v4, intC As Integer) As Variant
    Dim aryS As Variant, intP As Integer, strS As String
    strS = Nz(v1, ",") & "," & Nz(v2, ",") & "," & Nz(v3, ",") & "," & v4
    strS = Replace(strS, ",,", "")
    aryS = Split(strS, ",")
    If UBound(aryS) >= 0 Then
        intP = UBound(aryS) + 1
        Select Case intC
            Case 1
                GetCode = aryS(0)
            Case 2
                If intP > 1 Then GetCode = aryS(1)
            Case 3
                If intP > 2 Then GetCode = aryS(2)
            Case 4
                If intP > 3 Then GetCode = aryS(3)
        End Select
    End If
End Function

I can now see a non-VBA approach but need to know more about your data in the Code_ fields. Is the example a representation of your actual data? Will number of digits never exceed 4? Assuming yes, consider:

SELECT Table1.ID, Table1.Name, Table1.Code1, Table1.Code2, Table1.Code3, Table1.Code4, Replace(Replace(Format(Nz([Code1],0),"0000") & "," & Format(Nz([Code2],0),"0000") & "," & Format(Nz([Code3],0),"0000") & "," & Format(Nz([Code4],0),"0000"),"0000,",""),",0000","") AS strS, Left([strS],4) AS 1, Mid([strS],6,4) AS 2, Mid([strS],11,4) AS 3, Mid([strS],16,4) AS 4
FROM Table1;

Or

SELECT Table1.ID, Table1.Name, Table1.Code1, Table1.Code2, Table1.Code3, Table1.Code4, Replace(Format(Nz([Code1],0),"0000") & Format(Nz([Code2],0),"0000") & Format(Nz([Code3],0),"0000") & Format(Nz([Code4],0),"0000"),"0000","") AS strS, Left([strS],4) AS 1, Mid([strS],5,4) AS 2, Mid([strS],9,4) AS 3, Mid([strS],13,4) AS 4
FROM Table1;


Name is a reserved word. Should not use reserved words as names for anything.