jueves, 9 de abril de 2015

Consultas




Una vez que se han relacionado las tablas que integran una BD podemos crear consultas, que son elementos que reunen información de tablas distintas.


Una consulta es un método para acceder a los datos en las bases de datos. Con las consultas se puede modificar, borrar, mostrar y agregar datos en una base de datos.


Los filtros eliminan temporalmente de una tabla, aquellos datos que no son necesarios para realizar cierta tarea, por ejemplo para mostrar solo los clientes que tienen adeudos.


A diferencia con las consultas, nada que se haga mediante el uso de filtros es permanente; siempre se pueden restablecer todos los registros para ver el total de la información.

Consultas
Una consulta extrae información de la Base de Datos y la muestra al usuario. Los registros seleccionados son dinámicos en el sentido de que usualmente su contenido tiene por origen varias tablas y que solo existen mientras la consulta esté activa. Al cerrar la consulta, el conjunto de registros deja de existir.
Mediante consultas podemos:
  • Elegir campos específicos de diversas tablas;
  • Seleccionar registros empleando criterios;
  • Calcular totales;
  • Crear formularios e informes;
  • Crear otras consultas y gráficos.
Estudiaremos tres tipos de consultas
  • De selección: seleccionan y muestran registros
  • De referencias cruzadas: seleccionan y presenta registros en formato de tabla
  • De acción: alteran el contenido de registros en una única operación

Las consultas de selección muestran aquellos datos de una tabla (o varias) que cumplen los criterios especificados. Una vez obtenido el resultado permiten modificar los datos si se requiere.
Una consulta de selección genera una tabla lógica, que no está físicamente en el disco duro sino en la memoria RAM del ordenador y cada vez que se abre la consulta se vuelve a calcular.Por ejemplo en la tabla Clientes, realice una consulta que seleccione apellido paterno (en orden ascendente), nombre y teléfono.


Rango de valores
Entre...Y.  Por ejemplo para seleccionar las personas mayores de 18 años pero menores de 30, en la celda Criterios escribimos: Entre 18 Y 30.

Lista de valores
En (“valor1”,”valor2”,….)  Por ejemplo para seleccionar todos los clientes de tres ciudades, en el campo Ciudad se utilizaría este criterio:
En ("Monterrey","Saltillo", "Victoria")

Varios criterios en diferentes campos (Y lógica)
Cuando se escriben varios criterios en el mismo renglón Access buscará un registro que cumpla todos los criterios. Por ejemplo si deseamos clientes que sean de Nuevo Laredo, mayores de edad y de sexo masculino.


Varios criterios en un mismo campo (O lógica)
Cuando se escribe un criterio en la casilla "Criterio", otro en la casilla o, y opcionalmente más criterios en las filas de abajo, el programa buscará un registro que cumpla al menos uno de los criterios. Por ejemplo si deseamos clientes que se llamen Laura; Lorena o Lucía


Selección de registros con datos
En criterios se escribe No es Nulo

Selección de registros sin datos
En criterios se escribe es Nulo
Por ejemplo realice una consulta de clientes sin segundo nombre.

Para especificar una cadena de longitud cero, escriba dos comillas dobles sin espacio entre ellas ("").) por ejemplo en el campo Fax (para filtrar aquellos clientes sin Fax)

Fecha actual
En criterios se escribe Fecha()

[Consultas con parámetros]
Se coloca la pregunta entre corchetes [ ] debajo del campo que se desea consultar.


Consultas Calculadas.      (Nombre del campo: expresión).

En algunas ocasiones es conveniente generar campos calculados a partir de los datos existentes en una tabla. Por ejemplo si en un campo se tiene la fecha de nacimiento, puede calcularse la edad a partir de una expresión simple:
  edad: Int((Fecha()-[FechaNac])/365)

Estos campos se crean en la propia consulta y para ello en vista de diseño de la consulta, se establece en la fila Campo de una columna vacía el nombre del campo seguido por el signo : (dos puntos) y una expresión correspondiente al cálculo a realizar.
Para construir la expresión utilizamos operandos (variables y constantes) , operadores y funciones, de una manera muy similar a como lo haríamos en Excel.
Las consultas de referencias cruzadas permiten visualizar los datos en renglones y en columnas (son semejantes a las tablas dinámicas de Excel). Por ejemplo si tenemos una tabla de productos y otra tabla de pedidos, mediante este tipo de consultas podemos  construir una tabla que muestre como renglones los nombres de los productos y como columnas el número del año y en la intersección el importe de las ventas respectivas.


