Facilitar la lectura de datos de resumen mediante una consulta de tabla de referencias cruzadas

Al usar la vista Diseño para crear la consulta de tabla de referencias cruzadas, puede usar todos los orígenes de registros (tablas y consultas) que desee. Sin embargo, puede hacer que el diseño sea sencillo si primero crea una consulta de selección que devuelva todos los datos que desee y, después, usa dicha consulta como el único origen de registros para la consulta de tabla de referencias cruzadas. Para obtener más información acerca de la creación de una consulta de selección, consulte la sección Vea también.

Al crear una consulta de tabla de referencias cruzadas en la vista Diseño, se usan las filas Total y Tabla de referencias cruzadas de la cuadrícula de diseño para especificar qué valores de campo se convertirán en encabezados de columna, cuáles se convertirán en encabezados de fila y para qué campo se debe proporcionar la suma, promedio, recuento u otro cálculo.

1. La configuración de estas filas determina si el campo es un encabezado de fila, un encabezado de columna o un valor de resumen.

2. Esta configuración muestra los valores del campo como encabezados de fila.

3. Esta configuración muestra los valores del campo como encabezados de columna.

4. Esta configuración produce los valores de resumen.

Crear la consulta

1. En el grupo Consultas de la pestaña Crear, haga clic en Diseño de la consulta.

   

2. En el cuadro de diálogo Mostrar tabla, haga doble clic en cada tabla o consulta que desee usar como origen de registros.

   Si utiliza más de un origen de registros, asegúrese de que las tablas o consultas se combinan por campos que tengan en común. Para obtener más información acerca de la combinación de tablas y consultas, consulte la sección Vea también.

3. Cierre el cuadro de diálogo Mostrar tabla.

4. En la pestaña Diseño, en el grupo Tipo de consulta, haga clic en Tabla de referencias cruzadas.

5. En la ventana de diseño de la consulta, haga doble clic en cada campo que desee usar como origen de encabezados de fila. Puede seleccionar hasta tres campos para los encabezados de fila.

6. En la cuadrícula de diseño de la consulta, en la fila Tabla de referencias cruzadas de cada campo de encabezado de fila, seleccione Encabezado de fila.

   Puede especificar una expresión en la fila Criterios para limitar los resultados de dicho campo. También puede usar la fila Ordenar para especificar un criterio de ordenación para un campo.

7. En la ventana de diseño de la consulta, haga doble clic en el campo que desee usar como origen de encabezados de columna. Para los encabezados de columna, solo puede seleccionar un campo.

8. En la cuadrícula de diseño de la consulta, en la fila Tabla de referencias cruzadas del campo de encabezado de columna, seleccione Encabezado de columna.

   Puede escribir una expresión en la fila Criterios para limitar los resultados del campo de encabezado de columna. Sin embargo, el uso de una expresión de criterios con el campo de encabezado de columna no limita el número de columnas que devuelve la consulta de tabla de referencias cruzadas. En su lugar, limita qué columnas contienen datos. Por ejemplo, suponga que tiene un campo de encabezado de columna con tres valores posibles: rojo, verde y azul. Si aplica el criterio ='azul' al campo de encabezado de columna, la consulta seguirá mostrando una columna para rojo y otra para verde, pero solo la columna de azul contendrá datos.

   Si desea limitar los valores que se muestran como encabezados de columna, puede especificar una lista de valores fijos mediante la propiedad Encabezados de columna de la consulta. Para obtener más información, consulte la siguiente sección.

9. En la ventana de diseño de la consulta, haga doble clic en el campo que desee usar para calcular valores de resumen. Solo puede seleccionar un campo para valores de resumen.

10. En la cuadrícula de diseño de la consulta, en la fila Total del campo de valores de resumen, seleccione una función de agregado que se usará para calcular los valores.

