Actualmente tengo la siguiente función de Access VBA, que funciona como se explica en un pregunta anterior (muy útil para comprender esta pregunta):

Private Function MapFields(tableNameTemp As String, tableName As String, commonField As String, newTableName)

    Dim tableNameFieldCount As Integer
    tableNameFieldCount = GetFieldCount(tableName)
    Dim tableNameFieldsArray() As String
    ReDim tableNameFieldsArray(0 To tableNameFieldCount) As String ' since you cannot Dim against a variable size
    Call GetFields(tableName, tableNameFieldsArray)    

    sqlJoinQuery = "SELECT tbl_grp_by.*, [" + tableName + "].* " & _
    "INTO " + newTableName & _
    " FROM (SELECT Max([" + tableNameTemp + "].[Field1]) as [Field1], " & _
    "Max([" + tableNameTemp + "].[Field2]) as [Field2], " & _
    "Max([" + tableNameTemp + "].[Field3]) as [Field3], " & _
    "[" + tableNameTemp + "].[Field4] as [Field4] " & _
    "FROM [" + tableNameTemp & _
    "] INNER JOIN [" + tableName & _
    "] ON [" + tableNameTemp + "].[" + commonField + "] = [" + tableName + "].[" + commonField + "] " & _
    "GROUP BY [" + tableNameTemp + "].[" + commonField + "]) as tbl_grp_by " & _
    "INNER JOIN [" + tableName & _
    "] ON [" + tableName + "].[" + commonField + "] = tbl_grp_by.[" + commonField + "]"


    Debug.Print sqlJoinQuery
    CurrentDb.Execute sqlJoinQuery

End Function

Sin embargo, en lugar de Field3 que contenga sí o no, en mis datos puede contener uno de varios valores. Para simplificar, digamos que estos valores pueden ser cualquier elemento del siguiente conjunto de cadenas:
(0, >1 million, 0001-0010)

En este caso, la función SQL de acceso Max() no funcionará en Field3 ya que son cadenas con una jerarquía definida por el usuario. Sin embargo, necesito seleccionar el valor más grande. Definiré los valores del más bajo (1) al más alto (3):

  1. 0
  2. >1 million
  3. 0001-0010

Aquí hay un ejemplo de cómo puede aparecer tableNameTemp:

╔════════════════════════╦════════╦════════╦══════════════════════╗
║ Field4 AKA commonField ║ Field1 ║ Field2 ║ Field3               ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  SA12                  ║  No    ║  No    ║  0                   ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  CY84                  ║  No    ║  No    ║  0                   ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  CY84                  ║  Yes   ║  No    ║  0001-0010           ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  CY84                  ║  No    ║  No    ║  >1 million          ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  CY84                  ║  No    ║  Yes   ║  0                   ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  EH09                  ║  Yes   ║  No    ║  >1 million          ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  EH09                  ║  No    ║  No    ║  >1 million          ║
╚════════════════════════╩════════╩════════╩══════════════════════╝

Y dados los valores de ejemplo tableNameTemp anteriores, la siguiente tabla muestra cómo esos valores se asignarían a la tabla tableName. Observe cómo elige el valor más grande para mapear.

╔════════════════════════╦════════╦════════╦══════════════════════╗
║ Field4 AKA commonField ║ Field1 ║ Field2 ║ Field3               ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  SA12                  ║  No    ║  No    ║  0                   ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  CY84                  ║  Yes   ║  Yes   ║  0001-0010           ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  EH09                  ║  Yes   ║  No    ║  >1 million          ║
╚════════════════════════╩════════╩════════╩══════════════════════╝

Como no tengo mucha experiencia con Access SQL ni VBA, no sé cómo puedo hacer que esto suceda. Supongo que puede implicar codificar una lista de los posibles valores para Field3 y luego clasificarlos según su posición en la lista (es decir, la posición 3 contendría el valor 0). Luego, de alguna manera usando esto como una clave para el valor a elegir. Si esto es de alguna utilidad, he proporcionado el código a continuación:

Dim hierarchy(0 to 2) As String
hierarchy(0) = "0001-0010" ' highest value '
hierarchy(1) = ">1 million"
hierarchy(2) = "0"         ' lowest value  '

EDITAR
Código actualizado, según la ayuda de Serg:

sqlJoinQuery = "SELECT tbl_grp_by.[Field1],tbl_grp_by.[Field2], " & _
                        "Switch( " & _
                            "tbl_grp_by.[maxfield3] = 0, '0', " & _
                            "tbl_grp_by.[maxfield3] = 1, '>1 million', " & _
                            "tbl_grp_by.[maxfield3] = 2 '0001-0010' " & _
                        ") as [Field3], " & _
                        "tbl_grp_by.[Field4], " & _
                    "[" + tableName + "].* " & _
                    "INTO [" + newTableName + "] FROM (" & _
                        "SELECT Max([" + tableNameTemp + "].[Field1]) as [Field1], " & _
                            "Max([" + tableNameTemp + "].[Field2]) as [Field2], " & _
                            "Max(Switch( " * _
                                "[" + tableNameTemp + "].[Field3] = '0' , 0, " & _
                                "[" + tableNameTemp + "].[Field3] = '>1 million' , 1, " & _
                                "[" + tableNameTemp + "].[Field3] = '0001-0010', 2 " & _
                            "))as [maxField3], " * _
                            "[" + tableNameTemp + "].[Field4] as [Field4] " * _
                        "FROM [" + tableNameTemp + "] " & _
                        "INNER JOIN [" + tableName + "] " & _
                            "ON [" + tableNameTemp + "].[" + commonField + "] = [" + tableName + "].[" + commonField + "] " & _
                        "GROUP BY [" + tableNameTemp + "].[" + commonField + "] " & _
                    ") as tbl_grp_by " & _
                    "INNER JOIN [" + tableName + "] " & _
                        "ON [" + tableName + "].[" + commonField + "] = tbl_grp_by.[" + commonField + "]"
0
Paradox 11 ago. 2016 a las 20:50

2 respuestas

La mejor respuesta

Codificar valor, decodificar max de esta manera en SQL.

Actualmente está creando un comando SQL como (reemplacé las variables de nombre de tabla con valores arbitrarios, temp y tableName)

SELECT tbl_grp_by.*, [tableName].*  
INTO newTableName 
FROM (
    SELECT Max([temp].[Field1]) as [Field1], 
        Max([temp].[Field2]) as [Field2],  
        Max([temp].[Field3]) as [maxField3], 
        [temp].[Field4] as [Field4]  
    FROM [temp]
    INNER JOIN [tableName ]
       ON [temp].[commonField] = [tableName].[commonField] 
    GROUP BY [temp].[commonField]
 ) as tbl_grp_by  
INNER JOIN [tableName]
  ON [tableName].[commonField] = tbl_grp_by.[commonField]

Constrúyelo como

SELECT tbl_grp_by.[Field1],tbl_grp_by.[Field2],
    Switch( 
        tbl_grp_by.[maxfield3] = 0, '0',
        tbl_grp_by.[maxfield3] = 1, '>1 million',
        tbl_grp_by.[maxfield3] = 2 '0001-0010' 
    ) as [Field3],   
    tbl_grp_by.[Field4],
[tableName].*  
INTO newTableName 
FROM (
    SELECT Max([temp].[Field1]) as [Field1], 
        Max([temp].[Field2]) as [Field2],  
        Max(Switch(  
            [temp].[field3] = '0' , 0,
            [temp].[field3] = '>1 million' , 1,
            [temp].[field3] = '0001-0010', 2  
         ))as [maxField3], 
        [temp].[Field4] as [Field4]  
    FROM [temp]
    INNER JOIN [tableName ]
       ON [temp].[commonField] = [tableName].[commonField] 
    GROUP BY [temp].[commonField]
 ) as tbl_grp_by  
INNER JOIN [tableName]
  ON [tableName].[commonField] = tbl_grp_by.[commonField]   

Entonces [campo3] está codificado en max () en la consulta interna y ese max se decodifica en la consulta externa.

2
Serg 12 ago. 2016 a las 13:06

Consideraría crear una tabla de referencia con un campo de valor, ya que es más fácil de mantener, especialmente cuando los valores cambian con el tiempo.

CREATE TABLE tblReference (field_txt text, val Integer);

Obtenga el field_txt con el valor más alto y el campo único y luego la unión izquierda (unión interna) a su conjunto de datos actual.

qry_field3_max = "SELECT [Field3],[commonField] FROM tblReference INNER JOIN (SELECT [commonField], MAX(val) as val FROM tblReference INNER JOIN tblNameTemp on tblReference.[field_txt]=tblNameTemp.[Field3] Group By [commonField]) as tbl_max_fields on tblReference.val=tbl_max_fields.val"

1
winghei 11 ago. 2016 a las 20:20