Desde que me prejubilé no tocaba este tema. Hace casi 20 años. Mas que para difundir este tipo de trabajos es para que yo mismo los recuerde.
Se trata de manejar con SQL unos datos que tengo en un libro excel. En principio tengo un excel con los movimientos, gastos e ingresos, habidos en una cuenta bancaria en los últimos años. Me interesa, a partir de esos datos en excel, obtener un informe tipo:
| campo1 | aaaa | Enero | Febrero | Marzo | Abril | Mayo | Junio | Julio | Agosto | Septiembre | Octubre | Noviembre | Diciembre | total | Num |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Bizum | 2021 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -62 | 32,95 | -29,05 | 5 |
| Bizum | 2022 | -48 | -65,5 | -17,2 | -1,5 | -26 | 0 | 0 | -42,7 | 0 | 0 | -52 | 10 | -242,9 | 27 |
| Bizum | 2023 | -145 | 0 | 25 | 0 | 5,51 | -399 | 18 | -21,15 | -15 | -24 | 99 | -44 | -500,64 | 18 |
A partir de unos datos, en excel, del tipo:
| Fecha Operación | Fecha Valor | Importe | Concepto |
|---|---|---|---|
| 07/01/2026 | 07/01/2026 | -21,31 | Recibo Naturgy Clientes, S.a.u. Naturgy Clientes, Factura: Fe25390043657535 , Contrato: xxx Nº Recibo xxx Bbjyywx Ref. Mandato xxx, De |
| 05/01/2026 | 05/01/2026 | 250,00 | Transferencia , Concepto Gastos Mensuales Conjunta. |
Primer paso: Vincular la los datos excel a una B.D. Acces
- Bien en una B.D. nueva, creada para este propósito, bien en una B.D. existente vinculamos el excel con la B.D.
- Abrimos la B.D., buscamos "Datos Externos" Excel.
- Buscamos nuestro archivo excel.
- Seleccionamos el último item,"Vincular al origen de datos creando una tabla vinculada"
- La siguiente pantalla nos da a elegir la hoja excel que tiene nuestros datos. Seleccionamos la indicada.
- La tercera pantalla nos permite decir que la primera línea son los nombres de los distintos campos que forman nuestros datos.
- La última pantalla nos pide el nombre que le vamos a dar a la tabla vinculada.
Ya podemos trabajar la hoja excel como si fuera una tabla de acces.
Segundo paso: Adecuar esos datos a nuestras necesidades, de manera que los siguientes pasos sean lo mas cómodos posibles.
- En este caso necesito, por un lado, acortar los nombres de los campos, haciendo desaparecer espacios, acentos y otros caracteres "molestos" a la hora de preparar consultas y, por otro lado, separar la fecha en año, mes y día (sin descartar la fecha como tal)
- Filtrar los datos para eliminar algunos registros que no son propiamente datos.
Primera consulta (SQL): Consulta Paso1
SELECT [fecha operación] AS FOp, year( [fecha operación]) AS AAAA, month( [fecha operación]) AS MM, day( [fecha operación]) AS DD, val([fecha operación] & " ") AS VV, [fecha valor] AS FV, Importe, concepto
FROM Gastos
WHERE Val(([Fecha Operación]) & " ")>0 ORDER BY [fecha operación]
Con este paso, a partir de esta consulta, [fecha operación] pasa a ser FOp, [fecha valor] pasa FV, mucho mas sencillos de manejar en consultas sucesivas. Además obtenemos año, mes y día de la fecha de operación.
Después de varios intentos fallidos (si no hay nada en fecha operación da un error, por lo que hay que añadir un espacio) el filtro de registros no validos lo hago con condición Val(([Fecha Operación]) & " ")>0
Segunda consulta: Necesito incorporar 12 columnas, una por mes, para llevar el importe de cada registro al mes que le corresponde, a la columna de su mes. Para ello utilizo la función iif().
SELECT AAAA, MM, DD, FOp, VV, FV,
iif(MM=1,Importe,0) AS M1, iif(MM=2,Importe,0) AS M2, iif(MM=3,Importe,0) AS M3, iif(MM=4,Importe,0) AS M4, iif(MM=5,Importe,0) AS M5, iif(MM=6,Importe,0) AS M6, iif(MM=7,Importe,0) AS M7, iif(MM=8,Importe,0) AS M8, iif(MM=9,Importe,0) AS M9, iif(MM=10,Importe,0) AS M10, iif(MM=11,Importe,0) AS M11, iif(MM=12,Importe,0) AS M12,
1 AS N, *
FROM paso1
Incorporo (N) un contador, por si fuese necesario. De momento no lo utilizo.
Estas dos consultas SQL se pueden hacer en un solo paso, se pueden refundir en una sola, pero, como es lógico, queda una consulta mucho mas compleja. A veces no es necesario complicarse la vida. Es una consulta concatenada con otra en SQL:
SELECT AAAA, MM, DD, FOp, VV, FV, iif(MM=1,Importe,0) AS M1, iif(MM=2,Importe,0) AS M2, iif(MM=3,Importe,0) AS M3, iif(MM=4,Importe,0) AS M4, iif(MM=5,Importe,0) AS M5, iif(MM=6,Importe,0) AS M6, iif(MM=7,Importe,0) AS M7, iif(MM=8,Importe,0) AS M8, iif(MM=9,Importe,0) AS M9, iif(MM=10,Importe,0) AS M10, iif(MM=11,Importe,0) AS M11, iif(MM=12,Importe,0) AS M12, 1 AS N, *
FROM
(SELECT [fecha operación] AS FOp, year( [fecha operación]) AS AAAA, month( [fecha operación]) AS MM, day( [fecha operación]) AS DD, val([fecha operación] & " ") AS VV, [fecha valor] AS FV, Importe, concepto FROM Gastos WHERE Val(([Fecha Operación]) & " ")>0 ORDER BY [fecha operación])
Tercera consulta: Ahora nos interesa encontrar dentro del campo "concepto" una serie de literales que nos faciliten identificar el movimiento bancario.
- Creamos una tabla con los literales buscados, y su significado. La dejo abierta por si hay que modificar la búsqueda de esos literales dentro de concepto.
- Campo1 es el literal buscado, campo2 es el literal que va a aparecer en el informe y campo3 de momento no lo utilizo para nada, es por si fuera necesario modificar la búsqueda.
| Id | Campo1 | Campo2 | Campo3 |
|---|---|---|---|
| 9 | Lit1 | SPG | |
| 8 | 958 | PR15 | |
| 20 | 013 | Luz Madrid | |
| 10 | 117 | Luz CM | |
| 19 | 2005 | Gas 23 | |
| 13 | 30973 | Gas |
SELECT Campo1, Campo2, Paso2.*, instr(Concepto,campo1) AS P
FROM Tabla1, Paso2 where instr(Concepto,campo1)>0
Esta sentencia SQL multiplica matricialmente todos los registro de la tabla1 por los registros resultantes del paso2. Con instr(Concepto,campo1)>0 se eliminan todos los registros que no contienen el valor del correspondiente literal (campo1) en el concepto.
Paso4: en este paso, acumulamos los distintos importes de cada mes en la columna correspondiente, por año y concepto. Con cuatro consultas SQL ya tendríamos nuestro informe de gasto acumulado por mes.
SELECT campo1, aaaa, Sum(m1) AS Enero, Sum(m2) AS Febrero, Sum(m3) AS Marzo, Sum(m4) AS Abril, Sum(m5) AS Mayo, Sum(m6) AS Junio, Sum(m7) AS Julio, Sum(m8) AS Agosto, Sum(m9) AS Septiembre, Sum(m10) AS Octubre, Sum(m11) AS Noviembre, Sum(m12) AS Diciembre, Sum(Importe) AS total, Sum(n) AS Num
FROM paso3
GROUP BY campo1,AAAA
ORDER BY aaaa, campo1;
| campo1 | campo2 | aaaa | Enero | Febrero | Marzo | Abril | Mayo | Junio | Julio | Agosto | Septiembre | Octubre | Noviembre | Diciembre | total | Num |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| diputacion | Diputación | 2013 | 0 | 0 | 0 | 0 | 0 | -71,94 | 0 | 0 | 0 | -207,66 | 0 | 0 | -279,6 | 2 |
| Gas Natural | 2013 | -31,21 | 0 | -47,23 | -67,04 | 0 | -56,02 | 0 | 10,23 | 0 | 0 | -81,01 | -71,1 | -343,38 | 9 | |
| impuesto | Impuestos | 2013 | 0 | 0 | 0 | 0 | 0 | -71,94 | 0 | 0 | 0 | -207,66 | 0 | 0 | -279,6 | 2 |
| liquidacion | 2013 | 0 | 0 | 0 | 1,28 | 0 | 0 | 0 | 0 | 0 | 3,44 | 0 | 0 | 4,72 | 2 | |
| San Pedro | 2013 | 0 | -30 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -30 | 2 |
En este punto ¿Hemos terminado? No, no hemos terminado. Este trabajo es un trabajo preliminar, un intento de recordar como se trabaja con SQL y un intento de no dejar nada en el olvido.
Nos falta un registro con el total del año y, posiblemente, un separador de años. Tampoco he separado los valores positivos de los negativos, gastos e ingresos, cada uno por su sitio. Como ya he dicho, trabajo preliminar.
Paso5: Vamos totalizar el año, es una consulta similar a la anterior pero agrupando solamente por AAAA. Los literales Campo1 y Campo2 son fijos, AAAA es el que corresponda y el resto de los campos es una suma.
SELECT "Total año" AS Campo1, "Total año" AS campo2, AAAA, Sum(m1) AS Enero, Sum(m2) AS Febrero, Sum(m3) AS Marzo, Sum(m4) AS Abril, Sum(m5) AS Mayo, Sum(m6) AS Junio, Sum(m7) AS Julio, Sum(m8) AS Agosto, Sum(m9) AS Septiembre, Sum(m10) AS Octubre, Sum(m11) AS Noviembre, Sum(m12) AS Diciembre, Sum(Importe) AS total, Sum(n) AS Num
FROM paso3
GROUP BY AAAA
ORDER BY AAAA;
Paso6: Para terminar una consulta mas compleja, es la unión de varias consultas sencillas.
SELECT AAAA,Campo1,Campo2,Enero,Febrero,Marzo,Abril,Mayo,Junio,Julio,Agosto,Septiembre,Octubre,Noviembre,Diciembre,Total
FROM
(SELECT * , 2 as Ord FROM Paso4
union
select *,4 as Ord from Paso5
union
select "","","",aaaa,"","","","","","","","","","","","","", 0 as Ord from paso5 group by aaaa)
ORDER BY AAAA, Ord;
En esta unión tenemos tres consultas sencillas, dos de ellas Paso4 y Paso5 ya comentadas, la tercera:
select "","","",aaaa,"","","","","","","","","","","","","", 0 as Ord from paso5 group by aaaa
Es la que nos va a dar el separador de años. El único campo no nulo es el año, lo que nos va a dar una cabecera con ese año.
Introduzco un nuevo campo, Ord, que me va a dar la posición relativa de los distintos registros de la unión. Cero para la cabecera del año, dos para los registros del año y 4 para el total del año.
| AAAA | Campo1 | Campo2 | Enero | Febrero | Marzo | Abril | Mayo | Junio | Julio | Agosto | Septiembre | Octubre | Noviembre | Diciembre | Total |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2013 | |||||||||||||||
| 2013 | 17916330 | SPG | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -36,96 | 0 | 0 | -81,01 | -71,1 | -189,07 |
| 2013 | San Pedro | 0 | -30 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -30 | |
| 2013 | diputacion | Diputación Segovia | 0 | 0 | 0 | 0 | 0 | -71,94 | 0 | 0 | 0 | -207,66 | 0 | 0 | -279,6 |
| 2013 | impuesto | Impuestos | 0 | 0 | 0 | 0 | 0 | -71,94 | 0 | 0 | 0 | -207,66 | 0 | 0 | -279,6 |
| 2013 | Gas Natural | -31,21 | 0 | -47,23 | -67,04 | 0 | -56,02 | 0 | 10,23 | 0 | 0 | -81,01 | -71,1 | -343,38 | |
| 2013 | liquidacion | 0 | 0 | 0 | 1,28 | 0 | 0 | 0 | 0 | 0 | 3,44 | 0 | 0 | 4,72 | |
| 2013 | Total año | Total año | -31,21 | -30 | -47,23 | -65,76 | 0 | -199,9 | 0 | -26,73 | 0 | -411,88 | -162,02 | -142,2 | -1116,93 |
Puede que nos interesen solamente los informes de un determinado año, o de un litera, o ....
SQL con selección de año: Añadimos a la consulta anterior (Paso6)
la petición del año que queremos consultar:
SELECT Pregunta,AAAA, Campo1, Campo2, Enero, Febrero, Marzo, Abril, Mayo, Junio, Julio, Agosto, Septiembre, Octubre, Noviembre, Diciembre, Total
FROM (SELECT * , 2 as Ord FROM Paso4
union
select *,4 as Ord from Paso5
union
select "","","",aaaa,"","","","","","","","","","","","","", 0 as Ord from paso5 group by aaaa) AS [%$##@_Alias]
where AAAA=Pregunta
ORDER BY AAAA, Ord;
En donde insertamos una primera variable (Pregunta) y seleccionamos con where AAAA=Pregunta.