11. En la fila Tabla de referencias cruzadas del campo de valores de resumen, seleccione Valor.

    No se pueden especificar criterios para un campo de valores de resumen ni ordenar por dicho campo.

12. En la pestaña Diseño, en el grupo Resultados, haga clic en Ejecutar.

Especificar valores fijos para los encabezados de columna

Si desea especificar valores fijos para los encabezados de columna, puede establecer la propiedad Encabezados de columna de la consulta.

1. Abra la consulta de tabla de referencias cruzadas en la vista Diseño.

2. Si la hoja de propiedades no está visible, presione F4 para mostrarla.

3. En la hoja de propiedades, justo encima de la pestaña General, asegúrese de que el Tipo de selección es Propiedades de la consulta. Si no lo es, haga clic en un lugar vacío del espacio situado encima de la cuadrícula de diseño de la consulta.

4. En la hoja de propiedades, en la pestaña General, en la propiedad Encabezados de columna, especifique la lista de valores separados por comas que desee usar como encabezados de columna.

   Algunos caracteres (como la mayoría de los signos de puntuación) no están permitidos en los encabezados de columna. Si usa estos caracteres en la lista de valores, Access sustituye cada uno de ellos por el carácter de subrayado (_).

Sintaxis de SQL para una consulta de tabla de referencias cruzadas

Una consulta de tabla de referencias cruzadas se expresa en SQL como una instrucción TRANSFORM. Una instrucción TRANSFORM consta de la siguiente sintaxis:

Transform aggfunction
selectstatement
Campo _dinámico PIVOT [IN (valor1[, valor2[, ...]])]

La instrucción TRANSFORM consta de las siguientes partes:

Parte	Descripción
función_de_agregado	Una función de agregado de SQL que funciona en los datos seleccionados.
instrucción_de_selección	Una instrucción SELECT.
campo_dinámico	El campo o expresión que quiere usar para crear encabezados de columna en el conjunto de resultados de la consulta.
valor1, valor2	Valores fijos que se usan para crear encabezados de columna.

La vista SQL no limita el número de tablas o consultas que puede usar como origen de registros para una consulta de tabla de referencias cruzadas. Sin embargo, puede hacer que el diseño sea sencillo si crea una consulta de selección que devuelva todos los datos que desee usar en la consulta de tabla de referencias cruzadas y, después, usa dicha consulta de selección como el origen de registros. Para obtener más información acerca de la creación de una consulta de selección, consulte la sección Vea también.

1. En el grupo Otros de la pestaña Crear, haga clic en Diseño de consulta.

2. Cierre el cuadro de diálogo Mostrar tabla.

3. En la pestaña Diseño, en el grupo Vistas, haga clic en Ver y, luego, haga clic en Vista SQL.

4. En la pestaña Objeto SQL, escriba o pegue la siguiente instrucción SQL:

   TRANSFORM 
   SELECT 
   FROM 
   GROUP BY 
   PIVOT 
   ;
   

5. En la primera línea, después TRANSFORM, escriba una expresión para calcular valores de resumen, por ejemplo, Suma([Cantidad]).

   Si usa más de una tabla o más de una consulta como origen de registros, incluya el nombre de la tabla o la consulta como parte del nombre de cada campo. Por ejemplo, Suma([Gastos].[Cantidad]).

6. En la segunda línea, después de SELECT, escriba una lista de campos o expresiones de campo que desee usar para los encabezados de fila. Separe los elementos de la lista con comas; por ejemplo, [Presupuesto].[Dept_ID], [Gastos]. [Tipo].

7. En la tercera línea, después de FROM, escriba una lista de las tablas o consultas que va a usar como origen de registros. Por ejemplo, Presupuesto, Gastos.

8. En la cuarta línea, después de GROUP BY, escriba la misma lista de campos que ha usado en la cláusula SELECT, en el paso 6.

9. En la quinta línea, después de PIVOT, escriba un nombre de campo o una expresión que desee usar para encabezados de columna. Por ejemplo, PIVOT [Presupuesto].[Año].

