... Assinatura do site por 3 anos + Kit MontaRibbons + 3 Livros em PDF + Diversas Revistas (pdf) de brinde, por apenas R$182,00
(
podendo parcelar em até 10 vezes no cartão de crédito)...

Clique aqui e obtenha mais detalhes do nosso kit completo e de como comprar.


Numerar consultas com eficiência

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:

Usando Access - Numerar Consulta

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:

Usando Access - Numerar consulta erro

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?id=1

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

somar-contar-e-numerar-em-relatorios.asp?id=1#inicio

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!


 

 


3 comentário(s)

AMILTON PIRES DE OLIVEIRA JUNIOR   17/02/2017 05:49:01

Bom dia Avelino , realmente show, facilita muito quando precisa se fazer por exemplo fluxo de caixa
parabéns

valter b gusmao   21/02/2017 03:04:47

bom dia
Valeu professor muito bom

Wagner Bonelli   14/08/2018 10:49:12

OI Avelino, boa tarde!
Realmente muito bom esta abordagem.
Queria utilizá-la em um trabalho que realizo, mas não consegui.
Queria, antes de numerar a consulta, ordená-la por tres campos consecutivos: UF, Cidade, CEP. Após esta ordenação, fazer a numeração.
Isso se deve a impressão de carta e boleto para uma ong, que precisa ser numerado (carta e boleto mesmo número) e sequencial, na hora da impressão, mas ordenados conforme acima, por exigencia na postagem no correio.
No momento o que faço é importar a tabela gerada na consulta com todos os dados do boleto, inclusive linha digitável e código de barras, para o excell e lá ordeno pelas colunas que quero, e numero uma outra.
Obrigado!
Wagner


Envie seu comentário: