Júlio Battisti Livros e Cursosa sua sala de aula na internet

Lição 4 - Exemplos de uso da Linha Totais e Consultas Parametrizadas

Neste tópico apresentarei uma série de exemplos onde utilizaremos a linha Totais. Em seguida veremos o conceito e exemplos de Consultas Parametrizadas no Microsoft Access.
Vamos seguir a metodologia utilizada nas lições anteriores, ou seja, vamos criando consultas de exemplo e explicando os critérios e técnicas utilizados. Usaremos como base para este tópico o Banco de Dados curso_av.mdb, o qual você deve ter copiado, conforme orientações da Introdução desse tutorial.  

Inicialmente vamos construir algumas consultas para fixar entender bem a utilização da linha Totais.
 
Exemplo 01: Criar uma consulta que calcule o total de Frete por PaísDeDestino.
 
Para criar a consulta proposta faça o seguinte:

Abra o banco de dados curso_av.mdb.

Se você não estiver no Modo Estrutura, volte para o modo estrutura da Consulta 02.

Para facilitar o nosso trabalho, elimine todos os campos no modo estrutura, para isso basta marcar a coluna onde está o campo e pressionar a tecla DEL ou selecione o comando Editar -> Limpar grade.

Adicione o Campo PaísDeDestino da tabela Clientes

Adicione o Campo Frete

Clique no botão , para exibir a linha de totais ou selecione o comando Exibir -> Totais.

No campo PaísDeDestino, na linha Total, escolha "Agrupar Por"

No campo Frete, na linha Total, escolha "Soma"

Vá para o modo Folha de Dados e observe os resultados, conforme indicado na figura abaixo:

Consulta que calcula o Total de Frete por País.

O Agrupar Por pede ao Microsoft Access que agrupo todos os pedidos para um Mesmo PaísDeDestino, e o Soma pede para que o Microsoft Access Some o valor do Frete para todos os Pedidos de um Mesmo País.

É importante salientar que para termos acesso a estas opções de Agrupar Por e Soma é necessário que a linha Total esteja sendo exibida. Poderíamos, ao invés de calcular a Soma, calcular a média dos Pedidos. Para isto basta voltar ao modo estrutura e trocar Soma por Média, na linha Total do campo Frete
 
Exemplo 02: Criar uma consulta que conte o número de Pedidos por País de Destino
 
Para criar a consulta proposta faça o seguinte:

Se você não estiver no Modo Estrutura, volte para o modo estrutura da Consulta 02.

No campo PaísDeDestino, na linha Total, escolha "Agrupar Por", se já estiver Agrupar Por, não altere.

Exclua o campo Frete e adicione o campo NúmeroDoPedido.

No campo NúmeroDoPedido, na linha Total, selecione "Contar"

Vá para o modo Folha de Dados e observe os resultados, conforme indicado na figura abaixo:

Consulta que conta o Número de Pedidos por País de Destino

Observe que para Contar o Número de Pedidos, bastou alterarmos, no campo NúmeroDoPedido, na linha Total de Somar para Contar. Poderíamos da Mesma maneira utilizar qualquer uma das outras funções.
 
Você também poderia adicionar um critério para que somente fossem retornados os registros para países com mais de 50 Pedidos. Para isto basta colocar o seguinte critério no campo Frete: > 50
 
