Cómo escribir consultas de Microsoft Access SQL desde cero

Se puede decir que Microsoft Access es la herramienta más poderosa de todo el conjunto de aplicaciones de Microsoft Office, aunque desconcierta (y en ocasiones asusta) a los usuarios avanzados de Office. Con una curva de aprendizaje más pronunciada que la de Word o Excel, ¿cómo se supone que alguien debe envolver su cabeza en el uso de esta herramienta? Esta semana, Bruce Epper analizará algunos de los problemas provocados por esta pregunta de uno de nuestros lectores..

Un lector pregunta:

Tengo problemas para escribir una consulta en Microsoft Access.

Tengo una base de datos con dos tablas de productos que contienen una columna común con un código de producto numérico y un nombre de producto asociado.

Quiero saber qué productos de la Tabla A se pueden encontrar en la Tabla B. Quiero agregar una columna llamada Resultados que contiene el nombre del producto de la Tabla A si existe, y el nombre del producto de la Tabla B cuando no existe. en la tabla A.

Tienes algún consejo?

Respuesta de Bruce:

Microsoft Access es un sistema de administración de bases de datos (DBMS) diseñado para su uso en máquinas Windows y Mac. Utiliza el motor de base de datos Jet de Microsoft para el procesamiento y almacenamiento de datos. También proporciona una interfaz gráfica para los usuarios que casi elimina la necesidad de comprender el lenguaje de consulta estructurado (SQL).

SQL es el lenguaje de comando utilizado para agregar, eliminar, actualizar y devolver la información almacenada en la base de datos, así como para modificar los componentes básicos de la base de datos, como agregar, eliminar o modificar tablas o índices..

Punto de partida

Si aún no está familiarizado con Access u otro RDBMS, le sugiero que comience con estos recursos antes de continuar:

• Entonces, ¿qué es una base de datos? Entonces, ¿qué es una base de datos, de todos modos? [MakeUseOf explica] Entonces, ¿qué es una base de datos, de todos modos? [MakeUseOf Explica] Para un programador o un entusiasta de la tecnología, el concepto de una base de datos es algo que realmente se puede dar por sentado. Sin embargo, para muchas personas el concepto de una base de datos en sí es un poco extraño ... Leer más donde Ryan Dube usa Excel para mostrar los conceptos básicos de las bases de datos relacionales.
• Una guía rápida para comenzar con Microsoft Access 2007 Una guía rápida para comenzar con Microsoft Access 2007 Una guía rápida para comenzar con Microsoft Access 2007 Leer más, que es una descripción general de Access y los componentes que forman una base de datos de Access.
• Un tutorial rápido a las tablas en Microsoft Access 2007 Un tutorial rápido a las tablas en Microsoft Access 2007 Un tutorial rápido a las tablas en Microsoft Access 2007 Leer más analiza cómo crear su primera base de datos y tablas para almacenar sus datos estructurados.
• Un tutorial rápido sobre consultas en Microsoft Access 2007 Un tutorial rápido sobre consultas en Microsoft Access 2007 Un tutorial rápido sobre consultas en Microsoft Access 2007 Read More busca los medios para devolver partes específicas de los datos almacenados en las tablas de la base de datos.

Tener una comprensión básica de los conceptos proporcionados en estos artículos hará que lo siguiente sea un poco más fácil de digerir.

Relaciones de base de datos y normalización

Imagina que estás dirigiendo una empresa que vende 50 tipos diferentes de widgets en todo el mundo. Tiene una base de clientes de 1,250 y en un mes promedio vende 10,000 widgets a estos clientes. Actualmente está utilizando una sola hoja de cálculo para realizar un seguimiento de todas estas ventas, de hecho, una única tabla de base de datos. Y cada año agrega miles de filas a tu hoja de cálculo..

Las imágenes anteriores forman parte de la hoja de cálculo de seguimiento de pedidos que está utilizando. Ahora diga que ambos clientes le compran widgets varias veces al año para que tenga más filas para ambos..

Si Joan Smith se casa con Ted Baines y toma su apellido, ahora es necesario cambiar cada fila que contiene su nombre. El problema se agrava si tiene dos clientes diferentes con el nombre 'Joan Smith'. Se ha vuelto mucho más difícil mantener sus datos de ventas consistentes debido a un evento bastante común.

Al utilizar una base de datos y normalizar los datos, podemos separar los elementos en varias tablas, como inventario, clientes y pedidos..

Solo mirando la parte del cliente de nuestro ejemplo, eliminaríamos las columnas para Nombre del cliente y Dirección del cliente y las colocaríamos en una nueva tabla. En la imagen de arriba, también he mejorado las cosas para un acceso más granular a los datos. La nueva tabla también contiene una columna para una clave principal (ID de cliente), un número que se usará para acceder a cada fila en esta tabla.

En la tabla original donde eliminamos estos datos, agregaríamos una columna para una clave externa (ClientID) que es lo que enlaza con la fila adecuada que contiene la información para este cliente en particular..

Ahora, cuando Joan Smith cambia su nombre a Joan Baines, el cambio solo debe hacerse una vez en la tabla de Clientes. Todas las demás referencias de las tablas unidas obtendrán el nombre correcto del cliente y un informe que analiza lo que Joan ha comprado durante los últimos 5 años recibirá todos los pedidos con sus nombres de soltera y casada sin tener que cambiar la forma en que se genera el informe.

Como beneficio adicional, esto también reduce la cantidad total de almacenamiento consumido.

Tipos de unión

SQL define cinco tipos diferentes de combinaciones: INTERIOR, IZQUIERDA EXTERIOR, DERECHO EXTERIOR, COMPLETO EXTERNO y CRUZ. La palabra clave OUTER es opcional en la sentencia SQL.

Microsoft Access permite el uso de INNER (predeterminado), IZQUIERDA EXTERIOR, DERECHA EXTERIOR y CRUZ. FULL OUTER no es compatible como tal, pero al usar IZQUIERDA EXTERNA, UNION ALL y RIGHT OUTER, se puede falsificar a costa de más ciclos de CPU y operaciones de E / S.

La salida de una combinación CROSS contiene cada fila de la tabla izquierda emparejada con cada fila de la tabla derecha. La única vez que he visto usar una combinación de CROSS es durante las pruebas de carga de los servidores de bases de datos.

Echemos un vistazo a cómo funcionan las combinaciones básicas, luego las modificaremos para adaptarlas a nuestras necesidades.

Comencemos creando dos tablas, ProdA y ProdB, con las siguientes propiedades de diseño.

El Autonumérico es un entero largo que se incrementa automáticamente y se asigna a las entradas a medida que se agregan a la tabla. La opción de Texto no se modificó, por lo que aceptará una cadena de texto de hasta 255 caracteres de longitud.

Ahora, rellénalos con algunos datos..

Para mostrar las diferencias en cómo funcionan los 3 tipos de unión, he eliminado las entradas 1, 5 y 8 de ProdA.

A continuación, crea una nueva consulta yendo a Crear> Diseño de consulta. Seleccione ambas tablas en el cuadro de diálogo Mostrar tabla y haga clic en Agregar, entonces Cerrar.

Haga clic en ProductID en la tabla ProdA, arrástrelo a ProductID en la tabla ProdB y suelte el botón del mouse para crear la relación entre las tablas.

Haga clic con el botón derecho en la línea entre las tablas que representan la relación entre los elementos y seleccione Unir Propiedades.

De forma predeterminada, la combinación de tipo 1 (INTERIOR) está seleccionada. La opción 2 es una combinación EXTERIOR IZQUIERDA y 3 es una combinación EXTERIOR DERECHA.

Primero veremos la unión INTERIOR, así que haga clic en Aceptar para cerrar el cuadro de diálogo.

En el diseñador de consultas, seleccione los campos que queremos ver de las listas desplegables.

Cuando ejecutamos la consulta (el signo de exclamación rojo en la cinta), mostrará el campo ProductName de ambas tablas con el valor de la tabla ProdA en la primera columna y ProdB en la segunda.

Observe que los resultados solo muestran valores donde ProductID es igual en ambas tablas. Aunque hay una entrada para ProductID = 1 en la tabla ProdB, no se muestra en los resultados, ya que ProductID = 1 no existe en la tabla ProdA. Lo mismo se aplica a ProductID = 11. Existe en la tabla ProdA pero no en la tabla ProdB.

