Facilitar a leitura dos dados resumidos através de uma consulta de tabela de referência cruzada

Ao usar o modo Design para criar sua consulta de tabela de referência cruzada, você pode usar tantas fontes de registro (tabelas e consultas) quanto desejar. No entanto, você pode manter o design simples primeiro criando uma consulta de seleção que retorna todos os dados que você deseja e, em seguida, usar essa consulta como a única fonte de registro da sua consulta de tabela de referência cruzada. Para obter mais informações sobre como criar uma consulta de seleção, confira a seção Confira também.

Quando você cria uma consulta de tabela de referência cruzada no modo Design, usa as linhas Total e Tabela de Referência Cruzada na grade de design para especificar quais valores do campo se tornarão títulos de colunas, quais valores dos campos se tornarão títulos de linha e quais valores do campo se tornarão uma soma, média, contagem ou qualquer outro resultado de cálculo.

1. As configurações dessas linhas determinam se o campo é um título de linha, título de coluna ou valor resumido.

2. Esta configuração exibe os valores do campo como títulos de linha.

3. Esta configuração exibe os valores do campo como títulos de colunas.

4. Estas configurações produzem os valores resumidos.

Criar a consulta

1. Na guia Criar, no grupo Consultas, clique em Design da Consulta.

   

2. Na caixa de diálogo Mostrar Tabela, clique duas vezes em cada tabela ou consulta a ser utilizada como fonte de registro.

   Se usar mais de uma fonte de registro, verifique se as tabelas ou consultas estão associadas nos campos que elas têm em comum. Para saber mais sobre como associar tabelas e consultas, confira a seção Consulte também.

3. Feche a caixa de diálogo Mostrar Tabela.

4. Na guia Design, no grupo Tipo de Consulta, clique em Tabela de Referência Cruzada.

5. Na janela de design de consulta, clique duas vezes em cada campo a ser usado como fonte dos títulos de linha. Você pode selecionar no máximo três campos como títulos de linha.

6. Na grade de design de consulta, na linha Tabela de Referência Cruzada de cada campo de título de linha, selecione Linha.

   Você pode digitar uma expressão na linha Critérios para limitar os resultados desse campo. Você também pode usar a linha Classificar para especificar uma ordem de classificação para um campo.

7. Na janela de design de consulta, clique duas vezes no campo a ser usado como fonte dos títulos de colunas. Você pode selecionar apenas um campo como títulos de colunas.

8. Na grade de design de consulta, na linha Tabela de Referência Cruzada do campo de título de coluna, selecione Título de Colunas.

   Você pode digitar uma expressão na linha Critérios para limitar os resultados do campo de título de coluna. No entanto, o uso de uma expressão de critério com o campo de título de coluna não limita o número de colunas retornadas pela consulta de tabela de referência cruzada. Em vez disso, ele limita as colunas que conterão dados. Por exemplo, suponhamos que você tenha um campo de título de coluna que tenha três valores possíveis: vermelho, verde e azul. Se você aplicar o critério ='azul' ao campo de título de coluna, a tabela de referência cruzada ainda exibirá uma coluna para vermelho e uma coluna para verde, mas somente a coluna para azul conterá dados.

   Se você deseja limitar os valores exibidos como títulos de colunas, especifique uma lista de valores fixos usando a propriedade Título de Colunas da consulta. Para obter mais informações, confira a próxima seção.

9. Na janela de design de consulta, clique duas vezes no campo a ser usado para calcular valores resumidos. Você pode selecionar somente um campo para valores resumidos.

10. Na grade de design de consulta, na linha Total do campo de valores resumidos, selecione uma função de agregação para calcular os valores.

11. Na linha Tabela de Referência Cruzada do campo de valores resumidos, selecione Valor.

    Você não pode especificar critérios para um campo de valores resumidos ou classificá-lo.

12. Na guia Design, no grupo Resultados, clique em Executar.

Especificar valores fixos para títulos de colunas

Se você deseja especificar valores fixos para títulos de colunas, defina a propriedade Título de Colunas da consulta.

1. Abra a consulta de tabela de referência cruzada no modo Design.

2. Se a folha de propriedades não estiver visível, pressione F4 para exibi-la.

3. Na folha de propriedades, imediatamente acima da guia Geral, verifique se Tipo de seleção é Propriedades da Consulta. Se não for, clique em um ponto vazio no espaço acima da grade de design de consulta.

4. Na folha de propriedades, na guia Geral, na propriedade Títulos de Colunas, digite uma lista de valores separada por vírgulas a ser usada como títulos de colunas.

   Alguns caracteres (como a maioria dos sinais de pontuação) não são permitidos nos títulos de colunas. Se você usar esses caracteres na lista de valores, o Access os substituirá por um sublinhado (_).

Sintaxe SQL para uma consulta de tabela de referência cruzada

Uma consulta de tabela de referência cruzada é expressa em SQL como uma instrução TRANSFORM. Uma instrução TRANSFORM tem a seguinte sintaxe:

Transform aggfunction
selectstatement
PIVOT pivotfield [IN (value1[, value2[, ...]])]

A instrução TRANSFORM tem as seguintes partes:

Parte	Descrição
aggfunction	Uma função de agregação de SQL que funciona com os dados selecionados.
selectstatement	Uma instrução SELECT.
pivotfield	O campo ou expressão que você deseja usar para criar títulos de coluna no conjunto de resultados da consulta.
valor1, valor2	Valores fixos usados para criar títulos de coluna.

O modo SQL não limita o número de tabelas ou consultas que você pode usar como fontes de registro para uma consulta de tabela de referência cruzada. No entanto, você pode ajudar a manter o design simples criando uma consulta de seleção que retorna todos os dados que você deseja usar em sua consulta de tabela de referência cruzada e, em seguida, usar essa consulta de seleção como fonte de registro. Para obter mais informações sobre como criar uma consulta de seleção, confira a seção Confira também.

1. Na guia Criar, no grupo Outros, clique em Design da Consulta.

2. Feche a caixa de diálogo Mostrar Tabela.

3. Na guia Design, no grupo Modos de Exibição, clique em Exibir e em Modo SQL.

4. Na guia Objeto SQL, digite ou cole o seguinte SQL:

   TRANSFORM 
   SELECT 
   FROM 
   GROUP BY 
   PIVOT 
   ;
   

5. Na primeira linha, depois de TRANSFORM, digite uma expressão para usar para calcular os valores de resumo; por exemplo, Soma ([Montante]).

   Se você estiver usando mais de uma tabela ou consulta como fonte de registro, inclua a tabela ou o nome da consulta como parte de cada nome de campo; por exemplo, Sum([Despesa].[Montante]).

6. Na segunda linha, depois de SELECT, digite uma lista de campos ou expressões de campo que você deseja usar para títulos de linha. Separe os itens da lista usando vírgulas; por exemplo, [Orçamento].[Dept_ID], [Despesa].[Tipo].

7. Na terceira linha, depois de FROM, digite uma lista das tabelas ou consultas que você está usando como fontes de registro; por exemplo, Orçamento, Despesa.

8. Na quarta linha, depois de GROUP BY, digite a mesma lista de campos que você usou na cláusula SELECT, na etapa 6.

9. Na quinta linha após PIVOT, digite um nome de campo ou expressão que você deseja usar para títulos de coluna; por exemplo, PIVOT [Orçamento]. [Ano].

Às vezes, ao invés de usar cada valor de um campo para títulos de linha ou coluna, você deseja agrupar os valores de um campo em intervalos e, em seguida, usar esses intervalos para títulos de linha ou coluna. Por exemplo, suponha que você use um campo "Idade" para títulos de colunas. Em vez de usar uma coluna para cada idade, você pode preferir usar colunas que representem faixas etárias.

É possível usar a Função IIF em uma expressão para criar intervalos que serão utilizados como títulos de linha ou coluna.

1. Abra a consulta de tabela de referência cruzada no modo Design.

2. Na grade de design de consulta, na linha Campo, clique com o botão direito do mouse em uma coluna vazia e, em seguida, clique em Zoom no menu de atalho.

3. Na caixa de Zoom, digite o alias do campo, seguido de dois pontos (:).

4. Digite IIf().

5. Dentro dos parênteses após IIf, digite uma expressão de comparação que defina o primeiro intervalo de valores de campo.

   Por exemplo, suponhamos que você esteja criando intervalos para o campo Idade e deseje que cada intervalo seja de vinte anos. A expressão de comparação do primeiro intervalo é [Idade]<21.

6. Digite uma vírgula após a expressão de comparação e, em seguida, digite um nome para o intervalo, separado por aspas. O nome fornecido estará no título da tabela de referência cruzada dos valores que compõem o intervalo.

   Por exemplo, após [Idade]<21, digite uma vírgula e, em seguida, digite "0-20 anos".

7. Digite uma vírgula após o nome do intervalo (fora das aspas) e execute um dos seguintes procedimentos:

   • Para criar outro intervalo, digite IIf() e repita as etapas 5, 6 e 7.

   • No último intervalo, digite apenas um nome para o intervalo.

     Por exemplo, uma expressão IIf aninhada completa que separa um campo Idade em intervalos de vinte anos pode ter esta aparência (as quebras de linha foram adicionadas para fins de legibilidade):

     IIf([Age]<21,"0-20 years",
      IIf([Age]<41,"21-40 years",
      IIf([Age]<61,"41-60 years",
      IIf([Age]<81,"61-80 years", "80+ years"))))
     

     Observação: Quando o Access avalia a expressão, ele interrompe a avaliação assim que uma das instruções IIf retorna true. Não é preciso especificar a extremidade inferior de cada intervalo, pois qualquer valor que esteja abaixo da extremidade inferior de um determinado intervalo já teria sido avaliado como true.

8. Na grade de design da consulta, na linha Total, selecione Agrupar por.

9. Na linha Tabela de Referência Cruzada, especifique se os intervalos devem ser usados como títulos de linha ou títulos de colunas. Lembre-se de que você pode especificar de um a três títulos de linha e um título de coluna.

É recomendável que a consulta de tabela de referência cruzada solicite uma entrada quando for executada. Por exemplo, suponhamos que você esteja usando vários títulos de linha e um deles seja País/Região. Em vez de exibir sempre os dados de cada país ou região, convém que a consulta solicite um nome e exiba os dados de acordo com a entrada do usuário.

Você pode adicionar um prompt de parâmetro a qualquer campo de título de linha.

1. Abra a consulta de tabela de referência cruzada no modo Design.

2. Na linha Critérios do campo de título de linha para o qual você deseja solicitar a entrada do usuário, digite o texto da pergunta separado por colchetes. O texto da pergunta aparecerá como um prompt quando a consulta for executada.

   Por exemplo, se você digitar [Que país ou região?] na linha Critérios, quando a consulta for executada, uma caixa de diálogo aparecerá com a pergunta "Que país ou região?", uma caixa de entrada e um botão OK.

   Dica: Se você deseja que o parâmetro seja flexível, concatene a expressão com curingas, usando o operador Como. Por exemplo, em vez de usar [Que país ou região?] como critério, você poderá usar Como [Que país ou região?]&"*" para fazer o parâmetro corresponder a um intervalo de entrada maior. O uso de Como não alterará a aparência do prompt de parâmetro.

3. Na guia Design, no grupo Mostrar/Ocultar, clique em Parâmetros.

4. Na caixa de diálogo Parâmetros de Consulta, na coluna Parâmetros, digite o mesmo prompt de parâmetro usado na linha Critérios. Inclua os colchetes, mas não inclua nenhum curinga concatenado ou o operador Como.

5. Na coluna Tipo de Dados, selecione o tipo de dados do parâmetro. Ele deve corresponder ao tipo de dados do campo de título de linha.

Se o campo utilizado para calcular valores resumidos na consulta de tabela de referência cruzada contiver valores nulos, estes serão ignorados por qualquer função de agregação que você usar. Em algumas funções agregadas, os resultados podem ser afetados. Por exemplo, para calcular uma média, adicione todos os valores e divida o resultado pelo número de valores. Mas, se o campo contiver valores nulos, estes não serão contabilizados como parte do número de valores.

Em alguns casos, talvez você prefira substituir os valores nulos por zeros, a fim de que os valores sejam contabilizados durante os cálculos agregados. Você pode usar a função Nz para substituir valores nulos por zeros.

Sintaxe Nz

Nz ( variant [, valueifnull ] )

A sintaxe da função Nz tem os seguintes argumentos:

Argumento	Descrição
variant	Obrigatório. Uma variável de tipo de dados Variant.
valueifnull	Opcional (a menos que seja usado em uma consulta). Um Variant que fornece um valor a ser retornado se o argumento variant for Nulo. Esse argumento permite que você retorne um valor diferente de zero ou uma cadeia de comprimento zero. Observação: Se você usar a função Nz em uma expressão de uma consulta sem usar o argumento valueifnull, os resultados serão uma cadeia de comprimento zero nos campos que contiverem valores nulos.

1. Com a consulta aberta no modo Design, na grade de design de consulta, clique com o botão direito do mouse no campo Valor.

2. No menu de atalho, clique em Zoom.

3. Na caixa de Zoom, coloque o nome do campo ou a expressão entre parênteses e digite Nz na frente dos parênteses.

4. Digite 0 dentro do parêntese direito.

   Por exemplo, se você usou Nz com um campo chamado "Horas Perdidas" para transformar valores nulos em zeros, sua expressão concluída teria esta aparência:

Nz([Hours Lost], 0)

• Mantenha a simplicidade    As consultas de tabela de referência cruzada podem se tornar difíceis de ler à medida que o número de combinações de linhas aumentar. Não use mais títulos de linha do que o necessário.

• É recomendável criar a tabela de referência cruzada em etapas    Não se limite apenas às tabelas. Geralmente, você pode começar criando uma consulta de totais e usando essa consulta como fonte de registro para uma consulta de tabela de referência cruzada.

• Escolha o campo de título de coluna cuidadosamente    As folhas de dados de tabela de referência cruzada tendem a ser mais fáceis de ler quando o número de títulos de colunas permanece relativamente pequeno. Depois que você tiver identificado os campos que deseja usar como títulos, considere o uso do campo que tem menos valores distintos para produzir títulos de colunas. Por exemplo, se a consulta calcular um valor por idade e sexo, é recomendável usar os dados de sexo nos títulos de colunas, em vez da idade, pois os dados de sexo geralmente têm menos valores possíveis do que idade.

• Usar uma subconsulta em uma cláusula WHERE    Você pode usar uma subconsulta como parte de uma cláusula WHERE em uma consulta de tabela de referência cruzada.

Início da Página