TECNOLOGÍA E INFORMÁTICA: NOVENO

USAR PARÁMETROS EN CONSULTAS E INFORMES

Las consultas son útiles para poder trabajar sólo con los campos de una tabla que corresponden a una tarea determinada. Cuando se desea limitar aún más los datos con los que se va a trabajar, basándose en el valor de un campo, se pueden usar criterios en la consulta. Los criterios son reglas que se incluyen en el diseño de una consulta. Estas reglas especifican valores o modelos con los que los campos deben coincidir o que los campos deben contener para que la consulta los devuelva.

Cuando se desea que una consulta pida un valor o un modelo cada vez que se ejecuta, se puede crear una consulta de parámetros. También se pueden usar parámetros con un informe para que se pidan criterios cuando se ejecuta el informe. Se pueden usar formularios para que los parámetros de consulta e informe se puedan recopilar y pasar mejor de las siguientes maneras:

Permitiendo el uso de controles específicos del tipo de datos
Habilitando la persistencia de los valores de parámetro
Permitiendo el uso de controles de cuadro combinado (de modo que se puede elegir un valor de una lista en vez de escribirlo)
Permitiendo el uso de otros controles disponibles en formularios

En este artículo se explica cómo usar los parámetros en consultas e informes, y cómo usar los formularios para mejorar el uso de los parámetros.

Introducción

Puede usar criterios en una consulta en Microsoft Office Access 2007 para limitar el conjunto de registros que la consulta va a devolver. Consideremos el siguiente caso.

Supongamos que tiene una consulta denominada Último pedido, que devuelve los nombres y direcciones de sus clientes junto con la fecha en la que envió su último pedido. Acaba de agregar algunos productos nuevos e interesantes al inventario y al catálogo. Cada vez que envía un pedido, incluye una copia del actual catálogo. Prevé que los nuevos productos van a tener mucho éxito y desea asegurarse de que todos los clientes los conocen. Desea enviar un nuevo catálogo a todos los clientes que aún no lo hayan recibido junto con un pedido.

Por este motivo, desea crear una lista de correo con los nombres y direcciones de los clientes cuyo último pedido se envió antes de publicarse el nuevo catálogo. Para ello, aplica un criterio al campo de fecha de envío de la consulta Último pedido. El criterio especifica que la fecha que figura en ese campo debe ser anterior a la fecha en la que se inició la distribución del nuevo catálogo, por ejemplo, 31/3/2006. Cuando ejecute la consulta, obtendrá una lista de correo que contenga únicamente los clientes que no han recibido el nuevo catálogo.

Pero, ¿qué sucede si vuelve a producirse esta situación? Podría modificar el diseño de la consulta cambiando el valor especificado por el criterio pero, ¿no sería genial si la consulta pudiera solicitar una fecha y usar esa fecha para decidir qué clientes se van a incluir en la lista de correo? Para ello, deberá usar un tipo especial de criterio denominado parámetro.

Los parámetros también se pueden usar con los informes. Esto permite a los usuarios pasar al informe criterios cuando lo ejecutan. En este artículo, se muestra cómo usar Office Access 2007 para crear informes que acepten parámetros.

Puede que los cuadros de diálogo proporcionados por una consulta de parámetros sean insuficientes para sus objetivos. En esos casos, puede crear un formulario que se ajuste mejor a sus necesidades de recopilación de parámetros. En este artículo se muestra cómo crear un formulario que recopile parámetros de informe.

En este artículo se supone que está familiarizado con la creación de consultas e informes. Al menos deberá saber cómo crear una consulta de selección antes de continuar.

Para obtener más información sobre la creación de una consulta de selección o un informe, vea los artículos Crear una consulta de selección sencilla y Crear un informe sencillo.

Este artículo incluye ejemplos de cómo usar parámetros en las consultas y los informes. Sin embargo, no incluye una referencia exhaustiva para especificar criterios.

Para obtener más información sobre cómo especificar criterios, vea el artículo Ejemplos de criterios de consulta.

Usar parámetros en consultas

Crear una consulta de parámetros es tan fácil como crear una consulta que usa criterios. Puede diseñar una consulta de modo que pida un solo dato, como un número de pieza, o varios datos, como dos fechas. Por cada parámetro, una consulta de parámetros muestra un cuadro de diálogo independiente en el que se solicita un valor para ese parámetro.

Crear una consulta de parámetros