Al utilizar el botón Ver en la cinta y al cambiar a Vista SQL, puede ver la consulta SQL generada por el diseñador utilizado para obtener estos resultados..

SELECCIONE ProdA.ProductName, ProdB.ProductName DE ProdA INNER JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Volviendo a la vista de diseño, cambie el tipo de unión a 2 (IZQUIERDA EXTERIOR). Ejecuta la consulta para ver los resultados..

Como puede ver, cada entrada en la tabla ProdA se representa en los resultados, mientras que solo las entradas en ProdB que tienen una entrada ProductID coincidente en la tabla ProdB se muestran en los resultados.

El espacio en blanco en la columna ProdB.ProductName es un valor especial (NULL) ya que no hay un valor coincidente en la tabla ProdB. Esto será importante más adelante.

SELECCIONE ProdA.ProductName, ProdB.ProductName DE ProdA LEFT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Intente lo mismo con el tercer tipo de unión (DERECHO EXTERIOR).

Los resultados muestran todo, desde la tabla ProdB, mientras que muestra valores en blanco (conocidos como NULL) donde la tabla ProdA no tiene un valor coincidente. Hasta ahora, esto nos acerca a los resultados deseados en nuestra pregunta del lector..

SELECCIONE ProdA.ProductName, ProdB.ProductName DE ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Uso de funciones en una consulta

Los resultados de una función también pueden devolverse como parte de una consulta. Queremos que aparezca una nueva columna llamada 'Resultados' en nuestro conjunto de resultados. Su valor será el contenido de la columna ProductName de la tabla ProdA si ProdA tiene un valor (no es NULL); de lo contrario, debe tomarse de la tabla ProdB.

La función de IF Inmediato (IIF) se puede utilizar para generar este resultado. La función toma tres parámetros. La primera es una condición que debe evaluarse como un valor Verdadero o Falso. El segundo parámetro es el valor que se devolverá si la condición es verdadera, y el tercer parámetro es el valor que se devolverá si la condición es falsa.

La construcción de la función completa para nuestra situación se ve así:

IIF (ProdA.ProductID es nulo, ProdB.ProductName, ProdA.ProductName)

Observe que el parámetro de condición no comprueba la igualdad. Un valor Nulo en una base de datos no tiene un valor que pueda compararse con cualquier otro valor, incluido otro Nulo. En otras palabras, Null no es igual a Null. Siempre. Para superar esto, en cambio, verificamos el valor usando la palabra clave 'Es'.

También podríamos haber usado 'Is Not Null' y haber cambiado el orden de los parámetros True y False para obtener el mismo resultado..

Al colocar esto en el Diseñador de consultas, debe escribir toda la función en la entrada Campo :. Para que cree la columna 'Resultados', debe usar un alias. Para hacer esto, inicie la función con 'Resultados:' como se ve en la siguiente captura de pantalla.

El código SQL equivalente para hacer esto sería:

SELECCIONE ProdA.ProductName, ProdB.ProductName, IIF (ProdA.ProductID es nulo, ProdB.ProductName, ProdA.ProductName) COMO RESULTADOS DE ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Ahora, cuando ejecutamos esta consulta, producirá estos resultados..

Aquí vemos para cada entrada donde la tabla ProdA tiene un valor, ese valor se refleja en la columna Resultados. Si no hay una entrada en la tabla de ProdA, la entrada de ProdB aparece en Resultados, que es exactamente lo que pidió nuestro lector.

Para obtener más recursos para aprender Microsoft Access, consulte Cómo aprender Microsoft Access: 5 Recursos en línea gratuitos Cómo aprender Microsoft Access: 5 Recursos en línea gratuitos Cómo aprender Microsoft Access: 5 Recursos en línea gratuitos ¿Tiene que administrar una gran cantidad? ¿de datos? Usted debe mirar en Microsoft Access. Nuestros recursos de estudio gratuitos pueden ayudarlo a comenzar y aprender las habilidades para proyectos más complejos. Lee mas .