Ejemplos de lenguaje sql para Access

NUEVA VERSION EDITOR SQL

2010-11-28T10:21:00.000-08:00

El título lo dice todo, hoy publico una nueva versión : Editor Sql 1.4

La principal novedad es que se ha mejorado la compatibilidad con las nuevas versiones de Access. Se puede utilizar con Access 2000, 2002, 2003, 2007 y 2010.

Espero que os sea útil.

Ramon Poch, raipon. Terrassa a 28/11/2010.

JUGANDO CON LAS MATES (NÚMEROS PRIMOS)

2010-05-10T07:11:00.000-07:00

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, 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, 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, raipon. Terrassa a 10/05/2010.

EMULANDO AUTONUMERICOS

2009-11-04T09:04:00.000-08:00

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, raipon. Terrassa a 04/11/2009.

REVISION DE FORMULARIOS DESCONECTADOS

2009-07-09T01:06:00.001-07:00

Hola, he revisado y actualizado el código de Formularios desconectados.

SERIES NUMÉRICAS (SIN TABLA NUMS)

2009-03-01T01:00:00.000-08:00

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, raipon. Terrassa a 10/01/2009.

FORMULARIOS DESCONECTADOS

2009-02-25T04:56:00.000-08:00

Hoy no hablaré directamente de sql, si no de la posibilidad de trabajar en Access, con formularios desconectados de su orígen, al estilo de visual basic.
No es la primera vez que abordo este tema, pero siempre tropezamos con la misma piedra : la complejidad del código necesario. Pue bién, esta vez, y gracias a la clase 'cls_desc', este será todo el código necesario en un formulario (en este caso 'Proveedores' de 'Neptuno.mdb') :

Option Explicit
Option Compare Database
Private cl As New cls_desc

Private Sub cmdGrabar_Click()
On Error Resume Next

If Me.Dirty Then
Me.Refresh
End If

If cl.Dirty = True Then
cl.Actualizar CurrentProject.Connection
MsgBox "Se han realizado : " & cl.RecordsAffected & " actualizaciones."
MsgBox "Se han producido los siguientes errores : " & vbCrLf & cl.Errores
End If

End Sub

Private Sub Form_Open(Cancel As Integer)
Dim rst As New ADODB.Recordset

'Configuramos el recordset
rst.CursorLocation = adUseClient
rst.ActiveConnection = CurrentProject.Connection
rst.LockType = adLockBatchOptimistic
rst.CursorType = adOpenKeyset
rst.Source = "Proveedores"
'Abrimos el recordset
rst.Open
'Desvinculamos el recordset de su orígen
rst.ActiveConnection = Nothing
'Asignamos rst como recordset del formulario
Set Me.Recordset = rst
'Configuramos la clase
Set cl.ActiveRecordset = rst
cl.Campo_Clave = "IdProveedor"
cl.Unique_Table = "Proveedores"

End Sub

... ¿ no es demasiado complicado verdad ?. Y sin embargo podrás editar registros, añadir nuevos, eliminar, etc. y los cambios no se reflejarán en la base de datos a no ser que llamemos al método Actualizar de la clase. Puedes ver el código del módulo de clase 'cls_desc', así como un par de formularios de ejemplo en : Formularios desconectados.zip .
Por si lo anterior te ha parecido insuficiente : Esta clase, permite trabajar con un recordset obtenido de una consulta no actualizable (excepto consultas de unión). El recordset, al estar desconectado permite editar los registros, y al llamar al método Actualizar, los cambios serán volcados en la tabla definida por la propiedad Unique_Table.
Solo debes tener en cuenta que : este tipo de formularios no toleran bién los filtros y ordenar. Por lo demás han sido probados en las siguientes versiones de Access : XP, 2003 y 2007

Ramon Poch, raipon. Terrassa a 24/02/2009.

DISTINGUIR MAYÚSCULAS Y MINÚSCULAS

2009-02-15T00:01:00.000-08:00

A raiz de una pregunta en los grupos de discusión : microsoft.public.es.access : ¿ como conseguir que un campo de texto sea sensible al uso de mayúsculas y minúsculas ?, se me ocurrió la siguiente solución :

Utilizar un campo tipo binary, ya que en este contexto Access guarda los datos como bytes y no como texto. Esto no lo podemos conseguir desde la vista diseño de una tabla, pues este tipo de campo no está disponible, lo más parecido es un campo 'Objeto Ole', o lo que es lo mismo 'Long Binary', pero para este propósito no nos sirve. Para ello utilizaremos una instrucción ddl (Create Table, o Alter Table), bién desde una consulta (teniendo activada, en 'Opciones', la compatibilidad con Sql Server) o mediante el método Execute de Ado.

Ejemplo :

Create Table MiTabla (Campo1 Binary);

Insert Into MiTabla Values('A');
Insert Into MiTabla Values('a');
Insert Into MiTabla Values('B');
Insert Into MiTabla Values('b');
Insert Into MiTabla Values('C');
Insert Into MiTabla Values('c');
Insert Into MiTabla Values('D');
Insert Into MiTabla Values('d');
Insert Into MiTabla Values('E');
Insert Into MiTabla Values('e');

Select *, Asc(Campo1) As Char_code From MiTabla Order By Campo1;

Drop Table MiTabla;

Al abrir la consulta de selección, vemos como efectivamente el órden establecido corresponde al código del caracter (a pesar de ordenar por 'Campo1'), y no al valor como texto. Por tanto, Access realiza implícitamente la conversión de texto a bytes cuando guardamos datos en este campo, y al reves (de bytes a texto) cuando nos muestra el contenido del mismo.

Así mismo, esta técnica, nos permite tener un campo 'de texto', indexado, de valores únicos, que distinga entre mayúsculas y minúsculas. Ejemplo para crear un campo binary indexado sin duplicados :

Create Table MiTabla (Campo1 Binary Unique);

o si queremos que sea el campo clave :

Create Table MiTabla (Campo1 Binary Primary Key);

Ramon Poch, raipon. Terrassa a 31/01/2009.

FIRST Y LAST

2009-02-01T00:01:00.000-08:00

Si utilizamos estas funciones en una instrucción sql que apunte directamente sobre una tabla :

Create Table MiTabla (Campo1 Text (100));

Insert Into MiTabla Values ('Hola mundo');
Insert Into MiTabla Values ('Prueba first y last');
Insert Into MiTabla Values ('Fin de la prueba');

Select First(Campo1) As Primero, Last(Campo1) As Último
From MiTabla;

Drop Table MiTabla;

... vemos que los resultados son absolutamente coherentes : first devuelve el valor para dicho campo del primer registro insertado en la tabla, y viceversa ...
Sin embargo, estos resultados no se ven afectados si establecemos un orden concreto desde la vista 'hoja de datos' de la tabla. Es decir, si desde dicha vista establecemos un orden ascendente (alfabético) para campo1, y guardamos los cambios, First sigue devolviendo 'Hola mundo' en vez de 'Fin de prueba'. ¿ Significa esto que no podemos definir otro criterio de ordenación para establecer qué valor es primero o último ?. En la ayuda de Access podemos leer :

"... Devuelven simplemente el valor de un campo especificado en el primer o el último registro, respectivamente, del conjunto de resultados devueltos por una consulta. Debido a que los registros se devuelven normalmente sin un orden determinado (a no ser que la consulta incluya una cláusula ORDER BY), los registros devueltos por estas funciones son arbitrarios."

... vamos a probar :

Select First(Campo1) As Primero, Last(Campo1) As Último
From
(
Select Campo1
From MiTabla
Order By Campo1
);

... pues no, no es cierto, o no quiere decir lo que parece; nada ha cambiado. Y da igual que guardemos la consulta embebida dentro de la cláusula From como una nueva consulta, que establezcamos también el orden desde la vista 'hoja de datos' de esta nueva consulta, ... nada, ¡ es inútil !. Sencillamente : el orden establecido en una instrucción 'Select', no es persistente para posteriores llamadas a la misma.
Si pensamos en ello detenidamente, este comportamiento es del todo lógico, ya que el orden de los resultados solo debe ser relevante en cuanto a una mejor interpretación de los mismos, pero no para la validez de los datos devueltos. Y en cambio, ordenar los registros para cada 'Select' en una serie de llamadas entre consultas, penalizaría sin duda el rendimiento. Sin embargo hay un escenario en donde los registros devueltos pueden ser distintos según el criterio establecido en 'Order By' : ... cuando usamos la cláusula Top para restringir los resultados a un determinado número de registros. ¿ Solucionará también la cláusula Top nuestro problema ?. Efectivamente, si utilizamos dicha cláusula, el orden permanece para una posterior llamada a la consulta :

Select First(Campo1) As Primero, Last(Campo1) As Último
From
(
Select Top 100 Percent Campo1
From MiTabla
Order By Campo1
);

... y ahora sí, el valor devuelto por First es 'Fin de la prueba' y Last : 'Prueba first y last'. Valores coherentes si ordenamos alfabéticamente 'Campo1'. En conclusión : siempre que utilicemos dichas funciones, si queremos resultados consistentes y fiables debemos basar la sql sobre una consulta previa que incorpore la cláusula 'Top' (a no ser que deseemos recuperar valores directamente de la tabla según el orden de inserción).

Ramon Poch, raipon. Terrassa a 20/12/2008.

DESAFIO SQL (ORDER BY). LA SOLUCIÓN

2009-01-18T00:01:00.000-08:00

En primer lugar, un comentario sobre las 'herramientas' a utilizar.

