SUBAGRUPAR

Puede darse el caso de que en una misma consulta, necesitemos obtener resultados agrupados en distintos niveles. Un ejemplo sobre la base de datos Neptuno:

Resumen de importe de pedidos agrupado por Categoría de producto.

Select First(Categorías.NombreCategoría) As Categoria,
       Round(Sum(T.Cantidad * T.PrecioUnidad * (1-T.Descuento)), 2) As Importe
From (
     (
     (
     Pedidos 
     Inner Join
     [Detalles de pedidos] As T On Pedidos.IdPedido = T.IdPedido 
     )
     Inner Join 
     Productos On T.IdProducto = Productos.IdProducto
     )
     Inner Join
     Categorías On Productos.IdCategoría = Categorías.IdCategoría
Group By Productos.IdCategoría

Pero si necesitamos, además del importe por categoría, obtener resultados parciales, por ejemplo, para alguno de los países a los que se envían pedidos, vamos a tener un dilema con la cláusula Group By, puesto que si añadimos País, perdemos la posibilidad de mostrar el importe total por Categoría.

Por supuesto hay formas de resolverlo:

1.     Utilizar una consulta de referencias cruzadas manteniendo la agrupación por Categoría. Los países los utilizaremos como encabezado de columna y emplearemos el operador In en la cláusula Pivot para restringir los resultados a los países que nos interese.

2.     Agrupar por Categoría y País. El total general por Categoría lo obtendremos con una subconsulta.

Propongo una tercera: incorporar una expresión dentro de la función de agregado, en este ejemplo Sum(), que filtre los resultado que procesa la función:

Select First(Categorías.NombreCategoría) As Categoria,
       Round(Sum(T.Cantidad * T.PrecioUnidad * (1-T.Descuento)), 2) As Importe,
       Round(Sum(T.Cantidad * T.PrecioUnidad * (1-T.Descuento) * Abs(Pedidos.PaísDestinatario = "Alemania")), 2) As [Importe (Alemania)],
       Round(Sum(T.Cantidad * T.PrecioUnidad * (1-T.Descuento) * Abs(Pedidos.PaísDestinatario = "Francia")), 2) As [Importe (Francia)]
From (
     (
     Pedidos
     Inner Join
     [Detalles de pedidos] As T On Pedidos.IdPedido = T.IdPedido
     )
     Inner Join
     Productos On T.IdProducto = Productos.IdProducto
     )
     Inner Join
     Categorías On Productos.IdCategoría = Categorías.IdCategoría
Group By Productos.IdCategoría

Ramon Poch. Terrassa a 15/11/2020.

ELIMINAR REGISTROS DUPLICADOS

Si utilizamos el “Asistente para búsqueda de duplicados” de Access para crear una consulta que nos permita eliminar registros duplicados, nos encontraremos con el problema de que también borraremos los registros originales, es decir, en vez de mover los repetidos y dejar uno, los eliminaremos todos.

De las diversas formas que hay para abordar correctamente esta tarea, a mí me gusta particularmente la que a continuación explico, pues permite definir nítidamente cual es el registro “original” o “primero” que no hay que eliminar.

Como siempre utilizaré un ejemplo para ilustrar el método:

--Tabla de ejemplo

Create Table Alumnos (Id Counter Primary Key, Nombre Text (100), DNI Text (9));

--Insertamos valores en la tabla (algunos de ellos duplicados)

Insert Into Alumnos (Nombre, DNI) Values ("Alumno1", "12345678A");

Insert Into Alumnos (Nombre, DNI) Values ("Alumno2", "12345678B");

Insert Into Alumnos (Nombre, DNI) Values ("Alumno3", "12345678C");

Insert Into Alumnos (Nombre, DNI) Values ("Alumno1", "12345678A");

Insert Into Alumnos (Nombre, DNI) Values ("Alumno1", "12345678A");

--Este es el codigo sql propuesto:

Delete Alumnos.*

From Alumnos

Where Alumnos.Id Not In

       (

             Select First(Id)

             From

                    (

                           Select Top 100 Percent Alumnos.*

                           From Alumnos

                           Order By Nombre, DNI, Id

                    )

             Group By Nombre, DNI

       )

      

--Comprobamos los resultados:

Select * From Alumnos

Resultados
Id	Nombre	DNI
1	Alumno1	12345678A
2	Alumno2	12345678B
3	Alumno3	12345678C

Explicación: en la clausula Where excluimos los registros devueltos por una subconsulta. En esta mostramos el primer Id (campo clave) de cada conjunto susceptible de ser un duplicado (Alumno y DNI, aunque probablemente con este último seria suficiente), para ello, agrupamos por Nombre y DNI y obtenemos el primer Id mediante First. Para asegurarnos que el Id devuelto sea el “primero” es imprescindible establecer un criterio de ordenación por lo que en vez de basar la subconsulta directamente sobre la tabla Alumnos, utilizamos una tabla derivada donde empleamos el predicado Top y establecemos un orden. En este punto cabe recordar que cualquier instrucción “Order By” que no esté dentro de una consulta de selección (afectando directamente al orden de los resultados finales,) va a ser ignorada por el motor sql de Access, a no ser que utilicemos el predicado Top. La palabra reservada Percent se utiliza para especificar un porcentaje de registros a devolver sobre el total. Puesto que no es esta la intención, el valor de Percent lo establecemos a 100 (la totalidad de registros).

Ramon Poch. Terrassa, a 15/09/2018

CÁLCULO DE DÍAS LABORABLES ENTRE FECHAS

Existen numerosos ejemplos de funciones vba con este fin, pero las que conozco utilizan un bucle para recorrer todas las fechas y evaluar el día de la semana para contabilizarlas o no. Dado que en Access "el primer día" del tipo de datos FechaHora es el 01-01-1900, utilizar un bucle tampoco es demasiado costoso, pero me ha parecido un reto prescindir de él.

Se trata de un método para calcular dentro de una instrucción sql y sin necesidad de emplear vba, los días laborables entre fechas (ambas incluidas).

Para una mayor claridad en el código, utilizo dos parámetros, de nombre suficientemente descriptivo: FirstDate y LastDate. Obviamente pueden substituirse por valores o campos.

WorkDays:

Parameters FirstDate DateTime, LastDate DateTime;

Select DateDiff("d",FirstDate, LastDate, 2) + 1 As Days ,

       ((Days -1 - ((7 + 6 - WeekDay(FirstDate, 2)) Mod 7)) \ 7)

       + Abs(DateAdd("d", (7 + 6 - WeekDay(FirstDate, 2)) Mod 7, FirstDate) <= LastDate)         As SaturDays, 

       ((Days -1 - ((7 + 7 - WeekDay(FirstDate, 2)) Mod 7)) \ 7)

       + Abs(DateAdd("d", (7 + 7 - WeekDay(FirstDate, 2)) Mod 7, FirstDate) <= LastDate)         As SunDays,

       Days - SaturDays - SunDays As WorkDays;

De hecho, mas que un procedimiento para calcular días hábiles, lo que obtenemos es el número de días que son sábado (líneas dos y tres) y domingo (líneas cinco y seis), para finalmente descontar ambas cantidades del total de días entre ambas fechas.

He resaltado en amarillo algunos números, es la parte de la lógica que discrimina la clase de día de la semana (6 para el sábado y 7 para el domingo). Si se quiere adaptar el código para obtener el número de fechas que coincidan con cualquier otro día de la semana, solo hay que modificar el número resaltado.

Por otra parte, aclarar que el valor 2 en las funciones de fecha, es para indicar que el primer día de la semana es el lunes (corresponde al valor de la enumeración: VbDayOfWeek.vbMonday).

Ramon Poch. Terrassa a 20/03/2018.

MOSTRAR EN UNA CONSULTA ELEMENTOS SELECCIONADOS DE UN LISTBOX

Vamos a ver como mostrar directamente en una consulta los valores activados de un listbox que admita selección múltiple. 
Para este ejemplo utilizo un LisBox llamado MyList, en un formulario de nombre MyForm. El ListBox tiene la propiedad "Selección múltiple" establecida a "Simple", dos columnas y su origen de la fila es la tabla "Categorías" de la base de datos de ejemplo Neptuno.

SELECT Eval("Forms.MyForm.MyList.Column(" & (T10.Num * 10) + T0.Num  & ", 1)")

FROM

(

       Select Id - 2 AS Num From msysobjects Where Id Between 2 And 5

       Union All

       Select Id + 2 AS Num From msysobjects Where Id Between 2 And 5

       Union All

       Select Id + 6 AS Num From msysobjects Where Id Between 2 And 3

) As T0,

(

       Select Id - 2 AS Num From msysobjects Where Id Between 2 And 5

       Union All

       Select Id + 2 AS Num From msysobjects Where Id Between 2 And 5

       Union All

       Select Id + 6 AS Num From msysobjects Where Id Between 2 And 3

) As T10

WHERE Eval("Forms.MyForm.MyList.Selected(" & (T10.Num * 10) + T0.Num & ")") = True

En definitiva se trata de utilizar la función Eval en la clausula Where como intermediario para verificar si la linea del ListBox está o no seleccionada y nuevamente la función Eval en la clausula Select para obtener el valor de la columna.

La clausula From de esta consulta está explicada mas detalladamente en el artículo: Series Numericas (sin tabla nums) de este mismo blog.

Ramon Poch. Terrassa a 17/02/2018.

ON DUPLICATE KEY UPDATE, EQUIVALENTE EN ACCESS SQL

La sentencia On duplicate Key Update (MySql), permite insertar o actualizar registros, dependiendo de si existe o no la clave en la tabla. Me parece algo sumamente útil. Veamos como implementar algo parecido en Access, y para ello nada más elocuente que un ejemplo :

Supongamos que tenemos una tabla donde anotamos el estoc de productos :

Create Table Stocs (IdArticulo Long Primary Key, Cantidad Single);

Cuando recibimos un nuevo pedido, necesitamos anotar la cantidad del mismo en la tabla. Pero para no tener que preocuparnos en buscar si IdArticulo existe, en cuyo caso empleariamos una sentencia Insert, o en el supuesto contrario usariamos Update, podemos utilizar una sql como esta :

Update Stocs
            Right Join
            (Select Top 1 [IdArticulo ?] As IdArticulo From MSysObjects) As T
            On Stocs.IdArticulo = T.IdArticulo
Set Stocs.IdArticulo = T.IdArticulo, Stocs.Cantidad = Nz(Stocs.Cantidad,0) + 1;

... utilizamos una tabla derivada con orígen en MSysObjects para obtener un único registro con el valor que definamos en el parámetro [IdArticulo ?] . Al realizar un Update, teniendo las dos tablas relacionadas con Right Join, si en la tabla de la izquierda no hay registro coincidente con la de la derecha, el efecto práctico será la inserción de un nuevo registro, mientras que en caso contrario, la sentencia Update actuará de la forma esperada. Para modificar o insertar valores en el resto de campos, podemos utilizar valores o expresiones según nos convengan en la clausula Set. En este caso y a título de ejemplo, incrementaremos en una unidad el valor del campo Cantidad (si el registro todavia no existe el valor asignado será 1).

Ramon Poch. Terrassa a 07/05/2014.

INTERCALACIÓN AI EN ACCESS (NO DISTINGUIR ACENTOS)

Por defecto, el motor sql de Access distingue siempre el uso de acentos en las comparaciones de cadena : "acción" <> "accion". Por el contrario obvia el uso de mayúsculas y minúsculas : "Acción" = "acción". Sobre esto último, ya hablé en este artículo : Distinguir mayúsculas y minúsculas. Hablemos ahora de los acentos, cuestión de vital importancia a la hora de realizar búsquedas sobre texto.

Para evitar que una búsqueda tenga en cuenta los acentos, es habitual crear una función en vba. Dichas funciones pueden seguir dos estrategias : 

- Modificar el valor pasado al parámetro de búsqueda. Un magnífico ejemplo de esta estrategia es la función Buscaacent de Chea.

- Modificar los valores del campo donde se busca, quitando todos los acentos del mismo, y también realizar la misma operación en el parámetro de búsqueda. 

Creo que la mejor opción es la primera, pues la función se ejecutará una sola vez, mientras que en el segundo caso, la función deberá actuar sobre el campo escogido en todos los registros del conjunto de datos, además de sobre el valor de búsqueda. Es decir, si tenemos una tabla de Clientes con 10.000 registros, la función se ejecutará 10.000 veces. A pesar de esto propongo una solución basada en la segunda posibilidad, pero utilizando la función StrConv que viene con el módulo Strings de la librería VBA.  Esta solución será útil si no queremos depender de tener un módulo con la función personalizada, o si deseamos utilizar la propiedad filter de los recordsets Ado, ya que Filter es mucho más restrictiva en el uso de comodines para el operador Like que el entorno Sql de Access.

StrConv adapta una cadena según el tipo de conversión escogida en el segundo parámetro de la función, y según el LocaleID definido en la tercera. A priori esto no da mucho 'juego' en cuanto a los acentos, pero resulta que si usamos idiomas que emplean un alfabeto distinto del nuestro, la mayoría obvian los acentos de las vocales. Uno de los que he probado y que eliminan correctamente los acentos es el Coreano, cuyo LCid es 1042.

Un ejemplo :

Select StrConv("aâàáäeëèéiïíîöôoòúuüûñ", 2, 1042)

devuelve :

aaaaaeeeeiiiioooouuuun

... y sí, a la 'ñ' le ha quitado el acento. Si no hacemos de esto una cuestión de honor, StrConv bien puede emplearse para obviar los acentos.

Así pues, para realizar una búsqueda obviando los acentos, deberiamos hacer algo parecido a esto (he empleado la tabla Clientes de Neptuno.mdb) :

Select NombreCompañía, NombreContacto
From Clientes
Where StrConv(NombreContacto, 2, 1042) Like '*hernandez*'

... y el resultado :

Nombre de compañía	Nombre del contacto
HILARIÓN-Abastos	Carlos Hernández

Ramon Poch. Terrassa a 27/05/2013

JUGANDO CON LAS MATES (NÚMEROS PRIMOS)

Un pequeño divertimento : generar una tabla de números primos (de 0 a 1000) usando la tabla auxiliar Nums (con un único campo, entero, indexado -sin duplicados-, de nombre Num, con valores de 1 a 1000).

Select Nums.Num * T.Num
From Nums, Nums As T
Where Nums.Num * T.Num Between 1 And 1000
Group By Nums.Num * T.Num
Having Count(Nums.Num * T.Num)=2;

... y si queremos prescindir de la tabla Nums :

SELECT R1.Num * R2.Num
FROM
(
Select (T2.Num*10)+Nums.Num+1 As Num
  From
(
Select (T1.Num*10)+Nums.Num As Num
  From
(
    Select Id - 2 AS Num From msysobjects Where Id Between 2 And 5
    Union All
    Select Id + 2 AS Num From msysobjects Where Id Between 2 And 5
    Union All
    Select Id + 6 AS Num From msysobjects Where Id Between 2 And 3
  ) As Nums,
  (
    Select Id - 2 AS Num From msysobjects Where Id Between 2 And 5
    Union All
    Select Id + 2 AS Num From msysobjects Where Id Between 2 And 5
    Union All
    Select Id + 6 AS Num From msysobjects Where Id Between 2 And 3
  ) As T1
) As T2,
  (
  Select Num
  From
  (
    Select Id - 2 AS Num From msysobjects Where Id Between 2 And 5
    Union All
    Select Id + 2 AS Num From msysobjects Where Id Between 2 And 5
    Union All
    Select Id + 6 AS Num From msysobjects Where Id Between 2 And 3
  ) 
  ) As Nums
) AS R1,
(
  Select (T2.Num*10)+Nums.Num+1 As Num
  From
  (
  Select (T1.Num*10)+Nums.Num As Num
  From
  (
    Select Id - 2 AS Num From msysobjects Where Id Between 2 And 5
    Union All
    Select Id + 2 AS Num From msysobjects Where Id Between 2 And 5
    Union All
    Select Id + 6 AS Num From msysobjects Where Id Between 2 And 3
  ) As Nums,
  (
    Select Id - 2 AS Num From msysobjects Where Id Between 2 And 5
    Union All
    Select Id + 2 AS Num From msysobjects Where Id Between 2 And 5
    Union All
    Select Id + 6 AS Num From msysobjects Where Id Between 2 And 3
  ) As T1
  ) As T2,
  (
  Select Num
  From
  (
    Select Id - 2 AS Num From msysobjects Where Id Between 2 And 5
    Union All
    Select Id + 2 AS Num From msysobjects Where Id Between 2 And 5
    Union All
    Select Id + 6 AS Num From msysobjects Where Id Between 2 And 3
  )
  ) As Nums
) AS R2
WHERE R1.Num * R2.Num Between 1 And 1000
GROUP BY R1.Num * R2.Num
HAVING Count(R1.Num * R2.Num)=2

Ramon Poch. Terrassa a 10/05/2010.