Agora vamos criar algumas consultas que envolvem tanto as totalizações quanto cálculos na mesma consulta. Criaremos uma consulta que irá calcular o total de vendas por País de Destino. Para isto iremos criar uma consulta que calcula o subtotal para cada ítem de cada pedido ( [Quantidade]*[PreçoUnitário]*(1-[Desconto]) e depois totalizar por Pedido. Depois construiremos uma segunda consulta que ira totalizar estes subtotais por País de Destino. Com isso vamos aprender um novo conceito relativo a consultas: O Microsoft Access permite que seja criada uma consulta, baseada em outra consulta. 

Exemplo 03: Crie uma consulta baseada na tabela Detalhes Do Pedido, a qual calcule o subtotal para cada ítem, e some todos os subtotais para obter o total por pedido, utilizando a seguinte fórmula, a qual leva em consideração o desconto:

SubTotal: ([PreçoUnitário]*[Quantidade])*(1-[Desconto])

Para criar a consulta proposta faça o seguinte:

Se você ainda estiver com a Consulta 02 aberta, feche-a.

Crie uma nova consulta baseada na tabela Detalhes Do Pedido.

Adicione o campo NúmeroDoPedido

Na segunda coluna, na linha campo digite a seguinte fórmula:

SubTotal: ([PreçoUnitário]*[Quantidade])*(1-[Desconto])

Clique no botão , para exibir a linha de totais.

Na linha Total, no campo NúmeroDoPedido, escolha Agrupar Por

Na linha Total, no campo calculado para o subtotal, escolha Soma. Com isso estamos pedindo para o Microsoft Access Agrupar Todos os ítens do mesmo pedido e efetuar a soma dos mesmos.

Vá para o modo Folha de Dados e observe os resultados, conforme indicado na figura abaixo:

 

Consulta calculando o total para cada Pedido.

Na criação desta consulta utilizamos duas técnicas já conhecidas. Criamos um campo calculado, utilizando a fórmula indicada anteriormente. Apenas para lembrar, o que vem antes dos dois pontos é apenas o Nome do Campo calculado, depois dos dois pontos é que vem a fórmula de Cálculo. Também utilizamos a linha de Total para Agrupar todos os ítens do mesmo pedido e pedir para que o Microsoft Access fizesse a soma destes ítens.

Salve a consulta com o Nome de "Calcula Subtotais" e feche-a.

Exemplo 04: Criar uma nova consulta, baseada na Consulta "Calcula Subtotais" e na tabela Pedidos. Esta consulta ira fazer o somatório de todos os pedidos, agrupados por País De Destino.
           
Para criar a consulta proposta faça o seguinte:

Crie uma nova consulta baseada na Consulta Calcula Subtotais e na Tabela Pedidos

Uma vez no modo estrutura, você precisará criar um relacionamento entre a consulta Calcula Totais e a tabela Pedidos. Este relacionamento se dará através do campo NúmeroDoPedido, comum a ambos. Para criar este relacionamento, basta arrastar o campo NúmeroDoPedido da tabela Pedidos sobre o campo NúmeroDoPedido da consulta "Calcula Subtotais". Aqui estamos aprendendo um novo conceito, ou seja, podemos criar um relacionamento diretamente dentro de uma consulta. Este relacionamento somente existe e tem efeito dentro da consulta.

Adicione o campo PaísDeDestino da Tabela Pedidos e o campo Subtotal da Consulta "Calcula Subtotais". Exiba a linha Total. No campo PaísDeDestino, na linha Total, escolha Agrupar Por e no campo Subtotal, na linha Total escolha Soma.

Lembre de formatar a coluna Subtotal para Moeda com duas casas decimais.

Vá para o modo Folha de Dados e observe os resultados, conforme indicado na figura abaixo:

Total de vendas por País de Destino.

Observe que como rótulo da coluna o Microsoft Access coloca SomaDeSubtotal, isto é , ao nome do campo (SubTotal), é adicionada a palavra SomaDe. Para alterar este rótulo, basta acessar as propriedades da coluna (no modo estrutura) e no campo Legenda, digitar o texto desejado. Por exemplo, volte para o Modo Estrutura, clique com o direito sobre a coluna dos subtotais, clique em Propriedades. Na janela que surge , digite Total por País, no campo Legenda, dê um clique no x para fechar a janela de propriedades. Volte para o modo folha de dados e observe que o título da coluna já deve ter se alterado para Total por País.
 
Agora que já conhecemos melhor a criação e utilização de Critérios avançados, operadores e funções, vamos fazer alguns exercícios para fixar os conceitos apresentados.
 
CRIAR AS SEGUINTES CONSULTAS:
 
01) Criar uma consulta que lista o total de vendas por categoria e por produto dentro de cada categoria, conforme indicado na figura abaixo:



02) Criar uma consulta que lista o Número de Pedidos por trimestre, para o ano de 1995, conforme indicado pela figura abaixo:



03) Alterar a consulta do ítem anterior, para que ao invés do número de pedidos por trimestre, seja exibido o número de pedidos por mês para o ano de 1995.
 