Para descomponer la cadena en segmentos : http://sqlraipon.blogspot.com/2008/04/descomponer-lista-csv-en-registros.html Se trata de la adaptación a Access de un ejemplo del mismo Itzik Ben-Gan.
Dentro del ejemplo anterior, es necesario generar una secuencia continua de números : http://sqlraipon.blogspot.com/2008/04/series-numricas.html.
Las subconsultas, son para Access un 'cuello de botella', colapsando con facilidad la ejecución de una consulta, si hay que procesar una cantidad considerable de registros. Una alternativa válida en determinados casos, consiste en incorporar, la tabla orígen de la subconsulta a la clausula From de la consulta principal, relacionando ambas tablas con un left join, mas la misma expresión utilizada en la cláusula Where de la subconsulta, para filtrar los resultados de la subconsulta a los registros relacionados de la consulta principal. Finalmente, para obtener el valor que nos devolvía la subconsulta, utilizamos una función de agregado.
Ejemplo sobre la base de datos Neptuno.mdb (tabla Pedidos). Se trata de numerar los pedidos de cada cliente, según la fecha del pedido :

Select
(Select Count(*) From Pedidos As T
Where T.IdCliente = Pedidos.IdCliente
And
T.FechaPedido < Pedidos.FechaPedido) + 1 As NºPedido_Cliente,
IdPedido, IdCliente, FechaPedido
From Pedidos;

Select Pedidos.IdPedido, Pedidos.IdCliente, Pedidos.FechaPedido,
Count(T.FechaPedido) + 1 As NºPedido_Cliente
From Pedidos Left Join Pedidos As T
On T.IdCliente = Pedidos.IdCliente And T.FechaPedido < Pedidos.FechaPedido
Group By Pedidos.IdPedido, Pedidos.IdCliente, Pedidos.FechaPedido;

Ambas consultas devuelven los mismos resultados, pero la segunda es 10 veces más rápida que la primera.
Esta técnica de substitución de subconsultas la he empleado, dentro de la solución al reto, en 'Qry2' y la consulta final.
Un conjunto de valores tal, que el resultado de todas las sumas posibles sea un valor único, irrepetible, y que la suma de los elementos menores de la lista, no pueda jamás alcanzar un elemento superior. Ejemplo : una lista de potencias 2, 4, 8, 16, 32, 64, etc o 3, 9, 27, 81, 243, etc.

Y esta es la solución :

CREATE TABLE t1 (id identity primary key, f_val Memo not null);

insert into t1 (f_val) values ('100');
insert into t1 (f_val) values ('7.4.250');
insert into t1 (f_val) values ('22.40.5.60.4.100.300.478.19710212');
insert into t1 (f_val) values ('22.40.5.60.4.99.300.478.19710212');
insert into t1 (f_val) values ('22.40.5.60.4.99.300.478.9999999');
insert into t1 (f_val) values ('10.30.40.50.20.30.40');
insert into t1 (f_val) values ('7.4.250');
insert into t1 (f_val) values ('-1');
insert into t1 (f_val) values ('-2');
insert into t1 (f_val) values ('-11');
insert into t1 (f_val) values ('-22' );
insert into t1 (f_val) values ('-123');
insert into t1 (f_val) values ('-321');
insert into t1 (f_val) values ('22.40.5.60.4.-100.300.478.19710212');
insert into t1 (f_val) values ('22.40.5.60.4.-99.300.478.19710212');

Create Table Nums (Num Long Primary Key);

Insert Into Nums Values (0);
Insert Into Nums Values (1);
Insert Into Nums Values (2);
Insert Into Nums Values (3);
Insert Into Nums Values (4);
Insert Into Nums Values (5);
Insert Into Nums Values (6);
Insert Into Nums Values (7);
Insert Into Nums Values (8);
Insert Into Nums Values (9);

-- Descomponemos la cadena f_val en tantos registros como segmentos
-- (definidos por el caracter '.' como separador) tenga.
CREATE PROCEDURE Qry1 AS
SELECT id,
Num,
CLng(Mid(f_val,Num+1,Abs(InStr(Num+1,f_val & ".",".")-Num-1))) As Valor,
f_val
FROM
(
Select (TNums_1.Num*10)+ Nums.Num As Num
From Nums, Nums As TNums_1
) AS TNums_2, t1
WHERE Num<=Len(f_val)
And
(Mid(f_val,IIf(Num=0,1,Num),1) = "." Or Num=0);

-- Añadimos un campo 'Nivel' que nos indica el número de segmento
-- dentro de la cadena
CREATE PROCEDURE Qry2 AS
Select Qry1.Id, Qry1.Valor, Qry1.f_val, Count(T.Num) + 1 As Nivel
From Qry1 Left Join Qry1 As T
On Qry1.Id = T.Id And Qry1.Num > T.Num
Group By Qry1.Id, Qry1.Valor, Qry1.f_val, Qry1.Num;

-- El campo Orden se muestra solo a efectos didácticos
Select Id, f_val, Sum(Orden2) As Orden
From
(
-- Calculamos la siguiente potencia :
-- El valor máximo de nivel (obtenido mediante una subconsulta)
-- lo elevamos al Nivel (en negativo).
-- Y dicha expresión la multiplicamos por Orden. La idea es obtener
-- una cifra tal, que la suma de todos los valores posibles para
-- 'niveles' más pequeños de la misma cadena, no la pueda superar.
Select Id, f_val, Orden * ((Select Max(Nivel) From Qry2) ^ (Nivel * -1)) As Orden2
From
(
-- Obtenemos para cada nivel, el número de registros más pequeños
-- según valor.
Select Qry2.Id, Qry2.Nivel, Qry2.f_val, Count(T.Id) As Orden
From Qry2 Left Join Qry2 As T
On Qry2.Nivel = T.Nivel And Qry2.Valor > T.Valor
Group By Qry2.Id, Qry2.Nivel, Qry2.f_val
)
)
Group By Id, f_val
Order By Sum(Orden2);

-- Eliminamos los objetos :
Drop Procedure Qry2;
Drop Procedure Qry1;
Drop Table t1;
Drop Table Nums;

Ramon Poch, raipon. Terrassa a 03/01/2009.

Actualización Editor Sql 1.3

2009-01-10T00:24:00.000-08:00

Hola, ayer subí de nuevo al servidor, el archivo Editor Sql 1.3.rar, con algunas correcciones. Si instalaste la versión 1.3 antes de 09/01/2009, puedes descargar de nuevo el archivo y ejecutar el Setup.

Ramon Poch, raipon. Terrassa a 10/01/2009.

DESAFIO SQL (ORDER BY)

2009-01-03T00:01:00.000-08:00

El 1 de Septiembre de 2008, Itzik Ben-Gan (MVP Sql Server) publicó en su blog el siguiente desafio http://www.sqlmag.com/Article/ArticleID/100156/sql_server_100156.html, que adaptado al lenguaje sql de Access (a utilizar desde un 'lote sql' del Editor Sql 1.3) queda así :

CREATE TABLE t1 (id identity primary key, f_val Memo not null);
insert into t1 (f_val) values ('100');
insert into t1 (f_val) values ('7.4.250');
insert into t1 (f_val) values ('22.40.5.60.4.100.300.478.19710212');
insert into t1 (f_val) values ('22.40.5.60.4.99.300.478.19710212');
insert into t1 (f_val) values ('22.40.5.60.4.99.300.478.9999999');
insert into t1 (f_val) values ('10.30.40.50.20.30.40');
insert into t1 (f_val) values ('7.4.250');

/*Aquí está la salida deseada:

id******f_val
----------------------------------------
7*******7.4.250
2*******7.4.250
6*******10.30.40.50.20.30.40
5*******22.40.5.60.4.99.300.478.9999999
4*******22.40.5.60.4.99.300.478.19710212
3*******22.40.5.60.4.100.300.478.19710212
1*******100

Puntos extra si su solución también apoyará negativos.
Añádanse los siguientes datos de ejemplo para poner a prueba los negativos: */

insert into t1 (f_val) values ('-1');
insert into t1 (f_val) values ('-2');
insert into t1 (f_val) values ('-11');
insert into t1 (f_val) values ( '-22' );
insert into t1 (f_val) values ('-123');
insert into t1 (f_val) values ('-321');
insert into t1 (f_val) values ('22.40.5.60.4.-100.300.478.19710212');
insert into t1 (f_val) values ('22.40.5.60.4.-99.300.478.19710212');

/*Incluida la salida deseada, los valores negativos:

id******f_val
----------------------------------------
13******-321
12******-123
11******-22
10******-11
9*******-2
8*******-1
7*******7.4.250
2*******7.4.250
6*******10.30.40.50.20.30.40
14******22.40.5.60.4.-100.300.478.19710212
15******22.40.5.60.4.-99.300.478.19710212
5*******22.40.5.60.4.99.300.478.9999999
4*******22.40.5.60.4.99.300.478.19710212
3*******22.40.5.60.4.100.300.478.19710212
1*******100

Solución (Ramon Poch, raipon, Terrassa a 03/01/2009) :
La publicaré, Dios mediante, dentro de 15 dias : el 18/01/2009.

*/

... pues eso, el 18/01/2009 publicaré mi solución a este problema. Sin embargo les animo a que me envien por correo (mirar en la parte de abajo de esta página) sus propias conclusiones, y las publicaré.
Cabe decir que si utilizamos una función creada desde vba en nuestra consulta, la solución puede ser más sencilla. Pero dado que se puede lograr exclusivamente mediante sql y las funciones vba disponibles ordinariamente en el entorno sql, creo preferible ceñirse a este contexto. En el fondo esto no es un trabajo profesional, tan solo un reto.
Naturalmente a estas alturas, en el blog de Itzik ya están colgadas varias soluciones a este reto, adaptadas a sql server. La solución mia la he diseñado antes de mirar dichas propuestas. No tendria sentido plantearlo de otra forma, ¿ cierto ?.

COMPARATIVA DE RENDIMIENTO

2008-12-22T00:12:00.000-08:00

Desde la semana pasada está disponible la nueva versión del Editor Sql (1.3). Una de las mejoras en dicho complemento es la posibilidad de obtener estadísticas de tiempo de ejecución, de los 'Lotes' sql, lo que permite realizar comparativas de rendimiento entre distintas sentencias.
A continuación expongo un ejemplo de Lote preparado para evaluar la velocidad de ejecución de distintos códigos, sobre la base de datos Neptuno.mdb. Solo comentar : para que los resultados sean fiables, es conveniente ejecutar el lote más de una vez.

---- Evitar funciones en el lado izquierdo de la expresión -----
------------------------------------------------------------------------

/*
Obtenemos los pedidos del mes de marzo, convirtiendo
los valores de FechaPedido mediante la función Month,
y comparando el resultado con un valor : 3
*/

Select * From Pedidos Where Month(FechaPedido)=3;

/*
Comparamos si FechaPedido se encuentra entre tres rangos,
que corresponden al mes de marzo de cada ejercicio. La sql
es más compleja y no se adapta dinámicamente si añadimos
nuevos registros (para un ejercicio diferente), pero es mucho
más rápida que la anterior puesto que no necesita procesar
con una función todos los registros.
*/

Select * From Pedidos
Where
FechaPedido Between #03/01/1996# And #03/31/1996#
Or
FechaPedido Between #03/01/1997# And #03/31/1997#
Or
FechaPedido Between #03/01/1998# And #03/31/1998#;

-------- Utilizar expresiones apropiadas al tipo de dato -------
-----------------------------------------------------------------------

/*
Obtenemos los pedidos cuya cantidad es múltiplo de 10,
empleando para ello un operador de texto (Like).
*/

Select * From [Detalles de pedidos]
Where Cantidad like '%0';

/*
Obtenemos los pedidos cuya cantidad es múltiplo de 10,
empleando para ello una función numérica. El rendimiento
mejora con respecto a la sql anterior.
*/

Select * From [Detalles de pedidos]
Where Cantidad Mod 10 = 0;

----------- Comparativa consultas de no coincidentes ---------
-----------------------------------------------------------------------

Select * From Pedidos
Where IdCliente Not In (Select IdCliente From Clientes);

Select Pedidos.*
From Pedidos Left Join Clientes
On Pedidos.IdCliente = Clientes.IdCliente
Where Clientes.IdCliente Is Null;

Select Pedidos.* From Pedidos
Where Not Exists
(Select IdCliente From Clientes Where Clientes.IdCliente = Pedidos.IdCliente);

/*
La sql más rápida la obtenemos al emplear Left Join,
a continuación Where Exists, y el método con peor
rendimiento es utilizar el operador IN.
*/

---------- Comparativa uso de Like frente a InStr() -----------
----------------------------------------------------------------------

Select * From Pedidos Where IdCliente Like '%bo%';

Select * From Pedidos Where InStr(1, IdCliente, 'bo') > 0;

/*
Aún cuando el uso del comodín '%' al principio del valor a
buscar, impide que Access utilice el índice, el operador
Like es mucho más eficiente que procesar cada registro
con la función InStr
*/

------------ Comparativa Distinct versus Group By ------------
----------------------------------------------------------------------

Select Distinct FechaPedido, FechaEntrega, FechaEnvío
From Pedidos;

Select FechaPedido, FechaEntrega, FechaEnvío
From Pedidos
Group By FechaPedido, FechaEntrega, FechaEnvío;

/*
... es más rápido Distinct que Group By ...
*/

--- Comparativa relacionar tablas en clausulas From o Where --
--------------------------------------------------------------------------

Select NombreProducto, NombreCompañía
From Productos Inner Join Proveedores
On Productos.IdProveedor = Proveedores.IdProveedor;

Select NombreProducto, NombreCompañía
From Productos, Proveedores
Where Productos.IdProveedor = Proveedores.IdProveedor;

/*
El rendimiento es análogo.
*/

¡ FELIZ NAVIDAD !

Ramon Poch, raipon. Terrassa, a 22/12/2008.

PROPIEDADES ÍNDICES

2008-11-02T23:48:00.000-08:00

Se trata de una consulta que explora los índices de una tabla determinada, devolviendo la siguiente información :

- Nombre del índice
- Si es o no Clave primaria
- Si es o no Clave externa
- Si admite o no valores repetidos
- Nombres de los campos que forman parte del índice

Para poder utiliar esta consulta, es imprescindible tener en la base de datos una tabla (de nombre 'Nums') con un solo campo (de nombre 'Num'), Entero largo, campo clave, con valores desde 0 a 99. Así mismo, puesto que usamos funciones de vba, será necesario establecer un nivel de seguridad apropiado (Dependerá de la versión de Access que utilicemos).

Esta es la consulta :

PARAMETERS Tabla Text ( 255 );
SELECT T.*,
Eval("CurrentDb.TableDefs('" & Tabla & "').Indexes('" & CStr(T.Nombre_del_índice) & "').Fields(" & Num & ").Name") AS Nombre_del_campo
FROM
(
Select Eval("CurrentDb.TableDefs('" & Tabla & "').Indexes(" & Num & ").Name") As Nombre_del_índice,
Choose(Abs(Eval("CurrentDb.TableDefs('" & Tabla & "').Indexes(" & Num & ").Primary"))+1,"No","Sí") As Es_clave_primaria,
Choose(Abs(Eval("CurrentDb.TableDefs('" & Tabla & "').Indexes(" & Num & ").Foreign"))+1,"No","Sí") As Es_clave_externa,
Choose(Abs(Eval("CurrentDb.TableDefs('" & Tabla & "').Indexes(" & Num & ").Unique"))+1,"No","Sí") As Sin_repeticiones
From Nums
Where Nums.Num<Eval("CurrentDb.TableDefs('" & Tabla & "').Indexes.Count")
) AS T,
(
Select Top 10 Num From Nums
) AS Nums
WHERE Num<Eval("CurrentDb.TableDefs('" & Tabla & "').Indexes('" & CStr(T.Nombre_del_índice) & "').Fields.Count")
ORDER BY T.Nombre_del_índice;

Ramon Poch, raipon. Terrassa, a 07/12/2007.

AUTONUMÉRICOS NEGATIVOS

2008-10-31T05:01:00.000-07:00

En cierta ocasión me vi en la siguiente necesidad : asegurarme que los valores de dos campos autonuméricos (de dos tablas distintas), no pudieran tener valores iguales. Para lograr tal objetivo, utilicé la siguiente instrucción para crear dichos campos :

Alter Table Tabla1 Add Column Id_1 Counter (1,1) Primary Key;
Alter Table Tabla2 Add Column Id_2 Counter (-1,-1) Primary Key;

La primera de las propiedades del campo, establece el valor inicial del autonumérico, mientras que el segundo define el valor de incremento.

Ramon Poch, raipon. Terrassa a 31/10/2008.

NUMERAR REGISTROS EN BASE A UN CAMPO CON REPETICIONES

2008-10-17T23:31:00.000-07:00

En el ejemplo anterior : http://sqlraipon.blogspot.com/2008/09/devolver-n-registros.html, utilizábamos una subconsulta para contar los registros con un valor 'menor', tomando como referencia uno de los campos. Este campo, debía cumplir dos condiciones : que el orden que se establece al utilizarlo como referencia, coincida con nuestro criterio para numerar los resultados de la consulta, y que no tenga valores duplicados.

Vamos a intentar numerar los resultados de una consulta, tomando como referencia para la precedencia entre registros uno de sus campos :

Create Table MiTbl (Id Counter Primary Key, Fecha DateTime);

Insert Into MiTbl (Fecha) Values(#10/02/2007#);
Insert Into MiTbl (Fecha) Values(#10/01/2008#);
Insert Into MiTbl (Fecha) Values(#10/03/2007#);
Insert Into MiTbl (Fecha) Values(#10/04/2007#);
Insert Into MiTbl (Fecha) Values(#10/10/2007#);
Insert Into MiTbl (Fecha) Values(#01/20/2007#);

SELECT * FROM
(
Select
(
Select Count(*) From MiTbl As T Where T.Fecha <= MiTbl.Fecha
) As Numerador,
MiTbl.*
From MiTbl
)
ORDER BY Numerador;

Drop Table MiTbl;

... los resultados devueltos son correctos (si queremos numerar según fecha), pero qué ocurre si hay fechas duplicadas ...

Create Table MiTbl (Id Counter Primary Key, Fecha DateTime);

Insert Into MiTbl (Fecha) Values(#10/02/2007#);
Insert Into MiTbl (Fecha) Values(#10/01/2008#);
Insert Into MiTbl (Fecha) Values(#10/03/2007#);
Insert Into MiTbl (Fecha) Values(#10/04/2007#);
Insert Into MiTbl (Fecha) Values(#10/10/2007#);
Insert Into MiTbl (Fecha) Values(#10/10/2007#);

SELECT * FROM
(
Select
(
Select Count(*) From MiTbl As T Where T.Fecha <= MiTbl.Fecha
) As Numerador,
MiTbl.*
From MiTbl
)
ORDER BY Numerador;

Drop Table MiTbl;

... no hay Numerador = 4 y sin embargo hay dos registros con Numerador = 5. ¿ Cómo solucionarlo ? ... Tomando para la comparación tantos campos como sea preciso para crear valores únicos. En este caso incorporaremos el campo Id a la subconsulta :

Create Table MiTbl (Id Counter Primary Key, Fecha DateTime);

Insert Into MiTbl (Fecha) Values(#10/02/2007#);
Insert Into MiTbl (Fecha) Values(#10/01/2008#);
Insert Into MiTbl (Fecha) Values(#10/03/2007#);
Insert Into MiTbl (Fecha) Values(#10/04/2007#);
Insert Into MiTbl (Fecha) Values(#10/10/2007#);
Insert Into MiTbl (Fecha) Values(#10/10/2007#);

SELECT * FROM
(
Select
(
Select Count(*) From MiTbl As T
Where CSng(T.Fecha) & T.Id <= CSng(MiTbl.Fecha) & MiTbl.Id
) As Numerador,
MiTbl.*
From MiTbl
)
ORDER BY Numerador;

Drop Table MiTbl;

... los valores del campo Numerador ya son coherentes, a pesar de que ordenemos por Fecha, y que para esta columna existan valores repetidos. Cabe destacar que Access realiza de forma implícita cambios en el tipo de datos (en campos calculados). Por ejemplo esta expresión : UnaFecha & UnNúmero lo convierte en texto. Para establecer un orden correcto, esto no supone ningún problema excepto si empleamos campos Fecha/Hora (nuestro caso, ¡ Qué mala suerte !), en cuyo caso el orden alfabético no coincide con el orden según Fecha (#20/10/2008# > #21/10/2007#, pero '20/10/2008' < '21/10/2007'). Para sortear esta dificultad, es preferible convertir las fechas a Single (CSng(#20/10/2008#) = 39741, CSng(#21/10/2007#) = 39376), de hecho, Access almacena internamente las Fechas como Single, siendo la parte entera los dias transcurridos desde el 31 de Diciembre de 1899, y la fracción decimal : Horas, minutos, segundos ...

Ramon Poch, raipon. Terrassa a 10/10/2008.

DEVOLVER N REGISTROS (PAGINAR)

2008-09-27T11:01:00.000-07:00

Para que una consulta devuelva una cantidad concreta de registros, utilizamos la cláusula top :

Select Top 10 * From Pedidos;

O bien, si en vez de una cantidad, queremos un porcentaje :

Select Top 10 Percent * From Pedidos;

Pero puede presentarse la siguiente necesidad : Obtener n registros por grupo (ver http://groups.google.es/group/microsoft.public.es.access/browse_thread/thread/e50cf402a9916ebb#)
En este ejemplo, evidentemente no nos sirve utilizar 'Top'. Una solución podría ser emplear una subconsulta que evaluara el número de registros devueltos para cada grupo. Y poner como condición que dicho número sea igual a n :

Ejemplo preparado para ejecutar desde el Editor Sql 1.2 http://groups.google.com/group/raipon?hl=es

Parameters n Long;

Create Table Tareas (id Long Primary Key, titulo Text (255), ambito Text (255), prioridad Bit);
Create Table Avance_Tareas (id Long, fecha_avance DateTime, descripcion Text (255));

Insert Into Tareas Values (1, 'Primera', Null, Null);
Insert Into Tareas Values (2, 'Segunda', Null, Null);

Insert Into Avance_Tareas Values (1, #09/30/2008#, Null);
Insert Into Avance_Tareas Values (1, #10/01/2008#, Null);
Insert Into Avance_Tareas Values (1, #10/02/2008#, Null);
Insert Into Avance_Tareas Values (1, #10/06/2008#, Null);

Insert Into Avance_Tareas Values (2, #09/30/2007#, Null);
Insert Into Avance_Tareas Values (2, #10/10/2007#, Null);
Insert Into Avance_Tareas Values (2, #10/11/2007#, Null);
Insert Into Avance_Tareas Values (2, #10/10/2008#, Null);

Select tareas.titulo, tareas.ambito, tareas.prioridad,
avance_tareas.fecha_avance, avance_tareas.descripcion
From tareas Inner Join Avance_Tareas
On tareas.id = Avance_Tareas.id
Where
(Select Count(*) From avance_tareas As T
Where T.id = tareas.Id And T.fecha_avance >= Avance_Tareas.fecha_avance) <= n;

Drop Table Tareas;
Drop Table Avance_Tareas;

Ramon Poch, raipon. Terrassa a 10/10/2008.

CÓDIGO SQL EMBEBIDO

2008-07-01T04:19:00.000-07:00

... la verdad es que pensaba titular este artículo así : 'Solo para valientes', pero no era demasiado descriptivo, ¿ o sí ?. En cualquier caso se trata de un tema solo recomendable a expertos en lenguaje sql, pues es fácil cometer errores que impidan el correcto funcionamiento de este sistema. Además, advierto que se debe proteger/controlar/verificar (mediante un formulario y el código que sea preciso) la 'bondad' de las reglas que se van a embeber. De no actuar así, estamos dejando una puerta abierta a que un usuario malintencionado nos haga un estropicio.

Para explicar de qué se trata, quizás sea mejor mediante un ejemplo : Supongamos que deseamos mostrar, mediante una consulta, los mensajes de correo electrónico que tenemos guardados en una tabla 'Mensajes'. Pero, imitando a los programas de correo, queremos filtrar el contenido, o el título, o el remitente de dichos mensajes. Para ello, hemos creado una segunda tabla 'Reglas', donde pensamos guardar expresiones que actúen como regla de validación de los correos. Es decir, queremos guardar fragmentos de código sql, dentro de una tabla y después ejecutar estos trozos de código dentro de una consulta.
Si habéis intentado algo por el estilo desde vba, ya sabréis que directamente no se puede, y que es necesario emplear 'un intermediario' que explique al compilador que una cadena de texto, no es tal, sino un fragmento de código. En este ejemplo hemos usado la función Eval() como intérprete de nuestros deseos. Sin embargo surge un problema con ello : para que este 'invento' tenga utilidad, es necesario, imprescindible, poder pasar valores por referencia, desde fuera de la regla, adentro de la misma. En concreto necesitamos que si en la regla escribimos el nombre de un campo de la tabla 'Mensajes', obtengamos el valor de dicho campo. Lamentablemente esto, en principio, no es posible, pero ... quizás es el momento de ver una secuencia (un lote) ejecutable desde el 'Editor Sql 1.1' que nos muestre cómo :

Dos advertencias previas : He substituido el operador Like y el carácter comodín '*' por ALike y '%'. Ha sido necesario por la forma de trabajar interna del Editor, pero para ejecutarse en una consulta de Access, esto no es necesario (a no ser que, en Opciones, tengamos activada la compatibilidad con Sql Server).
Para usar la función Eval() desde Sql, es necesario bajar el nivel de seguridad de Access.

/* Filtros embebidos, Ramon Poch, raipon. Terrassa a 13/05/2008 */

-- Creamos una tabla de mensajes de correo electrónico
Create Table Mensajes
(
Id Counter Primary Key,
Destinatario Text (255),
Titulo Text (255),
Cuerpo Memo
);

-- Insertamos unos cuantos valores de prueba
Insert Into Mensajes (Destinatario, Titulo, Cuerpo)
Values
("UnaDireccion@UnServidor.com",
"Prueba1",
"Oferta de electrodomesticos de ocasión ..."
);

Insert Into Mensajes (Destinatario, Titulo, Cuerpo)
Values
(
"OtraDireccion@OtroServidor.com",
"Prueba2",
"¿ Cómo te han ido estos dias de vacaciones ..."
);

Insert Into Mensajes (Destinatario, Titulo, Cuerpo)
Values
(
"UnaDireccion@UnServidor.com",
"Prueba3",
"Compra venta de ..."
);

-- Creamos una tabla de reglas
Create Table Reglas (Id Counter Primary Key, Regla Text (255));

-- Insertamos Reglas que sean capaces de restringir los mensajes
Insert Into Reglas (Regla)
Values
(
"'•Destinatario•' ALike '%@TutiSpam%'"
);

Insert Into Reglas (Regla)
Values
(
"'•Titulo•' = 'Gran oferta'"
);

Insert Into Reglas (Regla)
Values
(
"'•Cuerpo•' ALike '%Compra%'
Or
'•Cuerpo•' ALike '%Venta%'
Or
'•Cuerpo•' ALike 'Oferta'"
);

-- Veamos los mensajes ...
Select * From Mensajes;

-- Comprobamos qué efecto tiene el filtro :
Select * From Mensajes
Where Not Exists
(
Select Id From Reglas
Where
Eval(
Replace(
Replace(
Replace(Regla, "•Destinatario•", Destinatario),
"•Titulo•",Titulo),
"•Cuerpo•",Cuerpo)
)=-1
);

/* ¿ Qué hemos hecho ?. Para poder interpretar como código, el texto guardado en el campo Regla, utilizamos la función Eval(), sin embargo, desde dentro de esta función no podemos pasar valores por referencia de los campos 'Cuerpo', 'Titulo' o 'Destinatario' . Para solucionar este inconveniente empleamos la función Replace(), que sí que es capaz de leer por referencia los campos de la consulta y modificar la cadena de texto a interpretar por Eval(). Es decir, és la función Replace() la que 'pasa' el valor del campo a Eval(). Evidentemente es necesario emplear tantas veces la función Replace() embebida, como campos deseemos controlar. El carácter '•' (Alt 7) lo utilizamos para localizar de forma inequívoca el nombre del campo dentro de la regla.*/

-- Eliminamos los objetos del ejemplo
Drop Table Mensajes;
Drop Table Reglas;

En resumidas cuentas, se trata de un método que nos permite añadir funcionalidad a nuestras aplicaciones : los usuarios pueden modificar dinámicamente el comportamiento de algunas consultas, sin necesidad de alterar el diseño de las mismas.

Ramon Poch, raipon. Terrassa a 13/05/2008.

FULL JOIN EN ACCESS

2008-07-01T00:00:00.000-07:00

Del tutorial de sql de Claudio Casares (http://personal.lobocom.es/claudio/sql008.htm) : "... Full Join ... Este tipo de operador se utiliza para devolver todas las filas de una combinación tengan o no correspondencia. Es el equivalente a la utilización de LEFT JOIN y RIGHT JOIN a la misma vez ..."

Lamentablemente no disponemos de este operador en Access. Sin embargo, podemos emular su comportamiento :

-- Supongamos una tabla Autores ...
Create Table Autores (Id Int Primary Key, Nombre Text (255));
Insert Into Autores Values (1, "Ludwig Van Beethoven");
Insert Into Autores Values (2, "Wolfgang Amadeus Mozart");
Insert Into Autores Values (3, "Richard Strauss");
Insert Into Autores Values (4, "Gioachino Rossini");
Insert Into Autores Values (5, "Arrigo Boito");

-- Y una tabla de Operas ...
Create Table Operas (Id Int Primary Key, Título text (255), Autor Int);
Insert Into Operas Values (1, "Fidelio", 1);
Insert Into Operas Values (2, "La flauta mágica", 2);
Insert Into Operas Values (3, "El Barbero de Sevilla", 4);
Insert Into Operas Values (4, "Mefistofele", 5);
Insert Into Operas Values (5, "La Fuerza del destino", 6);

-- Y queremos mostrar en una consulta el nombre de la
-- ópera junto con el de su autor. En caso de que falte
-- algún dato, que aparezca el campo con valor nulo,
-- pero en cualquier caso que no omita el registro :
SELECT Autores.Nombre As Autor, Operas.Título
FROM
(
(
Select Id As Autor From Autores
Union
Select Autor From Operas
) AS T LEFT JOIN Operas
ON T.Autor = Operas.Autor
) LEFT JOIN Autores
ON T.Autor = Autores.Id;

-- Se trata por tanto de crear una consulta de unión
-- intermedia, que contenga todos los valores posibles
-- de ambas tablas, y armar la consulta final en base
-- a ella.

Drop Table Autores;
Drop Table Operas;

Ramon Poch, raipon. Terrassa a 04/06/2008

RECURSIVIDAD EN ACCESS (SQL + VBA)

2008-06-01T05:10:00.000-07:00

Podríamos definir la recursividad, como la capacidad de una instrucción de código, para llamarse a si misma, en busca de resultados más detallados. En las bases de datos, a veces puede ser necesario emplear esta técnica para explorar estructuras jerárquicas (en árbol). Si crees que esto es algo muy raro y alejado de la cotidianeidad, es conveniente que sepas que el propio Access utiliza este tipo de estructura en las tablas del sistema (MSysObjects, MSysQueries, etc). Sin embargo, el lenguaje sql de Access no admite recursividad, es decir, desde una consulta no podemos llamar a la propia consulta.

Bien, pues en este artículo veremos cómo podemos explorar estructuras en árbol, empleando recursividad, o algo parecido ...

Antes que nada, puesto que he afirmado que las tablas del sistema de Access almacenan la información de forma jerárquica, vamos a comprobarlo : Abrimos la tabla MSysObjects y veremos, entre otros, los siguientes campos : Id, Name, ParentId, ... si investigamos un poco la relación que existe entre los valores del primer y el último campo, comprobaremos que por ejemplo : del registro cuyo valor en el campo Name es 'Tables', cuelgan todas las tablas y consultas de la base de datos (ParentId = Id).

¿ Y qué utilidad tiene disponer la información de esta forma ?, ¿ No seria más útil emplear el modelo relac¡onal ?. En primer lugar, las tablas del sistema (en Access), solo tienen un nivel de anidamiento, es decir, que cada registro 'Padre' solo puede tener un grupo de registros subordinados, truncándose a este nivel la descendencia, por tanto, al realizar una exploración, el coste del proceso es mínimo ya que sabemos de antemano que solo hay que explorar un nivel. Y por otra parte, al reunir todos los nombres de 'objetos' en un único campo : 'Name', mediante un solo índice compuesto por los campos 'Name' y 'ParentId' podemos controlar que no haya nombres repetidos para el mismo tipo de objetos, de forma mucho más rápida que si tuviéramos dicha información repartida en varias tablas. De hecho, si en la vista diseño de la tabla MSysObjects, abrimos la pantalla que nos muestra los índices de la tabla, veremos que este índice sobre los dos campos efectivamente existe.

Para facilitar la creación de ejemplos, emplearemos una sola tabla : 'Empleados' de Neptuno.mdb, ya que a pesar de tener muy pocos registros, oculta una estructura jerárquica. Si la abrimos en vista diseño, podremos comprobar que cada registro, además de su correspondiente campo 'Id', dispone de un campo 'Jefe', que corresponde a un 'Id' de otro registro.

Supongamos que la empresa 'Importadores Neptuno', necesita mejorar la comercialización de sus productos, y entre otras medidas, ha decidido que todo el personal de ventas debe realizar un curso de márketing. El gerente de la sociedad, así se lo ha comunicado al Sr. Andrew Fuller, vicepresidente comercial, y este a su vez, ha encargado a la Srta. Laura Callahan, Coordinador de ventas interno, la elaboración de un listado del personal adscrito a su departamento. Naturalmente la Srta Callahan está dispuesta a emplear la información de la tabla 'Empleados' para obtener el susodicho listado, sin embargo, rápidamente se ha percatado de una dificultad : el campo 'Jefe' de cada empleado, define el superior inmediato de cada uno de ellos, pero dicho superior, puede a su vez estar subordinado a uno o varios responsables, ¿ cómo asegurarse de ir siguiendo todas las líneas ascendentes hasta llegar al Sr Fuller, sin olvidarse a nadie ?.

Hace varios años, leí una aportación de Marius Puig, en el foro de Access y vba : http://www.mvp-access.com/foro/default.asp, sobre consultas recursivas. Lamentablemente, puesto que los mensajes con cierta antigüedad se van borrando, dudo mucho que se pueda encontrar el hilo original. Sin embargo, he aquí la técnica que él empleaba :

Crear una función (pública) en un módulo. Dicho procedimiento, se encargará de devolver una cadena delimitada por comas (csv) de todos los 'hijos' de un determinado nodo. La función la utilizaremos en la cláusula 'Where' de una consulta.
Estas son la función y la consulta :

'Marius Puig
Public Function AWniveles(Tabla, Hijo, Padre, Valor)
Dim Dato, strRst, rst As DAO.Recordset

strRst = "select * from " & Tabla & " where " & Padre & "=" & Valor
Set rst = CurrentDb.OpenRecordset(strRst)
With rst
Do While Not .EOF
Dato = Dato & AWniveles(Tabla, Hijo, Padre, .Fields(Hijo))
.MoveNext
Loop
End With
AWniveles = Valor & ";" & Dato

End Function

SELECT
Empleados.IdEmpleado AS IdJefe,
Empleados.Nombre AS NombreJefe,
Duplicado.IdEmpleado AS IdEmpleado,
Duplicado.Nombre AS NombreEmpleado
FROM
Empleados, Empleados AS Duplicado
WHERE
InStr
(
AWniveles
(
"Empleados", "IdEmpleado","Jefe",Empleados.IdEmpleado
), Duplicado.IdEmpleado & ";"
)>0
And
Empleados.IdEmpleado <> Duplicado.IdEmpleado;

... ¡ Genial !, no solo va recorriendo todas las ramas del árbol, sino que además la función AWniveles se llama a sí misma de forma recursiva.
Para obtener 'limpios' los empleados que dependen del Sr. Fuller, tan solo tenemos que añadir este criterio a la consulta :
... And Empleados.IdEmpleado = 2 ...
Sin embargo, esta estructura tiene un inconveniente : es necesario generar más registros de los que devuelve la tabla original, por lo que en la consulta empleamos un 'Cross Join' (no relacionar las tablas), de la tabla consigo misma, lo que nos devuelve una cantidad de registros equivalente a elevar al cuadrado la cantidad de líneas de la tabla. En este caso, 'Empleados' tiene 9 registros por lo que la consulta, antes de filtrarla en la cláusula 'Where' devuelve 81 items. Naturalmente, a poco que 'abulte' la tabla implicada, el rendimiento decae rápidamente (una tabla de 1.000 registros, devuelve 1.000.000).

Una alternativa para evitar el 'Cross Join' : Emplear una tabla temporal para insertar recursivamente los registros :

'Ramon Poch, raipon. Terrassa a 24/05/2008
'El parámetro 'Sql_Orígen', puede ser el nombre de una tabla
'o consulta (emplear corchetes para delimitarlo si contiene
'caracteres extraños o espacios), o una instrucción sql, en
'cuyo caso, delimitaremos la instrucción sql entre paréntesis.
Sub InsertRecursivo(Padre As String, Hijo As String, Sql_Orígen As String)
On Error GoTo Err_InsertRecursivo
Dim qdf As DAO.QueryDef
Dim contador As Long
Dim control As Long: control = 1

'Creamos la tabla para guardar la información
CurrentProject.Connection.Execute _
"Create Table Recursiva " & _
"(" & _
"Nivel Integer, Id_Parent Long, Id_Fill Long " & _
")"

'Insertamos un primer nivel de exploración del árbol en la tabla temporal.
CurrentDb.Execute _
"Insert Into Recursiva " & _
"Select 0 As Nivel, Temp.[" & Hijo & "] As Id_Parent, tbl_origen.[" & Hijo & "] As Id_Fill " & _
"From (Select * From " & Sql_Orígen & ") As Temp " & _
"Inner Join (Select * From " & Sql_Orígen & ") As tbl_origen " & _
"On Temp.[" & Hijo & "] = tbl_origen.[" & Padre & "]"

Set qdf = CurrentDb.CreateQueryDef("", _
"Parameters p_Nivel Long; " & _
"Insert Into Recursiva " & _
"Select p_Nivel + 1 As Nivel, Temp.Id_Parent, Recursiva.Id_Fill " & _
"From Recursiva As Temp Inner Join Recursiva " & _
"On Temp.Id_Fill = Recursiva.Id_Parent " & _
"Where Recursiva.Nivel = P_Nivel")

'Iniciamos un insert recursivo, filtrando su alcance a los registros
'agregados en la última acción. Cuando la propiedad RecordsAffected
'sea igual a cero, abandonamos el bucle ...
Do While control > 0
qdf.Parameters("p_Nivel") = contador
contador = contador + 1
qdf.Execute
control = qdf.RecordsAffected
Loop

Set qdf = Nothing

Exit_InsertRecursivo:
Exit Sub

Err_InsertRecursivo:
MsgBox Err.Description
On Error Resume Next
If DCount("*", "MSysObjects", "Name = 'Recursiva' And Type = 1") > 0 Then
CurrentProject.Connection.Execute _
"Drop Table Recursiva"
End If
Resume Exit_InsertRecursivo

End Sub

'Procedimiento de llamada
Sub llamada()
InsertRecursivo "Jefe", "IdEmpleado", "Empleados"
End Sub

En este punto, si queremos recuperar la información de forma más legible, solo tenemos que realizar una consulta sobre los valores guardados en la tabla temporal : 'Recursiva'

Select
(Select Nombre From Empleados Where IdEmpleado = Id_Parent) As Jefe,
(Select Nombre From Empleados Where IdEmpleado = Id_Fill) As Empleado
From Recursiva

O mejor aún, puesto que no utilizaremos subconsultas :

Select
E1.Nombre + ' ' + E1.Apellidos As Jefe,
E2.Nombre + ' ' + E2.Apellidos As Empleado
From
(Recursiva Inner Join Empleados As E1 On Recursiva.Id_Parent = E1.IdEmpleado)
Inner Join Empleados As E2 On Recursiva.Id_Fill = E2.IdEmpleado

El procedimiento 'InsertRecursivo' es verdaderamente rápido, permitiéndonos incluso obtener cientos de miles de registros en escasos segundos. La desventaja es que debemos controlar la posterior eliminación de la tabla temporal 'Recursiva', o si queremos mantenerla en la base de datos : vaciarla antes o después de cada uso.

Ramon Poch, raipon. Terrassa a 24/05/2008.

PROPIEDAD COLUMN EN CONSULTA

2008-06-01T00:01:00.000-07:00

El lenguaje sql de Access permite hacer referencia al valor de un control de un formulario :

Ejemplos sobre Neptuno.mdb :

SELECT *
FROM Proveedores
WHERE IdProveedor = Forms!Proveedores!IdProveedor

pero si intentamos lo mismo con otras propiedades del control, por ejemplo Column, nos da error :

SELECT *
FROM Proveedores
WHERE NombreCompañía = Forms!Proveedores!IdProveedor.Column(1);

Normalente para manejar este tipo de casos, construimos dinámicamente la consulta desde vba, pero no es la única forma ...
La solución : utilizar Eval(). Esta función nos permite crear un puente entre propiedades que solo están disponibles desde vba y sql :

SELECT *
FROM Proveedores
WHERE NombreCompañía = Eval("Forms.Productos.IdProveedor.Column(1)");

Y ahora sí, al ejecutar la consulta, visualizaremos el registro del proveedor con el 'NombreCompañía' que muestre el combo IdProveedor en el formulario 'Productos' en ese momento.

Ramon Poch, raipon. Terrassa a 01/02/2007.

FUNCIÓN AGREGADA DE DOMINIO ENCADENAR

2008-05-25T23:31:00.000-07:00

Se trata de una función creada en vba, po lo tanto, debe estar en un módulo :

'////////////////////////////////////////////////////////////////////
'Ramon Poch, raipon. Terrassa a 13/11/2005
'Encadenar, permite obtener una cadena con los valores de un campo de
'una Tabla, consulta o instrucción sql, separados por un caracter
'a elegir. Su uso es muy similar a las funciones agregadas de dominio.
' -Separador es el caracter delimitador que escogemos para separar los
' distintos valores.
' -Tabla puede ser una la tabla, consulta, o intrucción sql.
' -Campo es una expresión que nos identifica una columna (de la Tabla,
' consulta o instrucción sql) de la que queremos concatenar sus valores.
' -Condición (opcional) nos permite filtrar los resultados. Similar a una condición
' Where pero sin dicha palabra. Al igual que las funciones agregadas de dominio,
' debemos tener las siguientes precauciones al pasar un valor o una referéncia a
' un campo :
' -Delimitar el valor o el campo según el tipo de dato (usar ' para
' texto y # para fechas.
' Ejemplo "Ciudad='Barcelona'" o "CampoFecha=#" & CampoFecha & "#"
' -Ademas, en lo que respecta a las fechas debemos formatearlas al
' estilo 'Americano'.
' Ejemplo "CampoFecha=#12/31/2005#" o
' "CampoFecha=#" & Format(CampoFecha, "mm/dd/yyyy") & "#"
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
Public Function Encadenar(Separador As String, tabla As String, Campo As String, _
Optional Condición As String) As String
On Error Resume Next
Dim rst As New ADODB.Recordset

rst.Open _
"Select " & Campo & " From (" & tabla & ") WHERE " & Campo & " Is not null" & _
IIf(Len(Condición) > 0, " AND " & Condición, ""), _
CurrentProject.Connection, adOpenStatic, adLockReadOnly

If Err.Number > 0 Then
Encadenar = ""
rst.Close
Exit Function
End If

If rst.EOF Then
Encadenar = ""
Else
Encadenar = rst.GetString(adClipString, , Separador, Separador)
Encadenar = Left(Encadenar, Len(Encadenar) - Len(Separador))
End If

rst.Close
End Function

Una vez esté guardado el módulo, podemos llamar a dicha función obteniendo una cadena con los valores del campo escogido concatenados, delimitada por el caracter que hayamos definido. Ejemplo sobre la tabla Proveedores de Neptuno.mdb :

SELECT Proveedores.NombreCompañía,
Encadenar(", ", "Productos","NombreProducto", "IdProveedor=" &
[Proveedores].[IdProveedor]) AS Productos
FROM Proveedores;

Ramon Poch, raipon. Terrassa a 13/11/2005

SEGURIDAD CON RESTRICCIÓN CHECK

2008-05-15T06:58:00.000-07:00

No, esta vez no voy a tratar el tema de la seguridad por usuarios, sino de un método alternativo que surgió a raíz de una pregunta en el 'Foro de Access y vba' : http://www.mvp-access.com/foro/default.asp el día 07/05/2008. Se trata de impedir que nadie pueda insertar nuevos registros, o modificar los existentes, al margen de los formularios y herramientas que hayamos previsto para ello. En otras palabras, asegurarnos que no se pueda acceder directamente a las tablas de la base de datos.
Para conseguir este objetivo obligaremos a los usuarios a entrar en nuestra aplicación con un acceso directo, que abra el programa en modo Runtime.
Configuración del acceso directo : Escribir la propiedad 'Destino', del acceso directo según este esquema :

"C:\Archivos de programa\Microsoft Office\OFFICE11\MSACCESS.EXE"
"C:\MiAplicación.mdb" /Runtime

Es decir, la ruta de la versión de Access que tengamos instalada (en este ejemplo Access 2003) y la ruta de la base de datos, seguida de la opción /Runtime.
Bien, ¿ pero cómo impedir que alguien abra la aplicación en modo normal ? : no podemos. Pero sí que es posible crear una restricción check sobre la tabla/s que deseemos controlar, de tal manera que impida insertar nuevos registros o editarlos si no hemos abierto la aplicación en modo Runtime.

Un ejemplo sobre una tabla llamada MiTabla :

Alter Table MiTabla Add Constraint Regla1 Check
((Select Top 1 (SysCmd(6) = True) As Regla From MSysObjects)=True);

Para ejecutar esta instrucción, lee el artículo anterior (Limitar nº registros en Tabla) o también puedes ejecutarla directamente mediante el Editor Sql 1.1 :
http://groups.google.com/group/raipon?hl=es

Es de destacar, que dado el comportamiento de Access (ejecutarse siempre en el 'lado cliente'), si tenemos nuestra aplicación dividida entre Front End y Back End (Objetos <=> Datos), aún cuando la restricción check se guarde con los datos (ya que depende de la tabla), la función SysCmd(6) evaluará el modo de apertura desde la base de datos activa. Por tanto, este método también funciona correctamente si desde el Front End empleamos tablas vinculadas, o accedemos a los datos a través de Dao o Ado.

Ramon Poch, raipon. Terrassa a 09/05/2008.

LIMITAR Nº REGISTROS EN TABLA

2008-05-09T06:40:00.000-07:00

Desde la versión 4.0 del motor Jet, se incorporaron algunas características nuevas, para mayor compatibilidad de Access con otros sistemas de bases de datos. En concreto esta versión de Jet, mediante ADO, permite crear restricciones Check sobre tablas o columnas, al estilo de Sql Server.
Para ello, o modificamos (en versiones superiores a Access 2000) 'Herramientas' => 'Opciones' => 'Tablas o consultas' => 'Sintaxis compatible con Sql Server (ANSI 92)' => 'Esta base de datos' (Hay que marcar el check box), y ejecutamos la sentencia ddl desde la vista sql de una consulta, o deberemos utilizar ADO (esta instrucción sql fallará si intentamos ejecutarla mediante DAO).
Una restricción Check es una regla de validación para una columna o una tabla. A diferéncia de la propiedad 'Regla de validación' de los campos de una tabla en vista diseño, con una restricción check podemos definir una regla basada en una instrucción sql (en la vista diseño de una tabla, ni siquiera podemos usar funciones agregadas de dominio como regla de validación).

Ejemplo : Crear una regla de validación a nivel de tabla, que limite la cantidad de registros :

(En este ejemplo, la tabla se llama MiTabla, y el número de registros que permitiremos será de 1)

CurrentProject.Connection.Execute _
"ALTER TABLE MiTabla ADD CONSTRAINT Regla1 " & _
"CHECK ((Select Count(*) From MiTabla) <= 1);"

Una vez ejecutado este código, el número máximo de registros que admitirá MiTabla será de uno. Tampoco podremos eliminar la tabla sin antes eliminar la restricción.

Para eliminar la restricción anterior :

CurrentProject.Connection.Execute _
"ALTER TABLE MiTabla DROP CONSTRAINT Regla1"

Finalmente comentar que este tipo de reglas solo están visibles en la tabla del sistema MSysObjects.

Ramon Poch, raipon. Terrassa a 03/01/2007.

DESCOMPONER LISTA CSV EN REGISTROS

2008-04-26T04:26:00.000-07:00

Esta idea, está basada en una genialidad de Itzik Ben-Gan, MVP de Sql Server. Si quereis ver el hilo de donde saqué el código original : http://groups.google.es/group/microsoft.public.es.sqlserver/browse_thread/thread/488acae128396270/252b881f6ea6a573?lnk=gst&q=BEN+GAN#252b881f6ea6a573

-- La clausula parameters la situamos al inicio de este lote, por exigencias del
-- Editor Sql 1.1, pero de hecho corresponde a la consulta de selección.
-- En 'Cadena' debemos escribir una lista separada por comas : ','
PARAMETERS Cadena Text (255);

-- Creamos una tabla con números de 0 a 9 :
Create Table Nums (Num Long Primary Key);
Insert Into Nums Values (0);
Insert Into Nums Values (1);
Insert Into Nums Values (2);
Insert Into Nums Values (3);
Insert Into Nums Values (4);
Insert Into Nums Values (5);
Insert Into Nums Values (6);
Insert Into Nums Values (7);
Insert Into Nums Values (8);
Insert Into Nums Values (9);

SELECT Número,
Mid(Cadena,Número+1,Abs(InStr(Número+1,Cadena,",")-Número-1))
FROM
(
Select (TNums_2.Número*10) + Nums.Num As Número
From
(
Select (TNums_1.Num*10) + Nums.Num As Número
From Nums, Nums As TNums_1
) As TNums_2, Nums
) AS TNums_3
WHERE Número<=Len(Cadena) And (Número <> 0 Imp Mid(Cadena,Número,1)=",");

DROP TABLE Nums;

Veamos una aplicación práctica : Separar el contenido de un campo de texto, en tantos campos como palabras compongan dicho campo. Es decir, se trata de algo parecido a las consultas para separar nombre y apellidos, pero en este caso, el número de campos, puesto que se trata de una consulta de referencias cruzadas, no es fijo sino variable. Emplearemos como carácter delimitador de las palabras el 'espacio', y basaremos nuestra consulta en el campo 'NombreContacto' de la tabla 'Clientes' de Neptuno.mdb. Además, puesto que Access no permite crear una consulta de unión no basada en tabla alguna (ver ejemplo de Itzik), también será preciso disponer de una tabla (Nums) con valores entre 0 y 9 :

Create Table Nums (Num Long Primary Key);

Insert Into Nums Values (0);
Insert Into Nums Values (1);
Insert Into Nums Values (2);
Insert Into Nums Values (3);
Insert Into Nums Values (4);
Insert Into Nums Values (5);
Insert Into Nums Values (6);
Insert Into Nums Values (7);
Insert Into Nums Values (8);
Insert Into Nums Values (9);

TRANSFORM
First(Mid(NombreContacto,Número,InStr(Número,NombreContacto & " "," ")-Número))
SELECT NombreContacto
FROM
(
Select (TNums_2.Número*10)+ Nums.Num + 1 As Número
From
(
Select (TNums_1.Num*10)+ Nums.Num As Número
From Nums, Nums As TNums_1
) As TNums_2, Nums
) AS TNums_3, Clientes
WHERE
Número<=Len(NombreContacto) And Mid(" " & NombreContacto,Número,1) = " "
GROUP BY NombreContacto
PIVOT
"Segmento_" & Número-Len(Replace(Left(NombreContacto,Número)," ",""))+1;

DROP TABLE Nums;

Otro ejemplo al hilo de las News. En este caso se trata de descomponer la lista embebida dentro del campo de texto, y repetir el mismo registro tantas veces como items tenga la susodicha lista :
http://groups.google.es/group/microsoft.public.es.access/browse_thread/thread/104f793a9ee8fd1e/ce28ad0e5aa2133b?lnk=raot#ce28ad0e5aa2133b

Create Table Nums (Num Long Primary Key);

Insert Into Nums Values (0);
Insert Into Nums Values (1);
Insert Into Nums Values (2);
Insert Into Nums Values (3);
Insert Into Nums Values (4);
Insert Into Nums Values (5);
Insert Into Nums Values (6);
Insert Into Nums Values (7);
Insert Into Nums Values (8);
Insert Into Nums Values (9);

Create Table tbl_musica (Id Counter, Artista Text (255));

Insert Into tbl_musica (Artista) Values ('Pepe/Juan/Paco/Roberto');
Insert Into tbl_musica (Artista) Values ('Gema');
Insert Into tbl_musica (Artista) Values ('Ramon/Juan/Pepito/Roberto');
Insert Into tbl_musica (Artista) Values ('Irene/Juan/Paco/Roberto');
Insert Into tbl_musica (Artista) Values ('Alex/Juan/Paco/Roberto/Alejandro');
Insert Into tbl_musica (Artista) Values ('Curro Romero');

SELECT Id, Num, Mid(Artista,Num+1,Abs(InStr(Num+1,Artista & "/","/")-Num-1))
FROM
(
Select (TNums_1.Num*10)+ Nums.Num As Num
From Nums, Nums As TNums_1
) AS TNums_2, tbl_musica
WHERE Num<=Len(Artista) And (Mid(Artista,IIf(Num=0,1,Num),1) = "/" Or Num=0);

DROP TABLE tbl_musica;
DROP TABLE Nums;

Puesto que las cadenas contenidas en el campo de texto son cortas, he usado una combinación que solo devuelve valores numéricos entre 0 y 100.

Ramon Poch, raipon. Terrassa a 19/01/2007.

USO DE CONSTANTES EN SQL

2008-04-24T07:41:00.000-07:00

La cláusula 'Parameters', tan injustamente infrautilizada por los programadores de Access, además de dar la seguridad de que el valor que pasamos como parámetro, va a ser tratado adecuadamente según el tipo de dato, podemos emplearla para definir constantes a usar dentro de una sentencia sql.
Veamos un ejemplo (sacado de una pregunta en las News) :
Obtener el número de semana del mes de una fecha dada, por ejemplo para 31 de Marzo de 2008.

Select ((Day(#03/31/2008#) - WeekDay((#03/31/2008#,2))\7)+Abs(((Day((#03/31/2008#) - WeekDay((#03/31/2008#,2))Mod 7) > 0)+1

Ha sido necesario escribir cuatro veces la fecha para obtener el valor : como mínimo tedioso ...

Vamos a usar la propiedad 'Default' de un parámetro (no nos va a preguntar el valor, cuando ejecutemos la consulta), para crear algo parecido a una constante :

Parameters F DateTime=#03/31/2008#;
Select
((Day(F) - WeekDay(F,2))\7)+Abs(((Day(F) - WeekDay(F,2))Mod 7) > 0)+1

... más corto, más legible, más cómodo, ...

Ramon Poch, raipon. Terrassa, a 01/04/2008.

SERIES NUMÉRICAS

2008-04-23T22:41:00.000-07:00

Cómo crear series numéricas a partir de una tabla (Nums) con diez dígitos : 0,1,2,3,4,5,6,7,8,9 en el campo Num :

Create Table Nums (Num Long Primary Key);
Insert Into Nums Values (0);
Insert Into Nums Values (1);
Insert Into Nums Values (2);
Insert Into Nums Values (3);
Insert Into Nums Values (4);
Insert Into Nums Values (5);
Insert Into Nums Values (6);
Insert Into Nums Values (7);
Insert Into Nums Values (8);
Insert Into Nums Values (9);

-- de 1 a 100 :

SELECT (TNums_1.Num*10)+Nums.Num+1 AS Número
FROM Nums, Nums AS TNums_1
ORDER BY (TNums_1.Num*10)+Nums.Num+1;

-- de 1 a 1000 :

SELECT (TNums_2.Número*10)+Nums.Num+1 AS Número
FROM
(
Select (TNums_1.Num*10)+Nums.Num As Número
From Nums, Nums AS TNums_1
) AS TNums_2, Nums
ORDER BY (TNums_2.Número*10)+Nums.Num+1;

-- de 1 a 10.000

SELECT (TNums_3.Número*10)+Nums.Num+1 AS Número
FROM
(
Select (TNums_2.Número*10)+Nums.Num As Número
From
(
Select (TNums_1.Num*10)+Nums.Num As Número
From Nums, Nums As TNums_1
) As TNums_2, Nums
) AS TNums_3, Nums
ORDER BY (TNums_3.Número*10)+Nums.Num+1;

-- de 1 a 100.000

SELECT (TNums_4.Número*10)+Nums.Num + 1 AS Número
FROM
(
Select (TNums_3.Número*10)+Nums.Num As Número
From
(
Select (TNums_2.Número*10)+Nums.Num As Número
From
(
Select (TNums_1.Num*10)+Nums.Num As Número
From Nums, Nums As TNums_1
) As TNums_2, Nums
) As TNums_3, Nums
) AS TNums_4, Nums
ORDER BY (TNums_4.Número*10)+Nums.Num + 1;

/*
... etc, la idea es aprovechar, el no relacionar las tablas, para nuestro interés. La clausula Order by, la he incorporado para que si ejecutais estas consultas, obtengais un resultado ordenado y legible, pero obviamente demoran la ejecución de la 'select', así que si incorporais esta consulta dentro de otra, es recomendable eliminar el 'Order by'.

Un ejemplo de las posibles aplicaciones de generar series numéricas de esta forma : una consulta de referencias cruzadas como calendario perpetuo :
*/

PARAMETERS Año Value, Mes Value;
TRANSFORM First(Temp.Número) AS Dia
SELECT CInt(Format(DateSerial(Año,Mes,Temp.Número),"ww",2)) AS Setmana
FROM
(
Select (TNums_1.Num*10)+Nums.Num+1 As Número
From Nums, Nums As TNums_1
Where (TNums_1.Num*10)+Nums.Num+1 <= Day(DateSerial(Año, Mes+1,0))
) AS Temp
GROUP BY CInt(Format(DateSerial(Año,Mes,Temp.Número),"ww",2))
PIVOT WeekdayName(Weekday(DateSerial(Año,Mes,Temp.Número),2),0,2);

-- Eliminamos la tabla de este ejemplo
DROP TABLE Nums;

Ramon Poch, raipon. Terrassa a 20/12/2005.

CAMPO AUTONUMÉRICO ¿ EDITABLE ?

2008-04-22T03:51:00.000-07:00

En este ejemplo veremos como modificar un campo autonumérico para que permita ediciones.

Advertencias previas :
- Hoy (22/04/2008), he modificado el complemento 'Editor Sql 1.1' para que admita las instrucciones 'Grant' y 'Revoke'. Por tanto, si has obtenido el Editor en fecha anterior, bájalo de nuevo y vuélvelo a instalar.
- Estas instrucciones solo funcionarán correctamente en bases de datos con formato 2000 y 2003.
- El campo autonumérico efectivamente permitirá ediciones, pero solo desde lenguaje sql.
- En principio, modificar un autonumérico es una mala praxis, pero de todas formas hay ocasiones en que puede ser útil saberlo hacer. Y aquí estamos para eso, para explicar 'cosas raras'.
- Para este ejemplo, se utiliza el usuario 'Admin', que es el usuario predeterminado de Access.

Esta secuencia de instrucciones está pensada para ejecutarla directamente como un nuevo 'Lote', en el 'Editor Sql'. Dado que está profusamente comentada, no añadiré nada más :

/*Ejemplo del uso del permiso UpdateIdentity */

-- Creamos la tabla
Create Table Ejemplo (Id Counter Primary Key, Campo1 Text (255));

-- Insertamos valores de ejemplo
Insert Into Ejemplo (Campo1) Values ("Valor1");
Insert Into Ejemplo (Campo1) Values ("Valor2");
Insert Into Ejemplo (Campo1) Values ("Valor3");
Insert Into Ejemplo (Campo1) Values ("Valor4");
Insert Into Ejemplo (Campo1) Values ("Valor5");

-- Mostramos el contenido de la tabla
Select * From Ejemplo;

-- Eliminamos un registro para crear una discontinuidad
Delete * From Ejemplo Where Id = 3;

-- Iniciamos secuencia para renumerar los valores, y restaurar
-- la continuidad en la serie de valores del campo 'Id'
GRANT UPDATEIDENTITY ON TABLE Ejemplo TO Admin;
Update Ejemplo Set Id = Id - 1 Where Id >= 3;

-- Restauramos los permisos originales
REVOKE UPDATEIDENTITY ON TABLE Ejemplo FROM Admin;

-- Si además, queremos evitar que la inserción de un nuevo registro
-- cree otra discontinuidad (puesto que el siguiente valor para 'Id'
-- será : 6, modificamos el campo 'Id' para que el nuevo valor sea 5 :
Alter Table Ejemplo Alter Column Id Identity (5, 1);

-- Mostramos el contenido de la tabla, renumerada
Select * From Ejemplo;

-- Finalmente, eliminamos la tabla Ejemplo
Drop Table Ejemplo;

Ramon Poch, raipon. Terrassa a 22/04/2008.

BUSCAR PALABRA EN CONSULTAS

2008-04-21T09:34:00.000-07:00

¿ Quieres saber en qué consultas empleas una función determinda, o está implicada una tabla, o un campo concreto, o utilizas un valor 'x' para filtrar los resultados, o ... ?. Si la respuesta a esta pregunta es afirmativa, quizás te interese la siguiente consulta, pues es capaz de buscar palabras completas dentro de las consultas de la base de datos.
Es imprescindible bajar el nivel de seguridad de la base de datos al mínimo, puesto que la sql de la consulta llamará a funciones de vba, caso contrario se producirá un error.

SELECT T.Name
FROM
(
Select Name From MsysObjects
Where Left$(MsysObjects.Name,1)<>"~" And Type = 5 And
Len(Trim(Palabra_a_buscar))>0
) AS T
WHERE Eval("CurrentDb.QueryDefs('" & Name & "').Sql") Like Palabra_a_buscar
Or
Eval("CurrentDb.QueryDefs('" & Name & "').Sql") Like Palabra_a_buscar &
"[ -/,',:,;,>,<,<>,=,^,\,[,-,+," & Chr(13) & "]*"
Or
Eval("CurrentDb.QueryDefs('" & Name & "').Sql") Like Palabra_a_buscar &
"]*"
Or
Eval("CurrentDb.QueryDefs('" & Name & "').Sql") Like
"*[ -/,',:,;,>,<,<>,=,^,\,[,-,+," & Chr(13) & "]" & Palabra_a_buscar
Or
Eval("CurrentDb.QueryDefs('" & Name & "').Sql") Like "*]" & Palabra_a_buscar
Or
Eval("CurrentDb.QueryDefs('" & Name & "').Sql") Like "*]" & Palabra_a_buscar &
"[ -/,',:,;,>,<,<>,=,^,\,[,-,+," & Chr(13) & "]*"
Or
Eval("CurrentDb.QueryDefs('" & Name & "').Sql") Like
"*[ -/,',:,;,>,<,<>,=,^,\,[,-,+," & Chr(13) & "]" & Palabra_a_buscar & "]*"
Or
Eval("CurrentDb.QueryDefs('" & Name & "').Sql") Like
"*[ -/,',:,;,>,<,<>,=,^,\,[,-,+," & Chr(13) & "]" & Palabra_a_buscar &
"[ -/,',:,;,>,<,<>,=,^,\,[,-,+," & Chr(13) & "]*";

Ramon Poch, raipon. Terrassa a 26/03/2008.

CONSULTA DE REFERENCIAS CRUZADAS DE MÚLTIPLES VALORES.

2008-04-21T00:11:00.000-07:00

Los ejemplos están basados en la base de datos Neptuno.mdb.
En principio, una consulta de referencias cruzadas solo puede tener una expresión para calcular los encabezados de columna. Es decir, como ejemplo : no podemos obtener el número de pedidos por empleado y año, y al mismo tiempo, la fecha del último pedido por empleado y año.
Para solucionar este inconveniente, desde Microsoft proponen esta solución : http://support.microsoft.com/kb/304458/es
El inconveniente de este método es que necesita conocer previamente los nombres de las columnas generadas por las consultas de referencias cruzadas, pues desde la consulta donde se combinan las dos consultas previas, se hace referencia explícita a cada uno de los campos por su nombre. Esto, si las consultas devuelven un número fijo de campos no es problema, pero si el número de campos es variable ...
Una solución seria no llamar los campos directamente, sino utilizar el asterisco para seleccionarlos todos :
Supongamos que tenemos estas dos consultas :

RefCruzPedidosCantidad

TRANSFORM Count(Pedidos.IdPedido) AS CuentaDeIdPedido
SELECT Pedidos.IdEmpleado
FROM Pedidos
GROUP BY Pedidos.IdEmpleado
PIVOT "Pedidos_" & Year(FechaPedido);

RefCruzPedidosÚltimo

TRANSFORM Max(Pedidos.FechaPedido) AS MaxDeFechaPedido
SELECT Pedidos.IdEmpleado
FROM Pedidos
GROUP BY Pedidos.IdEmpleado
PIVOT "Último_" & Year(FechaPedido);

Para mostrar todos los resultados a la vez :

SELECT RefCruzPedidosCantidad.*, RefCruzPedidosÚltimo.*
FROM RefCruzPedidosCantidad INNER JOIN RefCruzPedidosÚltimo
ON RefCruzPedidosCantidad.IdEmpleado = RefCruzPedidosÚltimo.IdEmpleado;

Bien, efectivamente ahora se muestran todas las columnas sin necesidad de llamarlas de forma explícita. Sin embargo nos encontramos con el inconveniente de que el campo IdEmpleado aparece dos veces.
Para solucionar este problema, un método alternativo, que gracias a una consulta de unión, embebida dentro de la consulta de referencias cruzadas, permite obtener directamente los resultados :

TRANSFORM First(Temp.Valor)
SELECT Temp.IdEmpleado
FROM
(
Select IdEmpleado, Count(IdPedido) As Valor, Year(FechaPedido) As Año,
"Pedidos_" & Año As Campo
From Pedidos
Group by IdEmpleado, Year(FechaPedido)
Union Select IdEmpleado, Max(FechaPedido) As Valor, Year(FechaPedido) As Año,
"Último_" & Año As Campo
From Pedidos
Group by IdEmpleado, Year(FechaPedido)
) AS Temp
GROUP BY IdEmpleado
PIVOT Campo;

Finalmente, debido a que las columnas se generan en base al campo 'Campo', la consulta anterior muestra los campos en este orden :
IdEmpleado, Pedidos_1996, Pedidos_1997, Pedidos_1998, Último_1996, etc
Si queremos que aparezcan agrupadas por año, es necesario cambiar la expresión que genera los títulos de columna para que establezca otro orden :

TRANSFORM First(Temp.Valor)
SELECT Temp.IdEmpleado
FROM
(
Select IdEmpleado, Count(IdPedido) As Valor, Year(FechaPedido) As Año,
Año & "_Pedidos" As Campo
From Pedidos
Group by IdEmpleado, Year(FechaPedido)
Union Select IdEmpleado, Max(FechaPedido) As Valor, Year(FechaPedido) As Año,
Año & "_Último" As Campo
From Pedidos
Group by IdEmpleado, Year(FechaPedido)
) AS Temp
GROUP BY IdEmpleado
PIVOT Campo;

Ahora el orden de las columnas es :
IdEmpleado, 1996_Pedidos, 1996_Último, 1997_Pedidos, 1997_Último, etc

Ramon Poch, raipon. Terrassa a 05/01/2007.