martes, 20 de marzo de 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) 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.





sábado, 17 de febrero de 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.

miércoles, 7 de mayo de 2014

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.

lunes, 27 de mayo de 2013

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ñíaNombre del contacto
HILARIÓN-AbastosCarlos Hernández

Ramon Poch. Terrassa a 27/05/2013

lunes, 10 de mayo de 2010

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.

miércoles, 4 de noviembre de 2009

EMULANDO AUTONUMERICOS


Un tema recurrente : generar una secuencia numérica correlativa, sin discontinuidades y a prueba de errores, para asignar valores a nuevos registros, al estilo de los autonuméricos, pero sin los inconvenientes de estos.
Ciertamente hay variedad de recetas para abordar este problema, pero la piedra de toque de todos ellos es su empleo en sistemas con elevada concurrencia de usuarios y que dicho sistema sea también efectivo dentro de una instrucción sql (y que permita por tanto un insert masivo). Personalmente siempre he usado transacciones (ADO) para asegurar que la lectura del posible nuevo valor, quede bloqueado (para nuevas lecturas) hasta que finalice el proceso de adjudicación del mismo. Esta vez propongo utilizar efectivamente transacciones, pero de forma implícita dentro de una instrucción sql. Esto, en un SGDB como Sql Server es bien simple, pues todas las instrucciones contenidas en un procedimiento almacenado, utilizan la misma transacción, de tal forma que un error de ejecución de una de ellas, trunca todo el procedimiento. Pero en Access, puesto que su lenguaje sql no admite lotes de instrucciones (solo podemos ejecutar/llamar comandos de uno en uno), esto no es posible, a no ser que seamos capaces en una sola sentencia de obtener un nuevo valor, actualizar la tabla y recuperar el valor escrito (hay que tener en cuenta que, una de las características de los autonuméricos es devolver el nuevo valor insertado).
Pongamos un ejemplo : Generar un nuevo número de factura. Para ello disponemos de un formulario para introducir la información de la cabecera del documento : Fecha, Cliente, Tarifa, etc, y un subformulario para los detalles de la misma. Una solución simple y clásica es obtener el mayor valor concedido al campo numerador mediante un Select Max() e incrementar en uno dicho dato ... Pero este sistema, desarrolla toda su lógica en el lado cliente y presenta inconvenientes evidentes en un entorno multiusuario. La solución que propongo consiste en tener dos tablas auxiliares, una en el 'lado datos' y otra en el 'lado cliente'. El objetivo es almacenar el último valor generado absoluto, y el relativo en cada máquina cliente. Ambas tablas deben estar accesibles en la base de datos donde ejecutemos el código : una como tabla local, y la otra (por ejemplo) como tabla vinculada.
Recapitulemos : Tenemos en una base de datos (de servidor) una tabla Facturas, y una tabla Numeradores que contiene el último número de factura generado. Cuando necesitemos generar un nuevo documento solo tenemos que ejecutar esta instrucción sql :

Update Numeradores

Left Join
(Select * From Facturas Where 1=0) As T
On Numeradores.Numerador = T.NFactura
Set Numeradores.Numerador = Numeradores.Numerador + 1,
Facturas.NFactura = Numeradores.Numerador,
Facturas.Fecha = #12/31/2009#,
Facturas.Cliente = 100,
Facturas.Tarifa = 1;

Condiciones previas : Numeradores debe contener un solo registro con valor 1 (valor inicial del autonumérico).
Dado que hemos relacionado de forma asimétrica ambas tablas, aún cuando la segunda tabla (en este caso una Select sobre Facturas) no devuelve resultados, puesto que hemos añadido la condición 1=0, la instrucción update (a efectos prácticos no es un update, sino un insert) afectará a tantos registros como haya en la tabla numeradores, es decir : uno. Al ejecutarse la sentencia, el valor del numerador se incrementa en 1 (no olvidemos que se trata de una instrucción update), en segundo lugar, el nuevo numerador generado se asigna al campo Nfactura de la tabla Facturas, pero dado que no hay ningún registro presente (en la consulta) de la tabla Facturas (hemos filtrado los resultados con esta condición imposible : 1=0), en realidad estamos insertando un nuevo registro en la tabla, y finalmente se actualizan el resto de los campos de Facturas con los valores incluidos en la instrucción sql.

Vamos a hablar de la tabla local de numeradores a la que previamente haciamos referencia. Supongamos que necesitamos agregar registros de uno en uno desde un formulario, y que vamos a introducir desde el propio formulario los datos en cada uno de los campos de la tabla Facturas : Fecha, Cliente, Tarifa, etc. Así pues, necesitamos generar un nuevo registro en dicha tabla, y recuperar automáticamente dicho registro para poder trabajar con él. ¿ En un escenario multiusuario con acceso concurrente a la tabla Facturas, si insertamos una nueva línea, como podemos estar seguros de recuperar el registro que nosotros hemos generado y no otro ? … cabe pensar en la posibilidad que escasos instantes despues de insertar el registro y antes de recuperarlo, otro usuario ha añadido otra línea en la tabla … Si utilizamos Max(NFactura) o leemos el valor de la tabla Numeradores, no nos sirve, puesto que el inoportuno usuario ha modificado también ambos campos …

La panacea a este dilema es incorporar una tabla de Numeradores local (o cliente) además de la tabla Numeradores del servidor que hasta ahora hemos ido usando en nuestra instrucción sql. Se trata de guardar en la tabla local el último Numerador asignado, y puesto que dicha tabla solo se modifica desde la aplicación cliente donde corre el código, tenemos la certeza de que el valor guardado en ella corresponde al último registro asignado por nosotros :

Update (Select Numeradores.Numerador As NumeradorServer,
Numeradores_locales.Numerador As NumeradorLocal
From Numeradores
Inner Join
Numeradores_locales
On Numeradores.Numerador >= Numeradores_locales.Numerador) As N
Left Join
(Select * From Facturas Where 1=0) As T
On N.NumeradorServer = T.NFactura
Set N.NumeradorServer = N.NumeradorServer + 1,
N.NumeradorLocal = N.NumeradorServer,
Facturas.NFactura = N.NumeradorServer,
Facturas.Fecha = #12/31/2009#,
Facturas.Cliente = 100,

Facturas.Tarifa = 1;

Para recuperar el último valor insertado (por nosotros) en la tabla Facturas :

Select Facturas.
*
From Facturas
Inner Join Numeradores_locales
On Facturas.NFactura = Numeradores_locales.Numerador

Pero … ¿ Solo puedo añadir registros de uno en uno ? …

No. Supongamos que tenemos que insertar un conjunto de registros en la tabla Facturas, estos registros los obtiene una consulta llamada Nuevas_Facturas. Dicha consulta debe tener los campos que nos interesa grabar en Facturas, y además, un campo para relacionarlo con Facturas.Nfactura y Numeradores.Numerador. El valor de dicho campo ha de ser tal, que sea imposible que coincida en cualquiera de ambas tablas. Si hemos empezado la numeración de documentos a partir de 1, un valor aceptable para este propósito seria -1.
Y esta es la sql a ejecutar :

Update (Numeradores
Left Join
Nuevas_facturas
On Numeradores.Numerador <> Nuevas_facturas.NFactura)
Left Join
Facturas On Facturas.NFactura = Nuevas_facturas.NFactura
Set Numeradores.Numerador = Numeradores.Numerador + 1,
Facturas.NFactura = Numeradores.Numerador,
Facturas.Fecha = Nuevas_facturas.Fecha,
Facturas.Cliente = Nuevas_facturas.Cliente,
Facturas.Tarifa = Nuevas_facturas.Tarifa;

… con ello añadiremos tantos registros en la tabla Facturas como nos devuelva la consulta Nuevas_Facturas.
En líneas anteriores comentaba la necesidad de que si alguna de las modificaciones en tabla, de alguno de los registros, fallara, se truncará todo el proceso. Podemos comprobar (manipulando la tabla Numeradores para producir duplicados en el campo clave Nfactura), que caso de haber un error, todo el proceso queda abortado, permaneciendo intactas ambas tablas.


Ramon Poch. Terrassa a 04/11/2009.

domingo, 1 de marzo de 2009

SERIES NUMÉRICAS (SIN TABLA NUMS)


El 23 de Abril de 2008, publiqué este artículo : http://sqlraipon.blogspot.com/2008/04/series-numricas.html, donde se explica como obtener, una consulta con una secuencia de números continua (de 1 a 100, de 1 a 1000, etc) a partir de una tabla ('Nums') con solo 10 registros. Dichas consultas resultan de gran utilidad y las he empleado en muchos de los ejemplos de este blog.


Pues bien, es posible obtener los mismos resultados sin necesidad de la tabla 'Nums' :

-- Para bases de datos con formato Access 2000 :-- En una base de datos nueva, en la que todavia no se haya creado
-- ningún objeto, la tabla del sistema MSysAccessObjects contiene
-- 4 registros, con valores de 0 a 3 en el campo Id.
-- Es de resaltar, que en esta tabla jamás se elimina ningún registro,
-- cuando menos, la serie de valores del campo Id nunca presenta
-- discontinuidades.
-- Por tanto, esta consulta de unión nos asegura una serie numérica
-- de 10 líneas con valores entre 0 y 9, sea cual sea el estado de la
-- base de datos (nueva o con múltiples objetos).

Select Id As Num From MSysAccessObjects Where Id <= 3
Union All
Select Id + 4 As Num From MSysAccessObjects Where Id <=3
Union All
Select Id + 8 As Num From MSysAccessObjects Where Id < 2;

-- Para bases de datos en formato 2003 o 2007. Hay que tener en cuenta que la
-- tabla MSysAccessObjects no existe, por lo tanto emplearemos otra tabla del
-- sistema (MSysObjects). Es de destacar que en una base de datos nueva, están
-- siempre presentes cuatro registros (entre otros) con los siguientes Id :
-- 2, 3, 4 y 5. Por lo tanto, esta consulta de unión nos asegura igualmente 10
-- registros, con valores de 0 a 9 :

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;

A partir de aquí, solo tenemos que guardar una de las dos consultas con el nombre Nums, y ya podemos emplear las consultas del enlace arriba citado sin necesidad de tener en la base de datos la tabla Nums.

Ramon Poch. Terrassa a 10/01/2009.
 

Contactar