En ocasiones, en lugar de usar todos los valores de un campo para los encabezados de fila o columna, desea agrupar los valores de un campo en intervalos y, después, usar dichos intervalos para los encabezados de fila o columna. Por ejemplo, suponga que usa un campo “Edad” para los encabezados de columna. En lugar de usar una columna para cada edad, puede ser preferible usar columnas que representen intervalos de edad.

Puede usar la función IIF en una expresión para crear intervalos para los encabezados de fila o columna.

1. Abra la consulta de tabla de referencias cruzadas en la vista Diseño.

2. En la cuadrícula de diseño de la consulta, en la fila Campo, haga clic con el botón secundario en una columna vacía y, después, haga clic en Zoom en el menú contextual.

3. En el cuadro Zoom, escriba un alias de campo seguido de dos puntos (:).

4. Escriba IIf().

5. Entre los paréntesis que siguen a IIf, escriba una expresión de comparación que defina el primer intervalo de valores del campo.

   Por ejemplo, suponga que va a crear intervalos para un campo Edad y desea que cada intervalo sea de veinte años. La expresión de comparación del primer intervalo es [Edad]<21.

6. Escriba una coma detrás de la expresión de comparación y, después, escriba un nombre para el intervalo entre comillas. El nombre que proporcione será el encabezado de tabla de referencias cruzadas para los valores comprendidos en el intervalo.

   Por ejemplo, detrás de [Edad]<21, escriba una coma y, después, escriba "0-20 años".

7. Escriba una coma detrás del nombre del intervalo (fuera de las comillas) y, después, realice uno de los siguientes procedimientos:

   • Para crear otro intervalo, escriba IIf() y, después, repita los pasos 5, 6 y 7.

   • Para el último intervalo, escriba solo un nombre para el intervalo.

     Por ejemplo, una expresión IIf anidada completa que separa un campo Edad en intervalos de veinte años puede tener el siguiente aspecto (se han agregado saltos de línea para facilitar la legibilidad):

     IIf([Age]<21,"0-20 years",
      IIf([Age]<41,"21-40 years",
      IIf([Age]<61,"41-60 years",
      IIf([Age]<81,"61-80 years", "80+ years"))))
     

     Nota: Cuando Access evalúa la expresión, se interrumpe en cuanto una de las instrucciones IIf se evalúa como True. No es necesario especificar el extremo inferior de cada intervalo, ya que cualquier valor por debajo del extremo inferior de un intervalo determinado ya se habría evaluado como True.

8. En la cuadrícula de diseño de la consulta, en la fila Total, seleccione Agrupar por.

9. En la fila Tabla de referencias cruzadas, especifique si se deben usar los intervalos como encabezados de fila o encabezados de columna. Recuerde que puede especificar entre uno y tres encabezados de fila, y un encabezado de columna.

Puede que desee que la consulta de tabla de referencias cruzadas solicite información cuando se ejecute. Por ejemplo, suponga que usa varios encabezados de fila, uno de los cuales es País o región. En lugar de mostrar siempre los datos de cada país o región, puede que desee que la consulta solicite un nombre y, después, se muestren los datos en función de la información del usuario.

Puede agregar un mensaje de solicitud de parámetros a cualquier campo de encabezado de fila.

1. Abra la consulta de tabla de referencias cruzadas en la vista Diseño.

2. En la fila Criterios del campo de encabezado de fila para el que desee solicitar información al usuario, escriba el texto de la pregunta entre corchetes. El texto aparecerá como un mensaje al ejecutar la consulta.

   Por ejemplo, si escribe [¿Qué país o región?] en la fila Criterios, cuando se ejecute la consulta, aparecerá un cuadro de diálogo con la pregunta "¿Qué país o región?", un cuadro para especificar la información y un botón Aceptar.

   Sugerencia: Si desea que el parámetro sea flexible, concatene la expresión con caracteres comodín mediante el operador Como. Por ejemplo, en lugar de usar [¿Qué país o región?] como criterio, puede usar Como [¿Qué país o región?]&"*" para que el parámetro coincida con un intervalo de datos mayor. El uso de Como no cambia la apariencia del mensaje de solicitud de parámetros.

