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

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: 

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:

 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 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:

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:

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:

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.