jueves, 30 de septiembre de 2010

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.

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.

Puede ser grabadoFiltroConsulta
Puede utilizarse como fuente de datos para un formulario, una consulta o un informe
Puede ordenar registros
Puede incluir registros de varias tablas No
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.


Introducción a las Consultas:



Introducción a las consultas
Consultas en Access 2007 parte 1      Consultas en Access 2007 parte 2

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 de 2010
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
*/*/2010                       Pagos en 2010
Como "*/09/10"            Pagos en Septiembre de 2010
 

lunes, 27 de septiembre de 2010

Buscar, Ordenar y Filtrar





Las opciones más usuales para poder localizar información en una tabla son buscar un dato,  ordenar los registros o filtrar los mismos.

Vamos a practicar con lo siguiente:
  • Buscar un dato.
  • Reemplazar datos.
  • Ordenar Registros (Ordenar por un campo, Ordenar por varios campos)
  • Eliminar los criterios de ordenación.
  • Aplicar Filtros en Access.
  • Filtros simples.
  • Filtros por selección.
  • Filtro por formulario.
  • Filtros avanzados.
En las Prácticas de Access frecuentemente vamos a utilizarlas.

Una  búsqueda localiza los registros con cierto criterio de uno en uno. Los filtros localizan todos los registros que tienen ese criterio en común de una sola vez.

Un filtro es un criterio (conjunto de criterios) que se aplica a los registros de una tabla (o una consulta o un formulario) a fin de mostrar un subconjunto de los registros que cumplan con cierta condición. Un filtro permite ver solo específicos.

Access maneja diversos tipos de filtros:
· filtro por selección
· filtro por formulario
· filtro avanzado.

Usar el filtro por selección
1. En un campo de la hoja de datos, encuentre una aparición del valor que desea que contengan los registros para que sean incluidos en el resultado del filtro.
2. Seleccione el valor y, a continuación, haga clic Filtro por selección en la barra de herramientas. Puede seleccionar todo el valor de un campo o parte de él (el modo en que se selecciona el valor determina qué registros devuelve el filtro).


Al seleccionarEncuentra los registros en los que Ejemplo
El contenido completo de un campo (o situar el cursor en el campo sin seleccionar nada)El contenido completo del campo coincide con la selecciónEn el campo Ciudad seleccione “Nuevo Laredo”, para ver todos los registros de “Nuevo Laredo”.
Los primeros caracteres de un campo.En los que los primeros caracteres son los mismos que los seleccionadosSi en el campo Paterno seleccionamos Gar, el filtro entrega los registros con Garay, García, Garza, etc.
Un grupo de caracteres intermediosEse campo contiene los caracteres seleccionadosPor ejemplo si en nombre de libro seleccionamos Access, encontrará “Domine Access 2007” y “Microsoft Access 2010”

Usar el filtro por formulario
1. Abra una tabla en vista de hoja de datos
2. Haga clic filtro avanzado / filtro por formulario para cambiar a la ventana correspondiente
3. Seleccione el campo en el que desea especificar los criterios que esos registros deben cumplir para ser incluidos en el conjunto de registros filtrados.
4. Introduzca los criterios seleccionando en la lista desplegable del campo el valor que desea buscar (o escriba el valor en el campo). Podemos aplicar criterios de filtrado a más de un campo.
5. Haga clic en Alternar Filtro, para ver la lista filtrada.

Para especificar criterios más complejos, escriba la expresión completa utilizando la combinación apropiada de identificadores, operadores, caracteres comodín y valores para producir el resultado deseado.
Carácter comodínUsoEjemplo
?Sustituye a un carácter individualTrevi?o filtrará los registros con Trevino, Treviño y Trevi~o
*Sustituye a uno o varios caracteresSi el criterio utilizado es Gar*, el filtro entrega los registros con Garay, García, Garza, etc.
#Sustituye a un carácter numérico individualSi el criterio utilizado es 200# el filtro entrega los registros con 2000, 2001, 2002,…, 2009

Usar el Filtro avanzado
1. Abra una tabla en la vista de hoja de datos
2. Haga clic filtro avanzado / filtro avanzado
3. Agregue a la cuadrícula de diseño el campo o campos que requiere y especifique los valores y criterios que el filtro utilizará para encontrar registros deseados.
4. Si desea ordenar los registros, en el renglón ordenar seleccione Ascendente o Descendente
5. En el renglón de criterios anote el o los criterios deseados (O lógica para criterios en renglones contiguos Y lógica para criterios en el mismo renglón)
6. Haga clic en Alternar Filtro, para ver la lista filtrada

Estando en la cuadricula de diseño de filtro avanzado podemos dar clic al botón de filtro avanzado y seleccionar la opción de Guardar como Consulta.


En esta sección vamos a realizar todo lo que indica la Práctica 5 (Ordenación y filtrado en Tablas de Access) y la siguiente presentación:




En este enlace puede descargar el archivo correspondiente.
Access permite realizar filtros complejos sobre Tablas, Consultas, Formularios e Informes, de una manera muy sencilla, para ello nos colocamos en el campo que deseamos filtrar y damos clic al Botón de filtro de la Cinta de Opciones.

Las opciones que se despliegan de que el campo por el que deseamos filtrar sea numérico, de texto o de fecha.
Por ejemplo si nos interesa conocer las ventas en un rango de fechas, selecionamos la opción Entre ... y luego indicamos entre que fechas deseamos filtrar.


También podemos filtrar dando clic a una celda de la tabla y clic al botón derecho del apuntador, para que Access despliegue un menú contextual que incluye filtros.

En la parte de abajo de la pantalla podemos ver si la hoja de datos esta filtrada o sin filtrar (dar clic en Alternar filtro).



Opcionalmente, un filtro puede ser guardado como consulta.

Practique todos los filtros de fechas citados utlizando esta tabla:  PagosAlumnos.xls, que actualiza la fecha automáticamente.

Otras tablas para practicar filtros:  Tablas para practicar filtros y consultas
...

...

Los archivos de Access que emplearemos para esta práctica ya han sido trabajados previamente y los deben tener en su SkyDrive todos ustedes, como se les indicó en su oportunidad. Quienes no los tengan pueden construirlos a partir de las tablas de Excel anteriormente proporcionadas, algunas de las cuales se citan a continuación

Nómina

Pagos Alumnos

Calificaciones MFQ

PC promociones

Dentista

Alumnos Administración

Carpeta con archivos para las prácticas

Primeras prácticas de Access



A continuación estan los enlaces a algunas bases de datos de ejemplo (para que practiquen estos temas):

Dulce

Neptuno

NorthWind

NorthWind 2007

Algunas ligas interesantes:

Ordenar registros en Access 2007

Filtros en Access 2007

Filtrar Registros

viernes, 24 de septiembre de 2010

TAREA: Empresa



Considere una empresa de ventas con varias sucursales, de la que se dispone la  información que muestran las tablas anexas
Importe las tablas y relaciónelas adecuadamente para dar respuesta a las preguntas que posteriormente se citan.
Al diseñar las tablas seleccione los tipos de datos que mas convengan.
En la tabla ventas el IDventa debe ser auto numérico
En la tabla empleados, el IDempleado debe ser auto numérico
IDsucursal debe ser entero
IDcargo debe ser entero
Trace las relaciones que convengan entre las tablas

Se requiere obtener
Listado con nombre y dirección de cada empleado
Listado con nombre y dirección de empleados de cierta ciudad (Monterrey)
Listado con nombre y dirección de empleados cuyo apellido paterno empiece con G
Listado de empleados por ciudad y puesto (nombre ciudad, puesto, nombre empleado)
Listado con nombre cargo y salario
Las ventas de una cierta fecha, por ejemplo 9 de sep.
El total de ventas en dicha fecha
Listado de ventas por vendedor
Ventas realizadas por un vendedor (y su total)
Listado de empleados con nombre y fecha de ingreso (por antigüedad decreciente)

martes, 21 de septiembre de 2010

TAREA: Hotel

Un hotel de la localidad necesita un sistema que realiza un seguimiento de sus reservaciones de habitaciones y huéspedes. Una habitación puede ser de un tipo particular y un rango de precios en particular. Los precios de las habitaciones varían de una habitación a otra (en función de su tipo) y de una temporada en temporada (dependiendo de la época del año).
La reserva de habitaciones puede incluir más de una habitación (un cliente puede reservar más de una habitación).
Los hechos proyecto se han definido como:
Las entidades obligadas deberán incluir:
■ Los clientes
■ Habitaciones
■ Reservas
■ Pagos
■ Tipos de Habitaciones
■ Rangos de Precio
■ Servicio de la habitación

