NUNCA MAIS PASSE RAIVA POR NÃO CONSEGUIR RESOLVER UM PROBLEMA COM O EXCEL - GARANTIDO!

UNIVERSIDADE DO VBA - Domine o VBA no Excel Criando Sistemas Completos - Passo a Passo - CLIQUE AQUI

Você está em: PrincipalArtigosOffice : Consultasavancadas_p5
Quer receber novidades e e-books gratuitos?
Lição 5 - Consultas de Referência Cruzada

Até agora, estivemos trabalhando com consultas do tipo Seleção. Criávamos as consultas, especificávamos alguns parâmetros, e obtínhamos uma listagem com os registros que atendiam aos critérios especificados.

Nesta lição iremos aprender a criar um outro tipo de consulta aceita pelo Microsoft Access. Aprenderemos a criar consultas do seguinte tipo: "Consulta de tabela de referência cruzada."

O Microsoft Access nos oferece 6 tipos diferentes de consultas, conforme indicado abaixo:
  • Consulta de Seleção

  • Consulta de tabela de referência cruzada

  • Consulta criar tabela

  • Consulta atualização

  • Consulta acréscimo

  • Consulta exclusão
  • Para uma descrição dos conceitos básicos desses seis tipos de consultas, consulte o Curso de Access Básico
     
    A consulta do tipo Tabela de Referência cruzada, conforme o próprio nome sugere, é utilizada para cruzar valores de duas grandezas. Por exemplo, posso criar uma Consulta do tipo Tabela de Referência cruzada para exibir o número de pedidos (quantitativo de pedidos) por País De Destino e por ano da DataDoPedido, conforme indicado na figura abaixo:


    Informações do Quantitativo de Pedidos por PaísDeDestino e ano da DataDoPedido.
     
    Podemos observar que as informações retornadas por uma consulta deste tipo, são informações consolidadas, onde estamos cruzando informações do PaísDeDestino, com o Ano da DataDoPedido. A Informação que estamos obtendo é o Quantitativo de Pedidos por Ano e PaísDeDestino.
     
    Numa consulta do tipo Tabela de referência cruzada, temos três elementos básicos:
  • Linha

  • Coluna

  • Valor
  • Linha: No exemplo da figura anterior, o campo que forma as linhas é o campo PaísDeDestino, isso significa que para cada País De Destino o Microsoft Access retorna uma linha.

    Coluna: No exemplo da figura anterior, o campo que forma as colunas é o Ano da DataDoPedido, isso significa que para cada Ano o Microsoft Access retorna uma nova coluna

    Valor: No exemplo da figura anterior, os valores que preenchem o restante da consulta são obtidos a partir da contagem do Número de Pedidos.
     
    Para entendermos bem como funcionam as consultas do tipo Tabela de referência cruzada, vamos praticar um pouco criando algumas consultas deste tipo.
     
    Exemplo 01: Criar a consulta indicada na figura anterior, onde será exibido o Quantitativo de Pedidos por Ano da DataDoPedido e País De Destino.
     
    Para criar a consulta proposta faça o seguinte:
  • Crie uma nova consulta, baseada nas tabelas Pedidos.

  • Adicione os campos PaísDeDestino, DataDoPedido e NúmeroDoPedido, nesta ordem. Na coluna DataDoPedido, precisamos extrair apenas o ano da DataDoPedido, para isto vamos utilizar a função Ano. Na linha campo, onde está DataDoPedido, altere para o seguinte: Ano([DataDoPedido]). Observe que ao sair do campo o Microsoft Access adiciona um Expr1:. Altere o Expr01 para Ano do Pedido.

  • Agora temos que transformar a consulta em uma consulta do tipo "Tabela de referência cruzada". Para isso faça o seguinte: Selecione o comando Consulta -> Tabela de referência cruzada.

  • Observe que duas novas linhas apareceram na estrutura da nossa consulta: A linha Total (que já utilizamos anteriormente) e a linha Referência Cruzada. Utilizaremos estas duas linhas para definirmos a nossa consulta.

  • Na coluna PaísDeDestino configure da seguinte maneira:

  • Total : Agrupar Por
    Referência Cruzada: Linha
  • Com as configurações anterior, estamos informando ao Microsoft Access que haverá uma linha para cada País de Destino

  • Na coluna Ano([DataDoPedido]) configure da seguinte maneira:

  • Total : Agrupar Por
    Referência Cruzada: Coluna
  • Com estas configurações, estamos informando ao Microsoft Access que haverá uma coluna para cada Ano da DataDoPedido.
  •  
  • Na coluna NúmeroDoPedido configure da seguinte maneira:

  • Total : Contar
    Referência Cruzada: Valor
  • Com estas configurações, estamos informando ao Microsoft Access que o campo NúmeroDoPedido formará os Valores da consulta e que este valor será obtido contando o Número de Pedidos para um determinado PaísDeDestino e Ano.

  • Em resumo nossas configurações deverão ficar conforme indicado na tabela abaixo:


  • Coluna Linha Total Linha Referência Cruzada
    PaísDeDestino Agrupar Por Linha
    Ano([DataDoPedido]) Agrupar Por Coluna
    NúmeroDoPedido Contar Valor

  • Sua consulta deve estar conforme a indicada pela figura abaixo:



  • Modo Estrutura da Consulta do tipo Tabela de referência cruzada.
     
  • Salve a consulta com o nome de Ref Cruzada 01.

  • Vá para o modo Folha de Dados e observe o resultado, deve estar conforme a figura indicada no início desta lição.
  • Vamos exercitar um pouco mais as consultas do tipo Tabela de referência cruzada.
     
    Exemplo 02:  Alterar a consulta Ref Cruzada 01, para que sejam exibidos os totais de Pedido por mês ao invés do Ano. Com isso teremos 12 Colunas, uma para cada mês.
     
    Para criar a consulta proposta faça o seguinte:
  • Volte para o Modo Estrutura da Consulta Ref Cruzada 01.

  • Na linha Campo, na coluna Ano([DataDoPedido]), altera para Mês([DataDoPedido])

  • Vá para o modo Folha de Dados e observe os resultados. Você deve obter um resultado semelhante ao indicado na figura abaixo:



  • Número de Pedidos pelo País De Destino e pelo Mês do Ano.
    A única mudança que fizemos foi utilizar a função Mês ao invés da função Ano. Observe que a função mês retorna o número do mês ( 1=Janeiro, 2=Fevereiro, etc). Como são doze meses no ano, obtivemos doze colunas de valores, uma vês que a expressão Mês([DataDoPedido]), esta formando as colunas da nossa Referência Cruzada. 

    Você também poderia especificar critérios. Por exemplo você poderia colocar um critério para que fossem exibidos somente os Pedidos para Brasil, França ou Alemanha. Com isso nos teríamos somente 3 linhas no resultado final, uma vez que PaísDeDestino forma as linhas da referência cruzada e estamos exibindo somente para três países.
     
    Vamos fazer este teste, vá para o modo estrutura da consulta Ref Cruzada 01, e na linha critério do campo PaísDeDestino, digite o seguinte:
    In ("Brasil";"França";"Alemanha")
    Vá para o modo Folha de Dados e Observe os resultados, somente serão exibidas três linhas: Uma para o Brasil, outra para a França e uma para a Alemanha. Salve e feche a consulta.
       
    Exemplo 03: Criar uma consulta do tipo Tabela de referência cruzada, a qual exiba o valor total das vendas para Cada Cliente e pelo Ano da Data do Pedido. Classificar em ordem ascendente pelo NomeDaEmpresa.

    Para criar a consulta proposta faça o seguinte:
  • Salve a consulta Ref Cruzada 01 e Feche a consulta.

  • Crie uma nova consulta baseada nas tabelas: Pedidos, Clientes e Detalhes Do Pedido

  • Adicione o campo NomeDaEmpresa da tabela Clientes

  • Adicione uma coluna calculada com a expressão: Ano do pedido:Ano([DataDoPedido]).

  • Crie uma coluna calculada, para o subtotal de cada ítem, utilizando a seguinte fórmula na primeira coluna em branco:
  • Total de vendas: ([PreçoUnitário]*[Quantidade])*(1-[Desconto])
  • Transforme a consulta em uma consulta do tipo Consulta de tabela de referência cruzada. Para isto dê um clique no menu consulta e depois dê um clique na opção "Consulta de tabela de referência cruzada".

  • Configure a consulta, conforme a tabela abaixo:


  • Coluna Linha Total Linha Referência Cruzada
    NomeDaEmpresa Agrupar Por Linha
    Ano([DataDoPedido]) Agrupar Por Coluna
    ([PreçoUnitário]*[Quantidade])*(1-[Desconto]) Soma Valor

  • Formate a coluna calculada (Clicando com o direito e escolhendo propriedades), como Moeda e duas casas decimais.

  • Vá para o modo folha de dados e observe o resultado, deve estar conforme indicado na figura abaixo:



  • Referência cruzada com o Total dos Pedidos por NomeDaEmpresa e Ano da DataDoPedido.
     
  • Salve e feche a consulta.
  • Exemplo 03: Criar uma consulta do tipo Tabela de referência cruzada que nos mostre o valor Médio do Frete por País de Destino.
     
    Para criar a consulta proposta faça o seguinte:
  • Crie uma nova consulta baseada nas tabelas: Pedidos.

  • Adicione o campo PaísDeDestino.

  • Crie uma coluna para o mês do pedido, utilizando a seguinte expressão:
  • Mês_ped :Mês([DataDoPedido])
  • Adicione o campo Frete da tabela Pedidos

  • Transforme a consulta em uma consulta do tipo Consulta de tabela de referência cruzada: Dê um clique no menu Consulta e dê um clique na opção Consulta de tabela de referência cruzada

  • Configure a consulta, conforme a tabela indicada abaixo:


  • Coluna Linha Total Linha Referência Cruzada
    PaísDeDestino Agrupar Por Linha
    Mês([DataDoPedido]) Agrupar Por Coluna
    Frete Média Valor

  • Salve a Consulta com o nome de Ref Cruzada 03

  • Vá para o modo Folha de Dados, você deve obter um resultado semelhante ao indicado na figura abaixo:

  •  

    Referência cruzada, com a média dos fretes por Mês e por PaísDeDestino.  

  • Salve e feche a consulta.
  • Vamos fazer alguns exercícios para fixar bem o conceito de consultas do tipo Tabela de referência cruzada.
     
    EXERCÍCIOS:
     
    01) Alterar a consulta Ref Cruzada 03 para que exiba os totais de frete por PaísDeDestino e por mês do ano.

    02) Alterar a consulta Ref Cruzada 03 para que exibo o Quantitativo de Pedidos por PaísDeDestino e por mês do ano

    03) Criar uma consulta de referência cruzada que exiba o Total de Frete por Transportadora (campo Via da tabela Pedidos que se relaciona com o campo CódigoDaTransportadora da tabela Transportadoras, utilizar o campo NomeDaEmpresa da tabela Transportadoras) e por Mês da DataDeEntrega. Salvar esta consulta como Ref Cruzada 04

    04) Alterar a consulta anterior para que ao invés do Total de Frete, sejam exibidas as médias de frete.

    05) Alterar a consulta anterior para que seja exibida a média anual de frete por transportadora.

    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

    Quer receber novidades e e-books gratuitos?

     
     

    Contato: Telefone: (51) 3717-3796 | E-mail: webmaster@juliobattisti.com.br | Whatsapp: (51) 99627-3434

    Júlio Battisti Livros e Cursos Ltda | CNPJ: 08.916.484/0001-25 | Rua Vereador Ivo Cláudio Weigel, 537 - Universitário, Santa Cruz do Sul/RS, CEP: 96816-208

    Todos os direitos reservados, Júlio Battisti 2001-2025 ®

    LIVRO: MACROS E PROGRAMAÇÃO VBA NO EXCEL 2016 - CURSO COMPLETO E PRÁTICO

    DOMINE A PROGRAMAÇÃO VBA NO EXCEL - 878 PÁGINAS - CLIQUE AQUI