04) Criar uma consulta que exiba o total de vendas por Vendedor, para o ano de 1995.
 
05) Criar uma consulta que exiba todos os Pedidos (NúmeroDoPedido, DataDoPedido, DataDeEntrega, Frete e PaísDeDestino) para o ano de 1995 e que o número de dias entre a DataDoPedido e a DataDeEntrega seja maior do que 7.
 
06) Alterar a consulta do ítem anterior de tal forma que somente sejam exibidos os pedidos para Clientes cujo NomeDaEmpresa tenha a primeira letra no intervalo de A até P e cujo País de Destino não seja Brasil ou Argentina. 
 
Consultas Parametrizadas com Critérios:
 
Até este momento, trabalhamos com consultas nas quais colocávamos, diretamente, no modo estrutura, os critérios desejados.
 
Podemos criar consultas mais genéricas, as quais ao serem executadas nos solicitam que seja digitado um determinado valor para um ou mais critérios. Por exemplo, ao invés de colocarmos Brasil como critério para o campo PaísDeDestino, podemos fazer com que o Microsoft Access solicite um valor para o critério, cada vez que a consulta for executada. Isso torna a consulta bem mais ágil, pois cada vez que executarmos a consulta, podemos digitar um critério diferente, de tal maneira que não precisamos entrar no modo estrutura para digitar um critério diferente.
 
Este tipo de consulta é chamada de "Consulta Parametrizada". Para criarmos uma consulta parametrizada, ao invés de digitarmos diretamente um valor para o critério, digitamos uma expressão do seguinte tipo:

[Qualquer texto entre colchetes]

Como no exemplo:

[Digite o Valor para o País de Destino :]

Por exemplo, na linha critério, do campo PaísDeDestino, podemos digitar a expressão indicada anteriormente. O texto dentro dos colchetes é simplesmente um texto explicativo, que será exibido quando a consulta for executada e o valor do Parâmetro for solicitado. Este texto serve como orientação para os usuários que forem utilizar a consulta.
 
Podemos utilizar expressões deste tipo em mais de um campo em uma mesma consulta. Por exemplo se você quiser que o Microsoft Access solicite o nome do PaísDeDestino ,uma data inicial e uma data final, de tal forma que somente sejam retornados os Pedidos para o país digitado e dentro do período especificado, você pode fazer isso sem maiores problemas.
 
Existem algumas limitações com a utilização de parâmetros. Por exemplo, se você utilizar uma expressão para solicitar que o usuário digite um CPF. Ao digitar o CPF, o usuário terá que digitar os pontos e o traço da formatação do CPF, uma vez que não podemos colocar uma máscara de entrada para o parâmetro, de tal forma que o usuário somente precise digitar os números.
 
De uma forma geral a utilização de consultas parametrizadas aumenta em muito a flexibilidade das consultas no Microsoft Access, pois não precisamos criar uma consulta diferente para cada critério que vamos utilizar. Ao invés disso podemos criar uma única consulta parametrizada, cada vez que executarmos a consulta, o Microsoft Access solicita o valor para o parâmetro.
 
Vamos usar a mesma abordagem do tópico anterior, isto é, vamos criar uma série de consultas que salientam os diversos aspectos da utilização de consultas parametrizadas. No final deste tópico serão propostos alguns exercícios para fixação dos conceitos apresentados.
 
Nunca é demais salientar que maiores informações sempre podem ser obtidas na Ajuda do Microsoft Access, o qual apresenta uma referência completa de todas as funções e operadores, bem como diversos exemplos sobre consultas.
  
Exemplo 01: Criar uma consulta que liste o NúmeroDoPedido, DataDoPedido, Frete e PaísDeDestino da tabela Pedidos. Fazer com que ao ser executada a consulte solicite que seja digitado um nome de País e que sejam retornados somente os Pedidos para o País digitado.
 
Para criar a consulta proposta faça o seguinte:

Abra o arquivo curso_av.mdb.

Crie uma nova consulta baseada nas tabelas Pedidos

Adicione os campos NúmeroDoPedido, DataDoPedido, Frete e PaísDeDestino da tabela Pedidos.