Las entidades se relacionan de la siguiente manera:
■ Un cliente puede hacer una o muchas reservas.
■ Una reserva puede ser para una o muchas personas - la persona no es necesariamente la persona que hace la Reserva.
■ Una habitación puede estar en una o varias reservas.
■ Una habitación puede tener diversos Servicios en la habitación.

Cuando preguntemos a la base de datos es posible que necesitemos saber:
1.Cuantas  habitaciones están disponibles actualmente para la reserva?
2.Que las instalaciones están disponibles en una habitación en particular?
3. Que huéspedes se registraron hoy. Cuales en esta semana?

Crear un modelo de datos que permita responder a estas preguntas y permita que la información contenida la BD sea almacenada de manera lógica y estructurada.

Inicialmente se ha pensado utilizar las siguientes tablas:
##############
Tabla de clientes:
IDcliente
Nombre
Apellido
Género
Dirección
Teléfono
Correo
##############

Tabla Hoteles
IDHotel
Nombre
Dirección
Ciudad
Teléfono
Número de Estrellas
###############

tabla Reservaciones
IDReservacion
IDCliente
IDHotel
Reserva Fecha
Fecha de Llegada
Fecha de salida
IDcategoria
Condición
CostoTotal
###############

Tabla categorías de habitaciones
IDCategoría
Nombre de la categoría
Número de camas
################

Tabla Habitaciones
IDHotel
Número de habitaciones
IDCategoría
################

Tabla de precios de habitaciones
IDHotel
IDCategoría
Temporada
Duración
Habitación Precio
################


ver:

http://www.databasedev.co.uk/hotel_room_booking_system_data_model.html

TAREA: Hospital

Un hospital desea manejar los ingresos de sus pacientes con una base de datos. Para ello dispone de las siguientes tablas:

-Tabla de PACIENTES que tiene los siguientes campos:
·        IDpaciente [Código del paciente]   (llave)
·        Nombre
·        Apellidos
·        Dirección
·        Ciudad
·        Teléfono
·        Fecha de nacimiento
·        Sexo
·        Nº del Seguro Social
·        Alergias
·        Observaciones

-Tabla de CAMAS que tiene los siguientes campos:
      ·        IDcama [Código de la cama]   (llave)
      ·        IDpaciente
      ·        Fecha de ingreso
      ·        Fecha de alta.

-Tabla de MEDICOS que tiene los siguientes campos:
·        IDmedico [Código del médico]   (llave)
·        Nombre
·        Apellidos
·        Especialidad
·        Dirección
·        Ciudad
·        Teléfono

-Tabla de PATOLOGIAS que tiene los siguientes campos:
·        IDpatologia  [Código de patología] (llave)
·        Descripción

-Tabla de INCIDENCIAS que tiene los siguientes campos:
·        IDincidencia  [Nº de incidencia (llave)]
·        Código del paciente.
·        Código de patología.
·        Motivo de alta (curación o mejoría, traslado a otro centro, alta voluntaria, fallecimiento, etc)


INSTRUCCIONES:
·  Crear la base de datos HOSPITAL las tablas con sus correspondientes campos llave.
·  Establecer las relaciones exigiendo integridad referencial.
·  Diseñar un formulario para cada tabla teniendo en cuenta que el formulario de la tabla de incidencias presentará tres listas desplegables:
·        Una para el campo Motivo de alta con los valores más usuales.
·        Otra para el campo Código de paciente obteniendo los valores de una consulta sobre la tabla de pacientes, elija que estos aparezcan ordenados por Apellidos y Nombre.
·        Otra para el campo Código de patología obteniendo los valores de una consulta sobre la tabla de patologías en la que estas aparezcan ordenadas por descripción.
·  Introducir datos de 10 pacientes, 10 patologías y 10 incidencias.
·  Relación de pacientes que presentaron la patología Influenza durante el mes de enero de 2010.
·  Relación de pacientes de sexo femenino con la patología fractura de cadera que tuvieron que ser trasladados a otro centro.
·  Relación de pacientes menores de 16 años que ingresaron por Fractura.
·  ¿Cuántos pacientes fueron dados de alta a petición propia durante el año 2010?
·  Relación de pacientes de las localidades de Anáhuac y Piedras Negras que ingresaron con la patología de faringitis a partir de enero de 2010.
·  Número total de incidencias atendidas en el hospital durante los meses de verano de 2010 y que fueron dados de alta por curación o mejoría.