miércoles, 23 de septiembre de 2015

La Tabla Números

Primero agradecer a José Bengoechea Ibaceta (Chea) y Emilio Sancha, por haber contribuido en el mejoramiento, revisión y agrandado de estas líneas.

En Access como en la vida, siempre hay varias formas de llegar a un resultado, pero para los amantes de las consultas, la tabla números más que un aliado es una herramienta indispensable para resolver problemas que sin ellas sería realmente difícil de hacer, aunque en lugar de hacerlo en la consulta lo podemos hacer en vba, siempre se puede generar un bucle en un recordset o hacer alguna función para hacer lo mismo.

Si observamos muchos Maestros como Chea, Raipon, Julián Sánchez, Emilio Sancha, Patxi Sanz, Mihura por mencionar solo algunos, la utilizan en sus bases de datos, de hecho hay algunos que afirman que la tabla números siempre debe estar presente en una base de datos, y esta solo consta de un campo numérico, que comienza en 0 o 1 y llega hasta donde lo requerimos,

En todos estos casos podemos usar la tabla números para obtener una solución:

Generar un rango de fechas
Encontrar la fecha que falta
Contar fechas
Repetir registros
Generar segmentos de hora
Separar una cadena
Calendario Mensual

La gran mayoría de estos ejemplos son sacados del Foro de Access y VBA, y fue realizado por alguno de los Maestros antes mencionados.

Lo primero es generar la tabla números, Raipon tiene una forma de generarla como consulta a partir una tabla del sistema y Emilio Sancha con solo del 1 al 31 genera con DateSerial todos los días desde 2001 hasta el 2027. Yo en Excel puse 0, 1 y 2 y los copio como serie hasta el número que quiero en este caso 1999 pero dependiendo de lo que hagan con ella, 2000 puede quedarse muy corto, selecciono y copio la columna y la pego en el campo Num de Access.

Nums
Num
0
1
2
3
. .
1999

Algunos comienzan la tabla números con el 0 otros desde el 1 la verdad no es importante depende lo que vamos a hacer después, por ejemplo si comenzamos con el 0 y queremos agregar registros tenemos que agregar al número+1 para obtener lo que deseamos.

Cuando ponemos en una consulta dos tablas sin relación, lo que obtenemos es un producto cartesiano que es la totalidad de los pares ordenados, en otras palabras obtenemos todas las posibles combinaciones de filas de ambas tablas, por lo tanto si nuestra dos tablas tienen 1,000 registros cada una, la tabla resultante es de 1 millon de registros, por lo tanto cuando usemos la tabla de números es preferible usar una tabla intermedia con solo los rangos que necesitemos, si no lo hacemos así corremos el riesgo que la consulta puede tardar en ejecutarse.

Generar un rango de fechas:

Partimos que tenemos una tabla llamada RangoFechas y queremos que nos muestre todas las fechas que están dentro del rango.

RangoFechas
Id
Inicio
Fin
1
01/09/2015
04/09/2015
2
02/09/2015
06/09/2015


Para eso hacemos una consulta con las dos tablas sin relacionar, esta nos dará la multiplicación de la tabla números x 2 registros de la tabla RangoFechas, si por ejemplo agregamos de la tabla Nums el campo Num tenemos dos veces repetidos todos los campos Num.

Siempre que tratemos con fechas vamos a agregar CDate o CVDate, para que Access convierta a fechas el campo, además CDate reconoce la configuración regional del sistema, así Access reconoce que 5/1/2015 es 5 de enero.

En base a esto es muy fácil agregar nuestra serie de fechas si en un campo ponemos [Inicio]+[Num] podemos observar que ya tenemos nuestras fechas iniciales y cada dia subsiguiente, solo nos falta detenerlo en la fecha fin, que en criterios tendríamos que agregar <=[Fin] la consulta completa queda así:

SELECT Nums.Num, RangoFechas.Id, CVDate([Inicio])+[Num] AS Rango
FROM Nums, RangoFechas
WHERE (((CVDate([Inicio])+[Num])<=CVDate([Fin])))
ORDER BY RangoFechas.Id, CVDate([Inicio])+[Num];

CtaRangoFechas
Num
Id
Rango
0
1
01/09/2015
1
1
02/09/2015
2
1
03/09/2015
3
1
04/09/2015
0
2
02/09/2015
1
2
03/09/2015
2
2
04/09/2015
3
2
05/09/2015
4
2
06/09/2015

Encontrar las fechas que faltan:

Ahora vamos a basarnos en una tabla que llamaremos fechaFaltante que contiene:

fechafaltante
Id
Nombre
Fecha
1
1
01/09/2015
2
1
02/09/2015
3
1
03/09/2015
4
1
06/09/2015
5
2
02/09/2015
6
2
03/09/2015
7
2
04/09/2015
8
2
06/09/2015
9
2
08/09/2015

Nos faltan las fechas 4/09/15 y 5/09/15 del Nombre 1 y el 5/09/15 y 7/09/15 del Nombre 2. Para hacerlo necesitamos obtener la fecha Mínima y la Máxima por Nombre, después crear con esas fechas una consulta de rangos por fechas y posteriormente comparar la consulta creada con la de fechasfaltante diciéndole que solo me de las nulas de la fechafaltante, ¿fácil verdad?

Creamos una consulta de totales de la tabla fechafaltante con tres campos Inicio:[Fecha] en totales como Min, Fin:[Fecha] en totales como Max y el campo Nombre y obtenemos :

SELECT Min(CVDate([Fecha])) AS Inicio, Max(CVDate([Fecha])) AS Fin, fechafaltante.Nombre
FROM fechafaltante
GROUP BY fechafaltante.Nombre;

Consulta1
Inicio
Fin
Nombre
01/09/2015
06/09/2015
1
02/09/2015
08/09/2015
2

Ahora con la consulta1 generamos un rango de fechas como en el punto anterior:

SELECT CVDate([Inicio])+[num] AS Lasfechas, Consulta1.Nombre
FROM Nums, Consulta1
WHERE (((CVDate([Inicio])+[num])<=CVDate([Fin])))
ORDER BY Consulta1.Nombre, CVDate([Inicio])+[num];

Consulta2
Lasfechas
Nombre
01/09/2015
1
02/09/2015
1
03/09/2015
1
04/09/2015
1
05/09/2015
1
06/09/2015
1
02/09/2015
2
03/09/2015
2
04/09/2015
2
05/09/2015
2
06/09/2015
2
07/09/2015
2
08/09/2015
2

Y estamos listos para generar la consulta de nuestras fechasfaltantes, vamos a agregar la consulta2 y la tabla de fechasfaltantes y relacionar Lasfechas=> Fecha y Nombre=> Nombre vamos a hacer un LEFT JOIN, es decir incluir todos los registros de Consulta2 y solo los registros de fechafaltante donde los campos combinados sean iguales y agregamos los campos Lasfechas y Nombre de la consulta2 y Fecha de la tabla fechafaltante y le decimos en criterio Es Nulo, algo así:

SELECT Consulta2.Lasfechas, Consulta2.Nombre
FROM Consulta2 LEFT JOIN fechafaltante ON (Consulta2.Nombre = fechafaltante.Nombre) AND (Consulta2.[Lasfechas] = fechafaltante.Fecha)
WHERE (((fechafaltante.Fecha) Is Null))
ORDER BY Consulta2.Lasfechas, Consulta2.Nombre;


Ctafechafaltante
Lasfechas
Nombre
04/09/2015
1
05/09/2015
1
05/09/2015
2
07/09/2015
2

En la base de datos del ejemplo para no tener consultas extras al más puro estilo de Julián, agregué las consulta1 y consulta2 dentro de la Ctafechafaltante y así lo seguiré haciendo en el resto del artículo.

Contar fechas:
Si tenemos una serie de fechas y requerimos saber cuántos días son de cada mes, por ejemplo tenemos la siguiente tabla:

Contarfechas
Id
Desde
Hasta
1
16/07/2015
26/10/2015
2
23/01/2015
17/05/2015

Ya sabemos que tenemos que crear nuestro rango de fechas, con la tabla números, tal como lo hemos hecho en los ejemplos anteriores y crear un campo que saque el mes y el año por si son varios años y el campo id, teniendo todo la convertimos a una de consulta de totales, el campo CVFecha([Desde])+[Num] le debemos indicar en totales: Dónde pues de lo contrario fallará, recordemos que "Dónde" sirve para indicar que ese campo no lo vas a usar para recoger, agrupar o calcular, sino únicamente para filtrar los registros que debe devolver la consulta.
Hecho esto agregamos un campo extra que llamaremos Días: CVFecha([Desde])+[num] y en totales: Cuenta y nuestra consulta esta lista:

SELECT Format(CVDate([Desde])+[num],"yyyy-mm") AS Periodo, Contarfechas.Id, Count(CVDate([Desde])+[num]) AS Días
FROM Nums, Contarfechas
WHERE (((CVDate([Desde])+[Num])<=CVDate([Hasta])))
GROUP BY Format(CVDate([Desde])+[num],"yyyy-mm"), Contarfechas.Id
ORDER BY Contarfechas.Id;


CtaContardias
Periodo
Id
Días
2015-07
1
16
2015-08
1
31
2015-09
1
30
2015-10
1
26
2015-01
2
9
2015-02
2
28
2015-03
2
31
2015-04
2
30
2015-05
2
17

Repetir registros
Una posibilidad muy interesante es cuando tenemos la necesidad de duplicar registros, por ejemplo imaginemos que tenemos una tabla:

RepetirRegistros
Id
Nombre
Cantidad
1
Emilio
5
2
Rolando
3
Y queremos repetir tantas veces los registros como el número en cantidad, agregamos las dos tablas y todos los campos de Repetir Registro y también el campo Num, como nuestra tabla números comienza en 0 tenemos que agregar +1 queda [Num]+1, y en criterio le ponemos <=[Cantidad] y obtenemos esto:
SELECT RepetirRegistros.Id, RepetirRegistros.Nombre, RepetirRegistros.Cantidad
FROM RepetirRegistros, Nums
WHERE ((([Num]+1)<=[Cantidad]))
ORDER BY RepetirRegistros.Id;


CtaRepetirRegistros
Id
Nombre
Cantidad
1
Emilio
5
1
Emilio
5
1
Emilio
5
1
Emilio
5
1
Emilio
5
2
Rolando
3
2
Rolando
3
2
Rolando
3

Generar segmentos de hora

Si tenemos que generar segmentos de horas de digamos 15 minutos de algunas horas en la mañana y otras en la tarde, la tabla de números puede hacernos el trabajo. Vamos a generarlo del día de hoy, para eso necesitamos una consulta con la tabla números y con un único campo del día de hoy y la tabla números: Fechahoy: CVFecha(Fecha())+[num] y en criterios <=CVFecha(Fecha()) para que solo tenga un registro.

SELECT CVDate(Date())+[num] AS fechahoy
FROM Nums
WHERE (((CVDate(Date())+[num])<=CVDate(Date())));


Consulta1
Fechahoy
23/09/2015

Con la ayuda de Chea, que me indico la forma, podemos simplificar, y solo hacer la consulta final, la podemos hacer de dos formas con CVFecha([Fechahoy]+([Num]*(1/24)*0.25)), donde 1/24 me da las horas y .25 cada 15 minutos o lo hacer con AgregFecha("n",([Num]*15),[Fechahoy]), donde “n” son minutos y queremos múltiplos de 15, de cualquier forma funciona y le agregamos la cláusula “In”  para que me de solo las horas que requiero, y queda así:

SELECT DISTINCT Consulta1.Fechahoy, CVDate([Fechahoy]+([Num]*(1/24)*0.25)) AS Expr1
FROM Nums, (SELECT Date()+[Num] AS Fechahoy FROM Nums WHERE (((Date()+[Num])<=CVDate(Date()))))  AS Consulta1
WHERE (((Nums.Num)<96) AND ((Hour(CVDate([Fechahoy]+([Num]*(1/24)*0.25)))) In (8,9,10,15,16,17)));

Y con la función de Agregar fecha:


SELECT DISTINCT Consulta1.Fechahoy, DateAdd("n",([Num]*15),[Fechahoy]) AS FechaMinutos, Hour(DateAdd("n",([Num]*15),[Fechahoy])) AS Expr1
FROM Nums, (SELECT Date()+[Num] AS Fechahoy FROM Nums WHERE (((Date()+[Num])<=CVDate(Date()))))  AS Consulta1
WHERE (((Hour(DateAdd("n",([Num]*15),[Fechahoy]))) In (8,9,10,15,16,17)) AND ((Nums.Num)<96));



Consulta3
FechaMinutos
Expr1
23/09/2015 08:00:00 a.m.
8
23/09/2015 08:15:00 a.m.
8
23/09/2015 08:30:00 a.m.
8
23/09/2015 08:45:00 a.m.
8
23/09/2015 09:00:00 a.m.
9
23/09/2015 09:15:00 a.m.
9
23/09/2015 09:30:00 a.m.
9
23/09/2015 09:45:00 a.m.
9
23/09/2015 10:00:00 a.m.
10
23/09/2015 10:15:00 a.m.
10
23/09/2015 10:30:00 a.m.
10
23/09/2015 10:45:00 a.m.
10
23/09/2015 03:00:00 p.m.
15
23/09/2015 03:15:00 p.m.
15
23/09/2015 03:30:00 p.m.
15
23/09/2015 03:45:00 p.m.
15
23/09/2015 04:00:00 p.m.
16
23/09/2015 04:15:00 p.m.
16
23/09/2015 04:30:00 p.m.
16
23/09/2015 04:45:00 p.m.
16
23/09/2015 05:00:00 p.m.
17
23/09/2015 05:15:00 p.m.
17
23/09/2015 05:30:00 p.m.
17
23/09/2015 05:45:00 p.m.
17


Separar una cadena
Una pequeña joya es lo que regalo raipon para separar cadenas, imaginemos que importamos una tabla y nos sale así:

Separar
id
Nombre
1
Emilio, José, Alberto, Pablo
2
María, Jacinta, Tamara
Y nosotros necesitamos un registro por cada nombre identificando el id, digamos que lo inverso a una consulta de unión. Con la tabla números y haciendo uso de las funciones de texto podemos conseguir lo que queremos.
Vamos a jugar un rato con las funciones de texto y después lo integramos para lograrlo
Si tenemos el texto “Emilio, José, Alberto, Pablo” y queremos separar Emilio, podríamos usar en la ventana inmediata:
?Left(“Emilio, José, Alberto, Pablo”,6) y el resultado es Emilio y si quiere obtener José tendría que usar Mid algo así: Mid(“Emilio, José, Alberto, Pablo”,8,4) el resultado es José, como puedo ir variando ese 8,4 por un 13,7 para el siguiente y por un 21,5 el restante, y mejor aún, que Access lo realice solo. Ocupamos usar InStr para encuentre la posición de cada coma.
Pero primero debemos ver cuantos registros hemos de repetir, ya sabemos que con el campo Num y poniendo en criterios la cantidad lo hacemos, para poner la cantidad podemos contar los caracteres con Len, y si agregamos un campo para que localice todos “/” y también los que comienzan con 0 para los dos primeros, tendríamos esto:

SELECT tbl_musica.Campo1, tbl_musica.Artista, Nums.Num
FROM Nums, tbl_musica
WHERE (((Nums.Num)<=Len([Artista])) AND ((Mid([Artista],IIf([Num]=0,1,[Num]),1))="/")) OR (((Nums.Num)=0 And (Nums.Num)<=Len([Artista])));

Consulta1
Campo1
Artista
Num
1
José José
0
2
Luis/Pepe/Alberto
0
2
Luis/Pepe/Alberto
5
2
Luis/Pepe/Alberto
10

Ya casi funciona solo resta agregar la función Mid para separar el nombre en cada “/” , la consulta completa queda:

SELECT Separar.id, Mid([Nombre],[Num]+1,Abs(InStr([Num]+1,[Nombre] & ",",",")-[Num]-1)) AS Expr1
FROM Nums, Separar
WHERE (((Mid([Nombre],IIf([Num]=0,1,[Num]),1))=",") AND ((Nums.Num)<=Len([Nombre]))) OR (((Nums.Num)=0 And (Nums.Num)<=Len([Nombre])))
ORDER BY Separar.id;

CtaSeparar
id
Expr1
1
Pablo
1
Alberto
1
José
1
Emilio
2
Tamara
2
Jacinta
2
María
Como observamos podemos construir consultas muy poderosas y en muy poco tiempo gracias a la tabla números, estos son solo unos pequeños ejemplos de lo que podemos conseguir con ella, aplicaciones muchas por ejemplo podemos crear en el año los días laborales incluidos los festivos, por poner otro ejemplo.