Cree una consulta de selección y, a continuación, abra la consulta en la vista Diseño.
En la fila Criterios del campo al que desee aplicar un parámetro, escriba entre corchetes el texto que debe aparecer en el cuadro de diálogo del parámetro; por ejemplo:

[País o región de origen:]

Cuando ejecute la consulta de parámetros, el parámetro aparecerá sin corchetes en un cuadro de diálogo.

Repita el paso 2 por cada parámetro que la consulta debe recopilar y aplicar.

Puede seguir los pasos anteriores para crear una consulta de parámetros a partir de cualquiera de los siguientes tipos de consulta:

Selección
Tabla de referencias cruzadas
Datos anexados
Creación de tabla
Actualización

También puede agregar parámetros a una consulta de unión. Para ello:

Abra la consulta de unión en la vista SQL.
Agregue una cláusula WHERE que contenga cada uno de los campos para los que desee solicitar un parámetro.

Si ya existe una cláusula WHERE, compruebe si los campos para los que desee solicitar un parámetro ya están incluidos en la cláusula. En caso contrario, agréguelos.

En vez de usar criterios en la cláusula WHERE, inserte indicadores de parámetro que tengan la misma sintaxis que los parámetros de otros tipos de consulta.

Hacer coincidir parte de un valor de campo con una cadena de parámetro

Quizás desee aplicar un poco de variabilidad en la forma en que la consulta aplica un parámetro. Por ejemplo, puede que desee que una consulta acepte una cadena de texto y la haga coincidir con parte de un campo. Para ello, use la palabra clave Como junto con caracteres comodín. Por ejemplo, desea que la consulta solicite un país o una región de origen y que el valor de campo pertinente contenga la cadena de parámetro. Para ello:

Cree una consulta de selección y, a continuación, abra la consulta en la vista Diseño.
En la fila Criterios del campo al que desee aplicar el parámetro, escriba Como "*"&[, escriba el texto que desee usar como indicador y, a continuación, escriba ]&"*".

Cuando ejecute la consulta de parámetros, la cadena aparecerá en el cuadro de diálogo sin corchetes y sin la palabra clave Como o los caracteres comodín.

Cuando la consulta acepte el parámetro, coincidirá con los valores que contiene la cadena de parámetro. Por ejemplo, la cadena de parámetro us coincide con las filas donde el campo de parámetro tiene el valor Australia y filas donde el valor es Austria.

Puede usar asimismo la palabra clave Como y los caracteres comodín para especificar que un parámetro debe coincidir con el inicio o el final de un valor de campo. Para que coincida con el inicio de un valor de campo, omita las comillas, el carácter comodín y el carácter de y comercial (&) delante del corchete de apertura. Para que coincida con el final de un valor de campo, omita el carácter de y comercial, las comillas y el carácter comodín situados detrás del corchete de cierre.

Coincidencia basada en valores desiguales

Puede que desee que una consulta, en vez de devolver las filas con un valor coincidente, devuelva las filas para las que se cumplan también otras comparaciones. Por ejemplo, desea solicitar un año y desea que se devuelvan las filas en las que el valor de año es mayor que la cadena de parámetro. Para ello, escriba un operador de comparación a la izquierda del primer corchete del indicador de parámetro. Por ejemplo, >[Año:].

----------------------------------------------------------------

CONSULTAS DE SELECCION:

La consulta de selección sirve para producir listados por pantalla con datos (reales o calculados) de las tablas relacionales de la base de datos. Las consultas de selección no pueden producir perdidas de datos por error o mal diseño, puesto que sólo seleccionan datos de una o varias tablas para mostrarlos por pantalla o ser origen de datos (registros) de Formularios, Informes y otros objetos de Acces...

Para utilizar las consultas lo primero que hay que hacer en Acces es entrar en la ventana de diseño de consultas. Si utilizas Acces 2003 o anterior deberás acceder como muestra la imagen...

Si en cambio, utilizas Acces 2007 o superior, deberás activar los botones que ves en la imagen siguiente. (aunque que la inteface 2007 o superior sea diferente a la versión 2003 o inferior, el funcionamiento de la ventana diseño de consultas es fundamentalmente el mismo)...

En cualquier caso la primera ventana que encontrarás será (Mostrar tabla) la que ves más abajo. Necesariamente debemos agregar (boton agregar) una o varias tablas. Si agregas más de una tabla, éstas deberán estar relacionadas según el arbol de tablas y relaciones de tu base de datos. Si tu base de datos no tiene arbol de relaciones será porque tendrás una sola tabla en tu base de datos. Si tienes más de una tabla y éstas no están relacionadas entonces pierdes el tiempo y tu esfuerzo con Acces. Es mejor que conviertas las tablas en hojas de Excel...

