Usando Access

Numerar consultas com eficiência

Nota importante: para ter acesso aos vídeos e arquivos exemplos deste site, adquira um dos planos apresentados abaixo. Você pode comprar em até 5x no Cartão de Crédito. 

Veja como comprar e saiba mais sobre o material oferecido,  clicando aqui.

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 - Aprenda sobre filtragens

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

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

Integrando o Access com Servidor MySQL - Introdução

Desabilitando a faixa(ribbon) superior do Access

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

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

Vídeo - Criando Ribbons parte 1  - Conhecendo a estrutura Xml

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