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: PrincipalArtigosAccess Básico › Capítulo 03 : 24
Quer receber novidades e e-books gratuitos?
« Lição anterior Δ Página principal ¤ Capítulos Próxima lição »
ACCESS BÁSICO - CURSO COMPLETO GRATUITO
Autor: Júlio Battisti
Lição 054 - Módulo 03 - Consultas de Referência Cruzada: Teoria e Prática

Consultas do tipo Tabela de Referência Cruzada: 

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

Neste item 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

Os demais tipos de consultas, serão abordadas nas próximas lições.

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: 

Curso Grátis de Access Básico - Júlio Battisti
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:

  1. Linha
  2. Coluna
  3. 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 nova 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: 

  • Abra o banco de dados ExConsultas.mdb.
  • Dê um clique na opção Consultas.
  • Crie uma nova consulta, baseada na tabela 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(), já descrita anteriormente.

Na linha campo, onde está DataDoPedido, altere para a seguinte expressão: 

Ano de Ref: Ano([DataDoPedido])

Agora temos que transformar a consulta em uma consulta do tipo "Tabela de referência cruzada", pois por padrão, ao criarmos uma nova consulta está é, automaticamente, definida como sendo do tipo Seleção.  Para alterar o tipo da consulta selecione o seguinte 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 anteriores, 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 a seguir:

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:

Curso Grátis de Access Básico - Júlio Battisti
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 deste tópico.

Vamos exercitar um pouco mais as consultas do tipo Tabela de referência cruzada.

Exemplo 02: Alterar a consulta Ref Cruzada 01, para que seja exibido o número de Pedidos por mês ao invés do Ano. Com isso teremos 12 Colunas, uma para cada mês, somente para o ano de 1998.

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 paraMês([DataDoPedido])

Agora precisamos definir um filtro para que sejam contados apenas os pedidos para o ano de 1998.

Adicione o campo DataDoPedido.

Na linha Total, do campo DataDoPedido, selecione a opçãoOnde.

Deixa a linha Referência cruzada, do campo DataDoPedido, em branco. Apenas para lembrar que, quando utilizamos a linha Total, temos que utilizar a opção Onde, quando queremos definir um critério.

Na linha Critério, do campo DataDoPedido, digite a seguinte expressão: Ano([DataDoPedido])=1998. Lembrando que você também poderia utilizar o operador Between (Between #01/01/1998# E #31/12/1998#) ou os operadores de comparação (>=#01/01/1998# E <=#31/12/1998#).

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

Curso Grátis de Access Básico - Júlio Battisti
Número de Pedidos por País De Destino e pelo Mês do Ano, para 1998. 

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) e definir um critério. 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. Na prática teremos apenas cinco colunas, pois na tabela Pedidos somente temos pedidos até o mês de Maio (05) de 1998. 

Você também poderia especificar critérios. Por exemplo você poderia colocar um critério para que fossem exibidos somente os Pedidos para Brasil, Argentina 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";"Argentina";"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 Argentina e uma para a Alemanha. 

Exemplo 03: Criar uma consulta do tipo Tabela de referência cruzada, a qual exiba o valor total dos Pedidos 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 criada no Exemplo 02 e feche-a.

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

Adicione o campo NomeDaEmpresa da tabela Clientes

Adicione o campo DataDoPedido da tabela Pedidos

Crie uma coluna calculada, para o subtotal de cada ítem, utilizando a seguinte fórmula na primeira coluna em branco:

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

Transforme a consulta em uma consulta do tipo Consulta de tabela de referência cruzada. Para isto selecione o comandoConsulta -> 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 botão direito e escolhendo propriedades), como Moeda.

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

Curso Grátis de Access Básico - Júlio Battisti
Referência cruzada com o Total dos Pedidos por NomeDaEmpresa e Ano. 

Salve a consulta como Ref Cruzada - 02 e feche-a.

Vamos fazer alguns exercícios para fixar bem o conceito de consultas do tipo Tabela de referência cruzada.

EXERCÍCIOS:

01) 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.

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

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

04) Criar uma consulta do tipo Tabela de referência cruzada que nos mostre o total anual de vendas por Produto.

05) Criar uma consulta do tipo Tabela de referência cruzada que nos mostre o total anual de vendas por Categoria de produto.


LIÇÕES DESTE MÓDULO:
031. Consultas: Definições e Usos
032. Criando uma Consulta de Seleção
033. Criação de Consultas Simples
034. Consultas Baseadas em duas ou mais Tabelas
035. Definindo Critérios de Filtragem e Ordenação em Consultas
036. Criação de Consultas Simples com Critérios
037. Totalizando Valores em Consultas
038. Mais um exemplo de Totalização
039. Exercícios
040. Criando Campos Calculados em Consultas
041. Exemplo com Valores Calculados
042. Exemplo com Valores Calculados
043. Exercícios
044. Utilizando Operadores para Definir Critérios de Pesquisa
045. Exercícios
046. Utilizando Funções do Access em Consultas
047. Funções para Tratamento de Data e Hora
048. Funções Matemáticas e de Texto
049. Uso de Funções em Consultas
050. Exemplos e Exercícios
051. Exercícios
052. Consultas Parametrizadas: Teoria e Prática
053. Exercícios
054. Consultas de Referência Cruzada: Teoria e Prática
055. Outros Tipos de Consultas no Microsoft Access
056. Exemplos e Exercícios


« Anterior Δ Página principal ¤ Capítulos Próximo »

você conhece a universidade do access?

Universidade do Access - Curso Completo de Access
com tudo para você dominar o Access - do Básico ao
Avançado - até a Criação de Sistemas Profissionais
Completos - Passo a Passo - Tela a Tela

Capa da Universidade do Access

Aplica-se ao Access 2019, 2016, 2013 e 2010!

13 Cursos - 574 Vídeo-Aulas - 63:32 horas

Para todos os detalhes, acesse:

https://juliobattisti.com.br/universidade-do-access.asp

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-2021 ®

[LIVRO]: MACROS E PROGRAMAÇÃO VBA NO EXCEL 2010 - PASSO-A-PASSO

APRENDA COM JULIO BATTISTI - 1124 PÁGINAS: CLIQUE AQUI