3. En la pestaña Diseño, en el grupo Mostrar u ocultar, haga clic en Parámetros.

4. En el cuadro de diálogo Parámetros de la consulta, en la columna Parámetros, especifique el mismo mensaje de solicitud de parámetros que usó en la fila Criterios. Incluya los corchetes, pero no incluya caracteres comodín concatenados ni el operador Como.

5. En la columna Tipo de datos, seleccione el tipo de datos del parámetro. El tipo de datos debe coincidir con el tipo de datos del campo de encabezado de fila.

Si el campo que usa para calcular valores de resumen en la consulta de tabla de referencias cruzadas contiene valores Null, estos valores se omitirán en cualquier función de agregado que use. En algunas funciones de agregado, los resultados pueden verse afectados. Por ejemplo, para calcular un promedio, se suman todos los valores y se divide el resultado entre el número de valores. Pero si el campo contiene valores Null, estos no se cuentan como parte del número de valores.

En algunos casos, es posible que prefiera reemplazar los valores Null por ceros con el fin de que los valores se cuenten durante los cálculos de agregado. Puede usar la función Nz para reemplazar los valores Null por ceros.

Sintaxis de Nz

Nz ( variant [, valueifnull])

La sintaxis de la función Nz consta de los siguientes argumentos:

Argumento	Descripción
variant	Necesario. Variable de tipo de datosVariant.
valueifnull	Opcional (a menos que se use en una consulta). Tipo de datos Variant que proporciona el valor que se va a devolver si el argumento variant es Null. Este argumento permite devolver un valor distinto de cero o una cadena de longitud cero. Nota: Si usa la función Nz en una expresión de una consulta sin usar el argumento valorSiEsNulo, el resultado será una cadena de longitud cero en los campos que contengan valores nulos.

1. Con la consulta abierta en la vista Diseño, en la cuadrícula de diseño de la consulta, haga clic con el botón secundario en el campo Valor.

2. En el menú contextual, haga clic en Zoom.

3. En el cuadro Zoom, incluya el nombre del campo o la expresión entre paréntesis y, después, escriba Nz delante de los paréntesis.

4. Escriba , 0 justo antes del paréntesis de la derecha.

   Por ejemplo, si usó Nz con un campo llamado "Horas perdidas" para convertir los valores Null en ceros, la expresión terminada tendría el siguiente aspecto:

Nz([Hours Lost], 0)

• Simplifique    Las consultas de tabla de referencias cruzadas pueden ser difíciles de leer a medida que aumenta el número de combinaciones de filas. No use más encabezados de fila de los necesarios.

• Construya la tabla de referencias cruzadas por pasos    No se limite al uso de tablas. Puede empezar por crear una consulta de totales y, después, usar dicha consulta como origen de registros de una consulta de tabla de referencias cruzadas.

• Elija con cuidado el campo de encabezado de columna    Las hojas de datos de tabla de referencias cruzadas tienden a ser más fáciles de leer cuando el número de encabezados de columna es relativamente pequeño. Una vez identificados los campos que desea usar como encabezados, considere la posibilidad de usar el campo que tenga menos valores distintivos para producir encabezados de columna. Por ejemplo, si la consulta calcula un valor por edad y sexo, considere la posibilidad de usar el sexo para los encabezados de columna en lugar de la edad, ya que el sexo tiene menos valores posibles que la edad.

• Usar una subconsulta en una cláusula WHERE    Puede usar una subconsulta como parte de una cláusula WHERE en una consulta de tabla de referencias cruzadas.

Volver al principio