Na linha Critério, do campo PaísDeDestino, digite a seguinte expressão:

[Digite o País Desejado:]

Salve a consulta com o nome de Consulta Parametrizada 01

Vá para o modo Folha de Dados, o Microsoft Access abrirá uma janela solicitando que você digito o nome do País desejado, conforme indicado abaixo:

Consulta Parametrizada, solicitando que seja digitado o valor do Parâmetro.
 

Digite Brasil e dê um clique em OK, você verá os resultados, conforme indicado na figura abaixo:

 

Consulta exibindo somente os pedidos para o Brasil.

Cada vez que você executar esta consulta, o Microsoft Access 97 irá solicitar que você digite um valor para o campo PaísDeDestino, valor este que será utilizado como critério de seleção.

Exemplo 02: Altere a consulta criada anteriormente, para que além de pedir que seja digitado o País, a mesma peça para que seja digitada uma Data Inicial e uma Data Final e exiba somente os Pedidos para o País digitado e dentro do período especificado.

Para criar a consulta proposta faça o seguinte:

Volte para o Modo Estrutura da Consulta Parametrizada 01

Na linha Critério do campo DataDoPedido, digite o seguinte:

Entre [Data Inicial:] E [Data Final:]

Com a expressão acima, ao executarmos a consulta, o Microsoft Access solicitará que sejam digitadas duas datas. Ao digitar a data o usuário deve digitar inclusive as Barras Separadoras, por exemplo: 01/01/1995. O Microsoft Access também solicitará que seja digitado o nome de um País (critério definido no exemplo anterior).

No final será exibida uma listagem com todos os Pedidos para o País digitado e dentro do período digitado.

Vá para o Modo Folha de Dados. O Microsoft Access começa a solicitar que os parâmetros sejam digitados.

Na janela abaixo vemos o Microsoft Access solicitando que seja digitada a data inicial. Digite 01/01/1995 e dê um clique em OK

Digitando a Data Inicial.

Na janela abaixo vemos o Microsoft Access solicitando que seja digitada a data Final. Digite 31/12/1995 e dê um clique em OK.

Digitando a Data Final.
 

O Microsoft Access solicita que seja digitado o nome do País. Digite Brasil e dê um clique em OK.

Após Clicar em OK, o Microsoft Access irá para o Modo Folha de Dados, exibindo os registros que foram selecionados de acordo com os critérios digitados (PaísDeDestino=Brasil e Período de 01/01/1995 até 31/12/1995). Na janela abaixo podemos ver os resultados exibidos pelo Microsoft Access.

Pedidos para o Brasil durante o ano de 1995.

Observe a utilização do Operador Entre em conjunto com os parâmetros, na seguinte expressão: Entre [Data Inicial:] E [Data Final:]

Quando executamos a consulta e digitamos os valores para a Data Inicial (01/01/1995) e para a Data Final (31/12/1995), o Microsoft Access 97 coloca estes valores na expressão e o critério se transforma em Entre #01/01/1995/# E #31/12/1995#

O Microsoft Access substitui os parâmetros pelos valores digitados. O sinal de # é adicionado pelo Microsoft Access, pois se tratam de campos do tipo Data e valor do tipo data deve vir delimitado, para efeitos de critérios de pesquisa em consultas, pelos caracteres #, no início e no final da data.

Exemplo 03: Altere a consulta criada no ítem anterior, de tal forma que o usuário possa digitar * quando for solicitado o nome do país, para que sejam exibidos os pedidos para todos os países, dentro do período especificado.
 
Para criar a consulta proposta faça o seguinte:

Volte para o Modo Estrutura da Consulta Parametrizada 01.

Altere a linha Critério, no campo PaísDeDestino, e coloque o seguinte critério:

Como '*'+[Digite o País Desejado (* para todos):]+'*'

Vá para o modo Folha de Dados, ao ser solicitada a Data Inicial, digite 01/01/1995. Ao ser solicitada a Data Final, digite 31/12/1995. Ao ser solicitado o nome do país de destino, digite *, conforme indicado abaixo:

Digite * no nome do País, para que sejam exibidos pedidos para todos os Países
 

