Neste
item vamos editar a consulta criada na Lição 15. Vamos alterar
alguns critérios. Também aprenderemos a utilizar mais algumas
funcionalidades da interface do Microsoft Query.
Exercício
22 Alterar a consulta criada na Lição 15. Adicionar a
tabela Detalhes do pedido à consulta. Criar um campo que
calcula o total por item da nota fiscal, usando a seguinte fórmula:
(Quantidade*PreçoUnitário)*(1-Desconto)
Em
seguida aprenderemos a agrupar todos os itens do mesmo pedido,
para obter o total geral do pedido.
Fazer
com que sejam retornados apenas os pedidos para os países onde
a primeira letra do nome do país está na faixa da A-M.
Retornar os dados para o Excel e salvar a planilha com o nome de
Exemplo 22.xls, na pasta C:\ExcelAvançado.
1. Você já deve estar
com a planilha Exemplo 21.xls, conforme orientações da Lição
anterior.
2. Para alterar a
consulta clique em uma das células que contenha dados e
selecione o seguinte comando: Dados -> Obter dados
externos -> Editar consulta...
3. O Microsoft Query será
aberto com a consulta criada na lição anterior.
4. Para adicionar uma
nova tabela selecione o comando Tabela -> Adicionar
tabelas...
5. Será exibida a
janela Adicionar tabelas. Clique na tabela Detalhes do pedido
para seleciona-la e depois clique no botão Adicionar. Clique no
botão Fechar. A janela Adicionar tabelas será fechada, você
estará de volta ao Microsoft Query e a tabela Funcionários já
faz parte da consulta, conforme indicado na Figura 2.52. Observe
que existe um relacionamento do tipo um para vários, entre a
tabela Funcionários e Pedidos, ou seja, cada funcionário é
cadastrado uma única vez (lado um do relacionamento), porém um
mesmo funcionário pode emitir vários pedidos (lado vários do
relacionamento). Também existe um relacionamento do tipo uma
para vários entre a tabela Pedidos e Detalhes do pedido, ou
seja, cada pedido é cadastrado uma única vez (lado um do
relacionamento), porém um mesmo Pedido pode conter diversos
itens (tabela Detalhes do pedidos, lado vários do
relacionamento).
Nota:
Para maiores detalhes sobre tabelas e relacionamentos consulte o
Módulo 1 do curso de Access Básico, disponível no seguinte
endereço: http://www.juliobattisti.com.br/accbasico/accessbasico.asp.
Figura
2.52 – Tabela Detalhes do Pedido já fazendo parte da
consulta.
6. Agora queremos
acrescentar um campo que faça o cálculo do total para cada
item de cada pedido, usando a fórmula proposta anteriormente.
Observe que este campo não existe diretamente na tabela
Detalhes do Pedido, ou seja, teremos que acrescentar um campo
que será calculado (montado) a partir de outros campos disponíveis
em uma ou mais tabelas da consulta. No nosso exemplo vamos criar
um campo calculado chamado Total Geral, o qual é composto pela
seguinte fórmula:
(Quantidade*PreçoUnitário)*(1-Desconto)
7. Para criar um campo
calculado, basta digitar a expressão de cálculo diretamente na
primeira coluna em branco, após o último campo da consulta. Não
use um sinal de igual (=) no início da expressão. Por
exemplo, para criar o campo Total Geral, utilize a expressão
indicada anteriormente. Após digitar a expressão pressione
Enter e observe que você já obtém os resultados indicados na
Figura 2.53:
Figura
2.53 – Campo para o cálculo do total por item do pedido.
8. Observe que a expressão
torna-se o nome da coluna. Para atribuir um nome para a coluna,
basta dar um clique duplo na expressão de cálculo. Será
exibida a janela Editar coluna. Nesta janela preencha o campo
Cabeçalho da coluna conforme indicado na Figura 2.54:
Figura
2.54 – Definindo um cabeçalho para o campo Calculado.
9. Clique em OK para
fechar a janela Editar coluna. Observe que a coluna já aparece
com o Cabeçalho TotalGeral, ao invés da expressão de cálculo.
10. Agora vamos alterar os critérios.
Na primeira linha temos o filtro para Brasil e primeiro
trimestre de 1997. Vamos alterar este filtro para que sejam
exibidos apenas os pedidos onde o primeiro nome do país está
na faixa de A até M. Exclua os critérios da primeira linha de
critérios. Para definir este critério utilizaremos o operador
Like, descrito na Lição 14: Digite o seguinte critério:
Like
'[A-M]%'
11. Exclua os critérios da segunda
linha. Exclua a segunda coluna de critérios. Sua janela deve
estar conforme indicado na Figura 2.55:
Figura
2.55 – Critério usando o operador Like.
12. Observe que são exibidos apenas
os pedidos para os países em que a primeira letra do nome está
na faixa de A até M.
Nota:
Para maiores detalhes sobre o uso do operador Like (Como),
consulte a Lição 14.
13. Agora temos que fazer com que o
Query totalize por nota fiscal. Se você observar os resultados,
verá que aparecem vários registros com o mesmo número de
pedido. Cada registro é um item do pedido. Por exemplo, o
Pedido número 10342 apresenta dois registros, isso significa
que este pedido tem dois itens. O que queremos é que sejam
agrupados todos os itens de cada pedido e calculada a soma
destes itens, o que na prática será o total geral do pedido.
14. Para fazer este agrupamento vamos
utilizar a função Soma, na coluna Total Geral. Dê um clique
duplo em Total Geral (no nome da coluna). Será exibida a janela
Editar coluna. Nesta janela, na lista Total selecione Soma,
conforme indicado na Figura 2.56:
Figura
2.56 – Fazendo a soma dos itens do pedido.
15. Clique em OK e observe os
resultados, indicados na Figura 2.57. Cada pedido aparece uma única
vez e é exibido o total geral por pedido:
Figura
2.57 – Fazendo a soma dos itens do pedido.
16. O próximo passo é fechar o
Microsoft Query, retornando estes dados para a planilha do
Excel. Para fazer isso utilize o comando: Arquivo ->
Retornar dados ao Microsoft Excel. O Excel abre a janela
solicitando que você informe a faixa da planilha onde os dados
da consulta devem ser exibidos. Por padrão o Excel sugere a
partir da célula A1. Clique em OK. O Excel executa a consulta e
dentro de alguns instantes os dados serão exibidos na planilha,
conforme indicado na Figura 2.58:
Figura
2.58 – Resultados da consulta, retornados para o Excel.
17. Observe que é retornada somente
uma linha para cada pedido, com o total geral do pedido e
somente pedidos para os países em que a primeira letra do nome
está na faixa de A até M.
Nota:
Você pode utilizar o comando Formatar -> Células para
formatar os dados da coluna G, definindo um número de casas
decimais e a exibição ou não do símbolo da moeda (R$). Para
maiores detalhes sobre formatação de células no Excel,
consulte o Curso de Excel em 120 Lições, disponível no
seguinte endereço: http://www.juliobattisti.com.br/excel120/excel120.asp.
18. Salve a planilha com o nome de Exercício
22.xls, na pasta C:\ExcelAvançado.
19. Mantenha o Microsoft Excel
aberto, pois iremos alterar esta consulta na próxima
lição.
Todos
os direitos reservados: ® Júlio Battisti, 2002