Como ves en la imagen siguiente la ventana Mostrar tabla, muestra todas las tablas de la base de datos activa. Una vez agregadas una o varias tablas relacionadas debes cerra la ventana Mostrar tabla...

Pero no puedes utilizar las consultas si tu base de datos no tiene al menos una tabla o entidad con un buen números de registros. Tenga en cuenta el lector que la perfecta comprensión de los ejemplos que se exponen a continuación implica visualizar en todo momento la estructura relacional (arbol de relaciones) de entidades que muestra la imagen siguiente.

Las consultas de selección son las más frecuentes, siendo necesarias para otros tipos de consultas que tratamos en los siguientes artículos. Los principales procesos que podemos realizar con las consultas de selección son:

Selección de campos (atributos) de una o varias tablas (entidades) relacionales...
Filtros o condiciones de selección...
Creación de campos calculados mediante expresiones...
Cálculos con totales y agrupamientos.
Paso de parámetros.
...

SELECCION DE CAMPOS:

Veamos directamente un ejemplo de una consulta básica sobre la tabla PRODUCTOS de la estructura relacional anterior. Dicha tabla corresponde a la existencia en el mundo real de marcas de coches que vende nuestra supuesta empresa o negocio. Los atributos que tienen interés a las reglas de mi negocio, son los que vemos en la imagen siguiente. Sólo queremos obtener un listado de todos los productos mostrando el número de producto (N_PRODUCTO, llave primaria), la descripción de dicho producto (Descripción), la categoría (Categoría) y la velocidad en km/h del producto, puesto que son coches (Velocidad).

Nuestra consulta tendrá la apariencia de la imagen de abajo. La zona superior de Acces mostrará una tabla (en nuestro ejemplo PRODUCTOS). La zona inferior de celdas es donde seleccionaremos campos, ponemos filtros, creamos expresiones, etc. Pero nuestra consulta es muy básica: sólo incluirá los cuatro campos que se ven en la imagen de la imagen siguiente...

Al ejecutar la consulta obtendríamos la lista de registros siguiente sobre la tabla PRODUCTOS. Como hay una sola tabla y no hay ningún filtro ni expresión, se obtienen todos los registro de la tabla que en el ejemplo de la imagen es 62, es decir 62 marcas de coches cuya primera marca es Land Rober, categoría A y velocidad 100 k/h...

FILTROS O CONDICIONES DE SELECCION:

Pero la selección puede ser más precisa si filtramos, por ejemplo para obtener los mismos campos pero sólo de aquellas marcas o modelos cuya velocidad sea superior o igual a 200 Km/h. Utilizamos la cuadrícula de celdas (zona inferior) la fila Criterios: que ves en la imagen. En la columna Velocidad en la celda de la fila Criterios escribimos la expresión: >=200 que ves en la imagen...

Se pueden escribir centenares de expresiones sintácticamente correctas en las filas de criterios, pero se trata de una sintaxis estricta que no admite errores sintácticos ni semánticos como si se trataran de fórmulas de Excel. Ambas aplicaciones son de Microsoft y comparten un buen número de funciones predefinidas que también pueden incorporarse a las filas de criterios para crear consultas avanzadas...

En cualquier caso, al ejecutar la consulta anterior obtendremos la lista de abajo en la que sólo aparecen 24 de los 62 modelos o marcas que tiene nuestra tabla PRODUCTOS. Los 24 modelos tienen una velocidad superior o igual a 200km/h...

Quizás otro ejemplo ayude a comprender el funcionamiento de las consultas. En la imagen siguiente utilizamos el operador "como" para atributos (campos) de texto. El operador "como" admite caracteres comodín como el asterisco (*) de tal forma que podemos querer obtener todos los modelos de una sola marca, por ejemplo Ford. Entonces podríamos escribir la siguiente expresión Como "Ford*", que ves en la imagen siguiente...

Es decir, el operador Como, un espacio, comillas de apertura, la palabra Ford, un asterisco y comilla de cierre y no olvides que se interpreta con una sintaxis estricta: colocar comillas, espacios, paréntesis, etc. mal colocados producen errores sintácticos insalvables...

Deberíamos obtener sólo 9 (de 62) registros que ves en el listado siguiente y que corresponden a la marca Ford, cualquier modelo...

CREACION DE CAMPOS CALCULADOS O VIRTUALES MEDIANTE EXPRESIONES

Un campo calculado es un campo que no se almacena en ninguna tabla, por tanto es un campo virtual no real cuyo valor depende (se calcula) de otro campo real de alguna tabla. La idea es sencilla: el valor de algún campo real lo utilizamos para crear otro campo virtual que se almacena en la consulta como expresión. Para crear dicha expresión hay que seguir la siguiente sintaxis general:

nombre_campo: expresión

nombre_campo es un nombre arbitrario (sin espacios) que identifica al campo calculado que crea la expresión. El carácter ":" es obligatorio como separador entre el nombre del campo y la expresión. La expresión debe ser válida sintáctica y semánticamente...

Los campos calculados no incrementa el tamaño de las tablas puesto que no se almacena sino que se calcula al ejecutar la consulta. Pero un ejemplo ayudará a entender la idea. En la imagen siguiente tenemos las tablas EMPLEADO_PERSONAL y EMPLEADO_LABORAL. Responde a la existencia en el mundo real de la entidad empleados de mi empresa. Ambas tablas tienen una relación uno a uno siendo EMPLEADO_PERSONAL la tabla principal...

Nos interesa el campo Salario de EMPLEADO_LABORAL, del que sabemos que almacena el salario bruto de cada empleado. Pero hay que realizar una retención correspondiente a algún impuesto que en nuestro ejemplo es el IRPF y más concretamente el 18% de los ingresos o salario. La imagen siguiente muestra el campo calculado al que hemos llamado irpf: aunque sea poco original...

Escribimos la expresión en una columna vacía de la zona de celdas, la palabra irpf, el carácter ":", carácter corchete "[" de apertura para nombre decampo, el campo salario, el corchete de cierre de nombre de campo, el carácter "*" como operador de multiplicar, la constante 18, que es la retención que queremos aplicar, el carácter "/" como operador de división y la constante 100 para porcentaje...

Por supuesto que podemos calcular la seguridad social, el neto, comisiones, etc. El número de campos que se pueden crear es ilimitado: edad del empleado con la fecha de nacimiento, la antigüedad en la empresa con la fecha de alta, ...

Obtendremos el listado siguiente:

CALCULOS CON AGRUPAMIENTOS Y TOTALES

Otra característica interesante de la consultas es la posibilidad de calcular totales y agrupamientos para encontrar respuesta alguna pregunta como ¿cuántas unidades del producto LAND ROBER se vendieron en el año 1998?

Hay que tener en cuenta que la pregunta implica a tres tablas relacionales que ves en la imagen inferior. Nuestra tabla PRODUCTOS define una relación con la realidad facturas a nuestros clientes de los productos que vendemos cuya traducción en tablas define una relación de varios a varios entre PRODUCTOS y los números de facturas (tabla NUM_VENTAS) mediante una tercera tabla de unión que contiene la llave primaria múltiple (tabla VENTAS) como ves en la imagen...

La zona inferior de celdas debe mostrar una nueva fila llamada Totales. Dicha fila se activa con el botón derecho del raton sobre una celda vacía, aparece un menú contextual para clic en Totales...

Necesitamos tres campos para responder nuestra pregunta: Descripción del producto, año 1989 con un campo calculado, y el campo cantidad de VENTAS al que aplicaremos la operación suma de la fila totales. No buscamos un listado sino la respuesta a la pregunta ¿cuántos LAND ROBER se vendieron en 1998?

Necesitamos filtrar para encontrar una sola respuesta. Utilizamos la fila Criterios para filtrar el modelo y el año...

El campo calculado ELAÑO utiliza la función Año([FECHA]) igual que Excel...

FECHA se refiere al campo Fecha de la tabla NUM_VENTA...

Obtenemos 10 unidades vendidas en 1998...

PASO DE PARAMETROS

Con cierta frecuencia Acces interrumpe la ejecución de una consulta con una pequeña ventana "Introduzca el valor del parámetro" y en la que aparece en la zona gris un nombre (variable) que hemos escrito en alguna expresión. Los nombres entre corchetes se interpretan como campos reales o calculados. Si no se encuentra ningún campo real o calculado entonces se convierte en una variable (parámetro) a la que se puede asignar valor.

Por ejemplo, la consulta de la imagen siguiente utiliza el parámetro [retencion] para aplicar cualquier porcentaje de retención en la ejecución de la consulta...