Ao clicar em OK, será exibida uma listagem com todos os Pedidos, para todos os paises, dentro do período especificado.

Observe que alteramos o parâmetro no País De Destino em dois pequenos detalhes. Adicionamos um asterisco antes e um depois do parâmetro que vem dentro dos colchetes. O asterisco deve ser colocado entre apóstrofes. Isso para que o Microsoft Access aceite o * (um caractere coringa) como sendo todos os países de destino. Também alteramos o texto dentro dos colchetes ( Digite o País Desejado (* para todos):), apenas para orientar o usuário sobre como proceder, quando da execução da consulta.
 
O Principal benefício da utilização de parâmetros, é que os mesmos tornam nossas consultas mais flexíveis. Não precisamos, cada vez que a consulta for executada, entrar no modo estrutura e alterar o parâmetro, uma vez que o Microsoft Access solicita um valor para o Parâmetro (Ou parâmetros), cada vez que a consulta for executada.

Além disso, o usuário final não deve ter acesso a estrutura das consultas, tabelas, formulários, etc. Para que possamos impedir que o usuário acesse a estrutura de uma consulta, temos que possibilitar que ele execute as consultas com diferentes parâmetros, conforme for necessário. Isso é possibilitado pela utilização das consultas parametrizadas.
 
EXERCÍCIOS:
 
01)  Criar uma consulta que solicita que seja digitado um valor para o frete e exiba somente os pedidos cujo valor do frete seja menor ou igual ao valor digitado. A consulta deve exibir os campos: NúmeroDoPedido, NomeDaEmpresa, PaísDeDestino e Frete. Classificar a consulta, em ordem ascendente, pelo valor do frete.
 
02) Alterar a consulta do ítem anterior, para que além do Frete, a consulta solicite o nome do País de Destino. Salvar a consulta com o nome de Parametrizada 02. 
 
03) Criar uma consulta que calcule o total por pedido. Colocar um parâmetro que solicite que seja digitado um valor para o Total do Pedido e somente exiba os pedidos cujo total seja maior ou igual ao valor digitado. Salvar com o nome do Totais por Pedido com Parâmetro.
 
04) Criar uma consulta com os campos NúmeroDoPedido, NomeDaEmpresa, Data do Pedido, PaísDeDestino e Frete. Fazer com que, ao ser executada, a consulta solicite uma Data Inicial, uma Data Final e um valor de Frete. A consulta deverá exibir somente os pedidos para o Período especificado e para valores de Frete maiores do que o digitado. Salve a consulta com o nome de Parametrizada 03.

OBSERVAÇÕES SOBRE O TUTORIAL:

O tutorial é composto de teoria e exemplos práticos, passo-a-passo. Para acompanhar todas as lições desse tutorial, você deve copiar o arquivo curso_av.mdb, o qual está disponível para Download, no formato compactado (.zip), no endereço a seguir:

CLIQUE AQUI PARA COPIAR O ARQUIVO NECESSÁRIO PARA ACOMPANHAR OS EXEMPLOS DO TUTORIAL

Para acompanhar os exemplos desse tutorial você já deve conhecer os conceitos básicos de criação de consultas no Microsoft Access. Para detalhes sobre a criação de consultas básicas no Microsoft Access, consulte o Curso Básico de Access. Um bom estudo a todos. 

Em caso de dúvidas, sobre o conteúdo e os exemplos do tutorial, ou para enviar sugestões sobre novos tópicos a serem incluídos, entre em contato através do e-mail: webmaster@juliobattisti.com.br. Somente serão respondidas dúvidas referentes às questões e tópicos contidos no tutorial. Não serão respondidas dúvidas sobre tópicos gerais do Microsoft Access ou sobre sistemas em desenvolvimento.

Outras partes do Artigo
Parte 1	Criação de campos calculados em consultas
Parte 2	Utilização de Critérios Avançados e Operadores
Parte 3	Utilização de Critérios Avançados e Operadores
Parte 4	Exemplos de uso da Linha Totais e Consultas Parametrizadas
Parte 5	Consultas de Referência Cruzada
Parte 6	Consultas de Alteração, Exclusão e Acréscimo de Dados

Clique aqui para voltar ao início da página