... Pague apenas R$67,00 uma única vez e tenha acesso por 1 ano aos vídeos e arquivos exemplos do site ...

Clique aqui e obtenha mais detalhes.


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!


 

 


2 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


Envie seu comentário: