Usando Access

Numerar consultas com eficiência

Adquira a assinatura vitalícia do site e passe a ter acesso aos arquivos exemplos, vídeos, revistas, livros e ao kit de montagem de ribbons. Você terá suporte por e-mail, caso necessite tirar dúvidas pontuais. Clique AQUI e veja como obter um dos nossos planos. Clique AQUI e faça uma visita a seção Downloads do site e verifique as centenas de arquivos que estará à sua disposição. Clique AQUI e faça uma visita a seção Vídeos do site e veja a lista que estará disponível ao se tornar o nosso assinante vitalício. Clique AQUI e saiba mais detalhes sobre o kit de montagem de ribbons (deixe seus aplicativos com aspecto profissional).

Quem já me conhece, através dos fóruns de Access, sabe que sou um grande crítico ao uso de consultas aninhadas e ao uso de funções de agregação DSun(), DCount(), ...  A minha crítica diz respeito ao resultado danoso quanto ao desempenho do aplicativo.  Observe esta consulta aninhada que gera uma numeração:

SELECT (SELECT COUNT(*)+1 FROM SuaTabela As x 
WHERE x.SeuCampo < y.SeuCampo) AS Numeracao, y.*
FROM SuaTabela AS y;

O que contribui para baixar o desempenho do aplicativo é o fato de que a consulta aninhada, acessa a tabela para realizar a contagem, a cada linha da consulta principal. 

Para fugir deste prejuízo no desempenho, recentemente aprendi uma técnica muito interessante, que utiliza um pouco de programação e uma consulta espelho.  Observe a seguinte consulta:

SELECT *, fncNumerarConsulta([CampoQualquer]) As Seq FROM NomeTabela;

A função utilizada na consulta é a seguinte:

Public Function fncNumerarConsulta(varDado) As Long
Static n As Long
If IsNull(varDado) Then
   n = 0
   Exit Function
End If
n = n + 1
fncNumerarConsulta = n
End Function

Observe que a variável n é do tipo Static.  Isso significa que o valor armazenado na variável n é preservado no encerramento da função.  A cada registro exibido pela consulta, a função é acionada e a variável n é então, incrementada em 1.  Ao exibir a consulta, a numeração aparece perfeita, conforme é possível verificar nesta imagem:

Ao clicar com o mouse em qualquer uma das linhas da consulta, ocorre uma confusão na numeração, devido a variável n ter mantido o valor acumulado.  Veja:

Por isso precisamos criar um mecanismo para zerar a variável n, sempre que a consulta recomeçar a contagem.   A técnica empregada para refazer a contagem do zero é a de utilizar uma consulta espelho, passando o valor null para a função.  Acompanhe a seguinte consulta exemplo, aonde isso acontece:

SELECT *, fncNumerarConsulta(null) as Seq FROM NomeTabela WHERE 1=0;

O valor null na função,  força o código a passar pela parte vermelha, zerando assim, o valor de n.

Public Function fncNumerarConsulta(varDado) As Long
Static n As Long
If IsNull(varDado) Then
   n = 0
   Exit Function
End If
n = n + 1
fncNumerarConsulta = n
End Function

E que condição é essa de 1=0 na cláusula WHERE ?  Bom, 1=0 retorna o valor False, forçando a consulta a não exibir os dados.  Esta consulta tem um único objetivo, que é o de zerar a variável n.

Chegou a hora de juntarmos as consultas através da cláusula UNION ALL, ficando assim:

SELECT *, fncNumerarConsulta(null) As Seq FROM NomeTabela WHERE 1=0

UNION ALL

SELECT *, fncNumerarConsulta([CampoQualquer]) As Seq FROM NomeTabela;

A primeira consulta zera a variável n e a segunda consulta apresenta os dados e a devida numeração. 

Agora podemos clicar em qualquer linha da consulta, pois a contagem sempre começará do 1. 

Nota 1: A consulta União nos traz um problema que é o da impossibilidade de editar os dados.

Nota 2: Só utilize a técnica aqui apresentada se for estritamente necessário realizar a numeração na consulta.

Existem outras técnicas de numeração.  Neste meu artigo ensino como numerar, via formulário:

como-numerar-itens-em-formularios.asp

E neste meu outro artigo, mostro como é super simples numerar pelo relatório:

somar-contar-e-numerar-em-relatorios.asp

Baixe o arquivo:

Clique aqui e baixe o arquivo exemplo.

Rode a consulta qryNumerar e veja o resultado.  Abra a consulta no modo estrutura e estude como foi montada.

Bom estudo!

Os dez artigos mais visitados

MontaRibbons v.7.0 - Assistente completo para criar ribbons no Access

Vídeo - Controle personalizado de Acesso de Usuários

Vídeo - Segurança máxima, usando o OPEN

Vídeo - Aprenda sobre filtragens

Uma ajuda para quem está começando um negócio ou um projeto

Vídeo - Programação de relatórios - Parte 1

Desabilitando a faixa(ribbon) superior do Access

Adquira o kit UsandoAccess e aprenda em alta velocidade

Integrando o Access com Servidor MySQL - Introdução

Como carregar o seu menu sem que ele vá para lista de suplementos

| Home | Vídeos | Tutoriais | Dicas | Ribbons | Suporte | Downloads | Contato | Artigo |