Las consultas de acción realizan cambios a los registros. Existen varios tipos de consultas de acción, de eliminación, de actualización, de datos anexados y de creación de tablas.

DeescripciónFiltroConsulta
Puede utilizarse como fuente de datos para un formulario, una consulta o un informe
Puede ordenar registros
Puede incluir registros de varias tablasNo
Permite especificar qué campos desea mostrar en el resultadoNo
Puede calcular sumas, promedios, cuenta y otros tipos de cálculosNo
Permite modificar datosSí, incluso pueden alterar el contenido de registros en una única operación (consultas de acción)


Al igual que otros objetos de Access, las consultas tienen Propiedades que podemos modificar al momento del diseño, para lograr un mayor control sobre el funcionamiento de la consulta.    Algunas propiedades importantes son:

Descripción: Permite describir someramente que es lo que realiza la consulta.
Valores superiores: En una lista ordenada descendente si indicamos 5, solo aparecerán en la hoja de datos los 5 registros que tengan los valores mayores. Ordenada ascendente aparecerán los 5 registros de menor valor.
Valores únicos: Si elegimos "Si", solo aparecerán en la hoja los valores de todos los campos que sean únicos.
Registros únicos: Access nos mostrara valores sin repetir.
Para obtener ayuda con cada propiedad debemos oprimir F1 después de situar el cursor en la casilla de la propiedad que interese.




Nota. Para ver las propiedades de una consulta, en la vista Diseño haga clic con el botón secundario en un espacio vacío en la ventana de diseño  y después  haga clic en Propiedades en el menú contextual.
Introducción a las Consultas:
*



Los siguientes archivos servirán de apoyo para realizar las consultas propuestas.





En la presentación de Introducción a las consultas se pide realizar lo siguiente:
Consulta que
1)      Tome datos de dos tablas vinculadas y los muestre
2)      Calcule la edad conociendo fecha de nacimiento
3)      Proporcione las iniciales de la persona
4)      Encuentre el mes del cumpleaños y el día de cumpleaños y ordene por esos campos
5)      Encuentre los registros de
a)      La fecha actual
b)      Registros de la fecha señalada
c)      Registros en el rango de fechas que se indique
d)      Anteriores a hoy
e)      Posteriores a hoy
f)       En la última semana
g)      En los últimos 30 días
h)      Fechas entre hace 60 días y hace 30 días
i)       En cierto mes
j)       Registros con más de 30 días
k)      Todas las fechas del año ....
6)      Conociendo nombres y apellidos los concatene y muestre en un mismo campo
7)      Conociendo cantidad y precio calcule el importe
8)      Conociendo el importe calcule el IVA y calcule la suma de importe más IVA en un campo adicional
9)      Pregunte por el nombre de la persona para mostrar sus datos
10)   Pregunte por la fecha inicial y la fecha final para mostrar registros en ese rango de fechas
11)   Muestre nombres y las calificaciones obtenidas en Matemáticas, Física, Química
a)      Que muestre quienes reprobaron las tres materias (Matemáticas Y Física Y Química)
b)      Que muestre quienes reprobaron una o más materias (Matemáticas O Física O Química)
12)   Que calcule el promedio de las 3 calificaciones y lo muestre
13)   Que solicite el número de control y muestre sus calificaciones obtenidas
14)   Las 21 consultas solicitadas en los 4 ejercicios de la presentación


En la práctica anterior se vio como guardar un filtro avanzado como consulta, practicarlo con diversas condiciones de filtrado, como son las siguientes:


Consulta que
1)      Busque las calificaciones de un alumno conociendo
a)      Su nombre
b)      Su número de control
c)      Las primeras letras de su nombre
d)      Su apellido paterno
e)      Su apellido paterno con comodines (si una vocal va acentuada o no, etc.)
f)       Parte de su apellido paterno
(que empieza con …),
(que termina con …)
(que contiene … xxx …)
2)      Busque los registros de personas con un salario
a)      Igual a
b)      Mayor que
c)      Menor que
d)      Entre
3)      Busque los registros de
a)      Cierto estado
b)      Que no sean de dicho estado
4)      Busque los registros de empleados con
a)      Cierto apellido … y que vivan en cierta ciudad …
b)      Cierto cargo … y cierta ciudad …
c)      Con cierto apellido u otro
d)      Con ingreso igual a …,
menor que …,
mayor que …,   entre … y …
e)      Con un campo (Pago) que es nulo
f)       Con un campo (Pago) que no es nulo  (o sea que si hay pagos)
5)      Busque los registros de empleados
a)      Desde cierto apellido hasta el final del alfabeto
b)      Desde el inicio del alfabeto hasta cierto apellido
c)      Entre un apellido y otro
Adicionalmente realice todas las consultas indicadas en la práctica correspondiente, algunos de los cuales se citan a continuación:

Consultas con criterios simples:                   (Dulce.mdb)
•                  Productos que tengan un valor igual, menor, mayor o distinto al valor de referencia.
•                  Ventas que se han efectuado en cierta fecha; antes de cierta fecha; después de cierta fecha; entre cierta fecha y otra.     Ventas de la fecha actual, Ventas de la última semana.
•                  Productos que pertenezcan a un mismo proveedor, a un cierto departamento, o que se le hayan vendido a cierto cliente.
                                                                                           (PagosEmpleados.mdb)
•                  Salarios mayores que un cierto valor; Menores que un cierto valor; Dentro de un cierto rango de valores.
•                  Ordenar ascendente o descendente y encontrar los n valores menores o mayores.
De la tabla salarios buscar los 10 mayores
Ordenar    Descendente        10

         Empleo de comodines

José         Encuentra solo a José
Jose         Encuentra solo a Jose                     
Jos?          Encuentra a Jose y a José
*Jos?        Encuentra a  Jose, José,  también a Juan José, Luis José, etc.
Jos*          Encuentra a  Jose, José, Josefina, Jose Luis, etc.
José*        Encuentra a  José, José Carlos, Jose Luis, etc.
*José*      Encuentra a los José (con cualquier nombre anterior o posterior)
J*              Encuentra a quienes su nombre empieza con J  (Javier, José, Juan, Jorge, etc.)
Entre A* y C*              Buscar clientes cuyos apellidos estén entre  A y C
 “* *”                          Nombres de dos o mas palabras
Negado “* *”             Nombres de una palabra

Consultas con criterios variables (Consultas con parámetros)
De la tabla alumnos buscar por número de control   [Introduzca el número de control]
•                  Clientes de una cierta ciudad que especificará el usuario.      [ texto ]
•                  Clientes cuyo nombre empiece con …                 Como [Nombre] & “*”

Consultas con criterios múltiples:
•                  Con dos campos unidos por un operador lógico Y.
•                  Con dos campos unidos por un operador lógico O.

Consultas calculadas:

De la tabla Vendedores tomar las iniciales de cada vendedor
Iniciales: Left(Nombre,1) & Left(Paterno,1) & Left(Materno,1)

De la tabla RFC calcular la edad a partir de la fecha de nacimiento
edad: Int((Date()-[FechaNac])/365.25)

De la tabla RFC encontrar el mes en que cumplen años
mes: month([FechaNac])

De la tabla PC encontrar el precio con descuento.
De la tabla PC encontrar el precio con descuento y con IVA.

De la base de datos Agencia Automotriz calcular la comisión de cada vendedor (3%)
Campo      Comisión: precio*0.03
De la base de datos Agencia Automotriz calcular la comisión de cada vendedor (4%) si cuesta más de $200,000 y 3% si no es así.
Comision2: IIf([precio]>200000,[precio]*0.04,[precio]*0.03)


Negado "(867)*"        Clientes con # teléfonos foráneos

""                                Buscar clientes sin número de teléfono

Como "*/09/*"             Pagos en Septiembre
*/*/2014                       Pagos en 2014
Como "*/03/14"            Pagos en Marzo de 2014

Ejemplos de consultas con criterios simples


La imagen siguiente muestra las consultas realizadas en estas 3 BD de ejemplo.