Calendario Mensual

Por último Emilio Sancha, de una idea de raipon nos enseña a hacer un calendario mensual, vamos a comenzar creando nuestra primer consulta que solo va a contener la tabla números, pero como nuestra tabla números tiene del 0 al 2000, nos dará error si intentamos hacerlo con tantos números, así que lo primero es reducir la cantidad, por ejemplo Emilio usa del 0 al 31 y nos da del año 2001 al 2029, así que primero hacemos nuestra consulta hasta el 31 y de esta la repetimos tres veces:

SELECT Nums.Num
FROM Nums
WHERE (((Nums.Num)<32));

Le vamos a cambia el nombre, ya que Access las renombra como Consulta4, Consulta4_1 y Consulta4_2 lo podemos  haces de dos formas en la vista diseño en la parte superior de cada tabla con el ratón derecho le damos un click y otro a propiedades y en alias le cambiamos el nombre.


La otra forma es en la SQL cambiarlo, le ponemos Dias, Meses y Años, y en sql queda:
SELECT
FROM Consulta4 AS Dias, Consulta4 AS Meses, Consulta4 AS Años;

Vamos a agregar los campos necesarios ahora:

SELECT DISTINCT DateSerial([Años].[Num],[Meses].[Num],[Dias].[Num]) AS Fecha, Month(DateSerial([Años].[Num],[Meses].[Num],[Dias].[Num])) AS Mes, Year(DateSerial([Años].[Num],[Meses].[Num],[Dias].[Num])) AS Año, Format$(DateSerial([Años].[Num],[Meses].[Num],[Dias].[Num]),"ww",2) AS Semana, Weekday(DateSerial([Años].[Num],[Meses].[Num],[Dias].[Num]),2) AS DiaSemana
FROM Consulta4 AS Dias, Consulta4 AS Meses, Consulta4 AS Años
WHERE (((Month(DateSerial([Años].[Num],[Meses].[Num],[Dias].[Num])))=[Mes]) AND ((Year(DateSerial([Años].[Num],[Meses].[Num],[Dias].[Num])))=[Año]) AND ((Meses.Num)>=12) AND ((Dias.Num)<=31))
ORDER BY DateSerial([Años].[Num],[Meses].[Num],[Dias].[Num]);
Ahora la hacemos de referencia cruzada, agregando los parámetros del mes y el año:

PARAMETERS Mes Byte, Año Short;
TRANSFORM First(Day([Fecha])) AS Dia
SELECT Consulta7.Semana
FROM Consulta7
GROUP BY Consulta7.Semana
PIVOT Consulta7.DiaSemana;

Y la hacemos toda junta:

PARAMETERS Mes Byte, Año Short;
TRANSFORM First(Day([Fecha])) AS Dia
SELECT Consulta7.Semana
FROM (SELECT DISTINCT DateSerial([Años].[Num],[Meses].[Num],[Dias].[Num]) AS Fecha, Month(DateSerial([Años].[Num],[Meses].[Num],[Dias].[Num])) AS Expr1, Year(DateSerial([Años].[Num],[Meses].[Num],[Dias].[Num])) AS Expr2, Format$(DateSerial([Años].[Num],[Meses].[Num],[Dias].[num]),"ww",2) AS Semana, Weekday(DateSerial([Años].[Num],[Meses].[Num],[Dias].[Num]),2) AS DiaSemana
FROM Consulta4 AS Años, Consulta4 AS Meses, Consulta4 AS Dias
WHERE (((Month(DateSerial([Años].[Num],[Meses].[Num],[Dias].[Num])))=[Mes]) AND ((Year(DateSerial([Años].[Num],[Meses].[Num],[Dias].[Num])))=[Año]) AND ((Meses.Num)>=12) AND ((Dias.Num)<=31))
ORDER BY DateSerial([Años].[Num],[Meses].[Num],[Dias].[Num])) As Consulta7
GROUP BY Consulta7.Semana
PIVOT Consulta7.DiaSemana;

Si no quieren tener limitante en cuanto a los años y quisieran poner cualquier fecha, Chea tiene un ejemplo similar al de Emilio, pero él utiliza además de la tabla números una variable TempVars y en el formulario o informe puede poner cualquier fecha, sin ningún limitante.

Gracias.

BajarArchivo

1 comentario: