EAD657 I 2015 Access Consultas Parte 1

Prévia do material em texto

EAD657 – Tecnologia de Informação 
 
 
 
 
 
 
 
Consultas 
MS Access 2007 
(Parte 1) 
 
 
 
 
 
 
 
 
 
 
 
Prof. Hiroo Takaoka 
takaoka@usp.br 
Atividade 
 
Com base no banco de dados Banco de Dados Controle 
Acadêmico, faça HOJE em Aula os exercícios 1 a 16. Entregar pelo 
Erudito. Pode ser Dupla ou trio. 
Carregar o banco de dados Banco de Dados Controle Acadêmico 
do Erudito na área de trabalho do seu microcomputador e, em 
seguida, abri-lo. Não abrir o banco de dados diretamente no Erudito, 
pois por estar no modo compartilhado, você não vai conseguir 
atualizá-lo. 
Introdução 
A criação de uma nova consulta é feita a partir da página de MS Office Access. 
 
 
 
Página de MS Office Access 
Para abrir a janela de criação da consulta: 
 Selecionar a guia de comandos Criar da Faixa de Opções. 
 Selecionar o botão Design da Consulta 
 Vai aparecer a janela de Consulta abaixo. 
 
 
 
Janela de Consulta vazia 
 
Para cada tabela ou consulta a adicionar, selecione seu nome e clique no 
botão Adicionar. Caso adicionar uma tabela/consulta errada, basta selecioná-la 
e apertar DEL para removê-la da área de tabelas. 
 
 
 
Janela de Consulta com tabelas selecionadas 
Tipos de Consultas 
Há dois tipos de consulta: 
 Seleção 
Consulta que seleciona e mostra apenas o conteúdo selecionado. 
Observamos que este é o tipo previamente selecionado pelo Access, isto é, 
padrão. 
 Ação 
Consulta que age sobre a tabela envolvida. Estas consultas são: criar 
tabela, acrescentar, atualizar e excluir. 
 
Como a consulta padrão é a Seleção, o Access inicia com esta opção. 
 
 
 
 
Consulta Seleção 
Através da consulta seleção podemos: 
 Escolher campos específicos de tabelas específicas; 
 Selecionar os dados através de critérios; 
 Mostrar os dados em várias ordens; 
 Obter dados de várias tabelas; 
 Derivar dados a partir de dados existentes no banco de dados. 
Os registros selecionados constituem um conjunto chamado Dynaset. Este 
conjunto é dinâmico no sentido de seu conteúdo ter por origem várias tabelas e no 
sentido de só existir enquanto a consulta estiver ativa. Ao fechar uma consulta, ele 
deixa de existir. Isto constitui uma grande vantagem, pois se alterarmos os dados 
de uma tabela as consultas sobre ela automaticamente refletirão essas alterações. 
 
A especificação da consulta seleção é feita através da grade QBE (Query By 
Example). 
 
 
Exemplo da Grade QBE 
 
Campo 
Esta linha serve para selecionar os campos à consulta. 
Tabela 
Esta linha serve para indicar a tabela a que pertence o campo selecionado. 
 
Classificação 
Basta abrir a caixa de dropdown clicando a seta que aparece na linha 
Classificação e escolher a ordem de classificação desejada (Crescente ou 
Decrescente. Observar que a ordem dos campos passa a ser importante, uma vez 
que a prioridade de classificação é da esquerda para direita. Portanto, pode ser 
necessário rearranjar as colunas em relação à sua ordem na tabela original. 
Mostrar 
Eventualmente é necessário utilizar um campo numa consulta sem que haja 
necessidade de mostrá-lo na folha de dados. A linha Mostrar serve para indicar se 
o campo deve ser mostrado ou não. Para não mostrar, basta desligar a caixa de 
seleção. 
Exemplos 
Suponha que você queira exibir apenas os alunos do curso EAD sem 
mostrar o campo Código do Curso. Para tanto, especifique “EAD” na linha 
Critério do campo Código do Curso e desligue a caixa de seleção da linha 
Mostrar como mostra o exemplo abaixo: 
 
 
Critério 
Como critério de seleção, podemos empregar diversos tipos de expressões 
envolvendo diversos tipos de funções internas. O quadro a seguir mostra alguns 
exemplos de expressões. 
 
Expressão Resultado 
>520 Todos os números maiores que 520 
entre 520 e 528 Todos os números entre 520 e 528 inclusive 
“EAD” Todos exatamente iguais a 
“S*” Todos iniciados com a letra S (Quando utilizado 
em uma expressão, o asterisco (*) representa 
qualquer sequência de caracteres) 
“fe*eira” (*) qualquer número de caracteres. Por exemplo: 
ferreira, fereira, festeira 
“sm?th” (?) qualquer caractere alfabético isolado. Por 
exemplo: smith, smyth 
“* * *” Nomes de três palavras separadas por um branco 
*/*/2007 Todas as datas de 2007 
>=1-jan-2007 e <=20-fev-2007 Todas as datas no intervalo 
“S*” ou “R*” Todos iniciados pela letra S ou R 
nulo Todos os registros com campo nulo 
negado nulo Todos os registros com valor no campo 
em (“EAD”; “EAE”) Todos com valor na lista 
data() Todos com a data atual 
entre data() e data()-7 Todos com data da última semana 
Exemplo de expressões 
Para especificar critérios de seleção envolvendo mais de um campo é necessário 
empregar as linhas Critério e ou da grade QBE. Deve-se observar a seguinte regra 
para definir expressões entre vários campos: 
 as expressões ligadas por e ficam na mesma linha 
 as expressões ligadas por ou ficam em linhas diferentes. 
Salvar e visualizar o resultado da consulta 
Para salvar a consulta, use as opções Salvar e Salvar como do Botão Office. 
Também será salva a tabela se fechamos a janela através do botão de 
fechamento do Windows. 
Para visualizar o resultado da consulta devemos clicar no botão Modo de Exibição 
e selecionar a opção Modo de Exibição de Folha de Dados. Para voltar à estrutura 
da consulta basta clicar no Modo de Exibição e selecionar o Modo Design. 
Para imprimir o resultado da consulta devemos estar na folha de dados e acionar 
Imprimir do Botão Office. 
 
Exercícios 
 
Como adicionar campos à grade QBE 
1. Listar todos os alunos com seus dados. 
 
 
 
 Selecionar a guia de comandos Criar da Faixa de Opções. Em seguida, 
selecione a opção Design da Consulta (Esta sequência de operações será 
representada como Criar/Design da Consulta). 
 Na janela Mostrar tabela selecionar a tabela Aluno e dar clique no botão 
Adicionar e, em seguida, dar clique no botão Fechar. 
 Selecionar os campos da tabela Aluno. Há várias maneiras de adicionar 
campos à grade QBE: 
o Pode-se selecionar vários campos clicando e apertando 
simultaneamente SHIFT ou CTRL. Em seguida, apontar para um dos 
campos selecionados e arrastar para a linha Campo na grade QBE. 
o Também é possível adicionar um campo abrindo a caixa de dropdown 
(clicar a seta que aparece na linha Campo) e escolher entre os campos 
listados. 
o Para selecionar todos os campos da tabela há um método muito 
interessante. Basta clicar duas vezes a barra de título da tabela e 
arrastar para a linha Campo na grade QBE. 
o Para selecionar e adicionar todos os campos há um método (opção 
Todos) que consiste em clicar duas vezes a linha de asterisco (*) da 
tabela. Entretanto, os campos QBE baseados em asterisco não admitem 
critério de classificação e seleção, uma vez que os campos não são 
identificados. 
o Para inserir um campo entre outros já existentes na planilha QBE basta 
arrastar o campo selecionado para o campo que ficará à direita do 
campo inserido. 
o Para rearranjar campos na planilha QBE basta selecionar e arrastar 
para outra posição. Para eliminar um campo, basta selecionar e dar 
DEL. 
 Salvar a consulta. Para salvar a consulta, use as opções Salvar e Salvar 
como do Botão Office. Também será salva a tabela se fechamos a janela 
através do botão de fechamento do Windows. 
 Dar duplo clique na consulta para visualizar o resultado da consulta. 
 
 
 
 
 
Opção Todos (*) 
 
2. Listar todas as disciplinas com seus dados. 
 
 
 
Como usar a Folha de Propriedade 
Para alterar as propriedades dos campos de uma consulta basta marcar 
o campo cuja propriedade deseja alterar e acionar o botão Folha de Propriedade 
ou dar um clique no botão do lado direito do mouse e selecionar a opçãoPropriedades. Em resposta, aparecerá a janela Folha de Propriedade. Certifique-
se que a janela de propriedades se refere ao campo e não à consulta. As 
propriedades que podem ser alteradas são: descrição, formato, máscara de 
entrada e legenda. 
 
 
3. Listar o nome do aluno em caracteres maiúsculos. 
 
 
 Criar/Design da Consulta. 
 Na janela Mostrar tabela selecionar a tabela Aluno e dar clique no botão 
Adicionar e, em seguida dar clique no botão Fechar. 
 Selecionar o campo Nome do aluno. 
 Marcar o campo Nome do aluno e acionar o botão Folha de Propriedade ou 
dar um clique no botão do lado direito do mouse e selecionar a opção 
Propriedades. Em seguida, especificar a opção “>” na propriedade 
Formato. 
 Salvar a consulta. 
 Dar duplo clique na consulta para visualizar o resultado da consulta. 
 
 
 
 
 
 
 
 
 
4. Listar todos os alunos alterando a legenda do campo 
Número de matrícula para Número USP e o formato do 
campo Data de nascimento para Data abreviada. 
Notar que na tabela a legenda do campo Número de matrícula está 
especificada como NUSP e o campo Data de nascimento está no formato 
Data normal. 
 
 
Tabela Aluno 
 
Resultado da Consulta 
5. Para todos os alunos listar seu nome e o nome das 
disciplinas cursadas com as respectivas notas. 
 
 
 
É comum que dados para uma consulta se localizem em diversas tabelas. 
Note que a tabela Aproveitamento faz referência apenas ao número de 
matrícula do aluno e ao código da disciplina. Os nomes de cada aluno e de 
cada disciplina constam somente nas tabelas Aluno e Disciplina 
respectivamente. 
Se desejarmos fazer uma consulta que apresente o nome do aluno com os 
nomes das disciplinas cursadas com as respectivas notas será necessário 
usar as três tabelas: Aluno, Aproveitamento e Disciplina. 
Como já definimos os relacionamentos entre as três tabelas na janela 
Relações, ao adicionar as tabelas na janela Consulta seleção aparecerá 
automaticamente a linha de ligação entre os campos relacionados. 
 Criar/Design da Consulta. 
 Na janela Mostrar tabela adicionar as tabelas Aluno, Aproveitamento e 
Disciplina. 
 Selecionar os campos Nome do aluno, Nome da disciplina, Nota 1 e Nota 2. 
 Salvar a consulta. 
 Dar duplo clique na consulta para visualizar o resultado da consulta. 
 
 
 
Como utilizar o critério de seleção 
Com o critério de seleção podemos empregar diversos tipos de expressões para 
selecionar os registros de consultas. 
 
Expressão Resultado 
>520 Todos os números maiores que 520 
entre 520 e 528 Todos os números entre 520 e 528 inclusive 
“EAD” Todos exatamente iguais a 
“S*” Todos iniciados com a letra S (Quando utilizado 
em uma expressão, o asterisco (*) representa 
qualquer sequência de caracteres) 
“fe*eira” (*) qualquer número de caracteres. Por exemplo: 
ferreira, fereira, festeira 
“sm?th” (?) qualquer caractere alfabético isolado. Por 
exemplo: smith, smyth 
“* * *” Nomes de três palavras separadas por um branco 
*/*/2007 Todas as datas de 2007 
>=1-jan-2007 e <=20-fev-2007 Todas as datas no intervalo 
“S*” ou “R*” Todos iniciados pela letra S ou R 
nulo Todos os registros com campo nulo 
negado nulo Todos os registros com valor no campo 
em (“EAD”; “EAE”) Todos com valor na lista 
data() Todos com a data atual 
entre data() e data()-7 Todos com data da última semana 
Exemplo de expressões 
 
Para especificar critérios de seleção envolvendo mais de um campo é necessário 
empregar as linhas Critério e ou da grade QBE. Deve-se observar a seguinte regra 
para definir expressões entre vários campos: 
 as expressões ligadas por e ficam na mesma linha 
 as expressões ligadas por ou ficam em linhas diferentes. 
 
6. Selecionar os alunos de administração de empresas 
(EAD) sem mostrar o curso. 
 
 
 
 
 Criar/Design da Consulta. 
 Na janela Mostrar tabela selecionar a tabela Aluno e dar clique no botão 
Adicionar. 
 Selecionar todos os campos. 
 Na linha Critérios do campo Código do curso especificar EAD. 
 Na linha Mostrar do campo Código do curso desligar a caixa de seleção. 
 Salvar a consulta. 
 Dar duplo clique na consulta para visualizar o resultado da consulta. 
 
 
 
Observação: 
Não é necessário digitar aspas (“). O Access colocará automaticamente. 
7. Selecionar os alunos de economia (EAE) através da 
consulta parametrizada. 
 
 
Janela para Inserir valor do parâmetro 
 
 
 
 Criar/Design da Consulta. 
 Na janela Mostrar tabela selecionar a tabela Aluno e dar clique no botão 
Adicionar. 
 Selecionar todos os campos. 
 Na linha Critérios digitar [Digite o código do curso] (Não esquecer de 
envolver a mensagem entre colchetes). Ao executar a consulta, esta 
mensagem será exibida na janela Inserir valor do parâmetro. 
 Salvar a consulta. 
 Dar duplo clique na consulta para visualizar a janela. 
 Digitar EAE e dar OK para visualizar o resultado da consulta. 
 
 
 
8. Selecionar os alunos de economia (EAE) e contabilidade 
(EAC). 
 
 
 
Dica 
Na linha Critérios do campo Código do curso especificar EAE ou EAC. 
 
9. Selecionar todos os alunos do curso de administração de 
empresas (EAD) e que nasceram em 1984. 
 
 
 
Dica 
Na linha Critérios especificar EAD no campo Código do curso e */*/1984 no 
campo Data de nascimento. Note que como as expressões estão ligadas 
por e ficaram na mesma linha. 
Quando utilizado em uma expressão (*/*/1984), o asterisco (*) representa 
qualquer sequência de caracteres. Note que as expressões ligadas por e 
ficam na mesma linha. 
10. Selecionar os alunos do curso de contabilidade (EAC) ou 
alunos nascidos em 1984. 
 
Dica 
Na linha Critérios especificar EAC no campo Código do curso e na linha ou 
especificar */*/1984 no campo Data de nascimento. Note que como as 
expressões estão ligadas por ou ficaram em linhas diferentes. 
Quando utilizado em uma expressão (*/*/1984), o asterisco (*) representa 
qualquer sequência de caracteres. Note que as expressões ligadas por ou 
ficam em linhas diferentes. 
 
 
 
11. Selecionar todos os alunos do curso de administração de 
empresas (EAD) nascidos em 1984 ou alunos com 
nomes iniciados pela letra A. 
 
 
 
Dica 
Na linha Critérios especificar EAD no campo Código do curso e */*/1984 no 
campo Data de nascimento e na linha ou A* no campo Nome do aluno. 
 
Como derivar campos 
 
É possível derivar um campo a partir de campos existentes no BD. Este campo 
deve ter a seguinte sintaxe: 
Nome do campo: expressão de derivação 
Exemplo; Média:([Nota 1]+[Nota 2])/2 
O nome do campo que antecede dois pontos (:) é o nome a ser atribuído ao 
campo derivado e a expressão especifica como o valor deve ser calculado. 
Observar que os campos utilizados em expressões precisam estar entre 
colchetes. 
 
 
12. Para todos os alunos listar seu nome e o nome das 
disciplinas cursadas com as respectivas notas e média. 
 
 
 
 
 
 Criar/Design da Consulta. 
 Na janela Mostrar tabela selecionar a tabela Aluno, Aproveitamento e 
Disciplina. 
 Selecionar os campos mencionados. 
 No primeiro campo em branco especificar Média:([Nota 1]+[Nota 2])/2. O 
nome Média que antecede dois pontos (:) é o nome a ser atribuído ao 
campo derivado e a expressão: ([Nota 1]+[Nota 2])/2 especifica como o 
valor deve ser calculado. Observe-se que campos utilizados em expressões 
precisam estar entre colchetes, daí a razão de se escrever [Nota 1] e [Nota 
2]. 
Campo derivado 
 Salvar a consulta como ConsultaMédia. 
 Dar duplo clique na consulta para visualizar o resultado da consulta. 
 Notar que as médias apareceram no formato Número Geral, isto é, sem 
formatação. Para aparecer no formato Fixo, basta marcar este campo e 
acionaro botão Folha de Propriedade ou dar um clique no botão do lado 
direito do mouse e selecionar a opção Propriedades. Em seguida, 
especificar na propriedade Formato a opção Fixo. 
 
 
 
 
 
Campo Média sem formatação 
13. Para todos os alunos listar seu nome e o nome das 
disciplinas cursadas com as respectivas notas, média, 
frequência e status (Aprovado ou Reprovado). 
 
 
 Criar/Design da Consulta. 
 Na janela Mostrar tabela selecionar a tabela Aluno, Aproveitamento e 
Disciplina. 
 Selecionar os campos mencionados. 
 No primeiro campo em branco especificar Média:([Nota 1]+[Nota 2])/2. 
 Marcar este campo e acionar o botão Folha de Propriedade ou dar um 
clique no botão do lado direito do mouse e selecionar a opção 
Propriedades. Em seguida, especificar na propriedade Formato a opção 
Fixo e valor 1 na opção Casas Decimais. 
 No outro campo especificar Status: IIf(([Média]>=5 E 
[Frequência]>=0,7);"Aprovado";"Reprovado"). A função IIf é a 
abreviação do termo Immediate If. Em português é Se Imediato (SeImed). 
 Salvar a consulta. 
 Dar duplo clique na consulta para visualizar o resultado da consulta. 
 Acertar o número de casas decimais do campo Média usando a Folha de 
Propriedades. 
 
 No outro campo especificar: 
Status: IIf(([Média]>=5 E [Frequência]>=0,7);"Aprovado";"Reprovado"). 
 A função IIf é a abreviação do termo Immediate If. Em português é Se 
Imediato (SeImed). 
 
 
 
 Salvar a consulta. 
 Dar duplo clique na consulta para visualizar o resultado da consulta. 
 Acertar o número de casas decimais do campo Média usando a Folha de 
Propriedades. 
 
 
 
 
 
 
 
 
Como calcular os totais das colunas (campos) 
 
Para calcular estatísticas simples (soma, média, mínimo, máximo, contagem, 
desvio padrão e variância) é necessário acionar o botão de Totais na barra de 
ferramenta para inserir na grade QBE a linha de Total. 
 
 
 
 
O Access fornece apenas a função Contar para campos definidos como tipo de 
dados Texto, pois não é possível realizar operações matemáticas em dados de 
texto. 
A opção Agrupado por serve para definir o como os dados devem ser agrupados 
para o calculo de estatísticas. 
 
14. Listar o número de alunos por curso. 
 
 Criar/Design da Consulta. 
 Na janela Mostrar tabela selecionar a tabela Aluno. 
 Selecionar os campos: Código do curso e Número de matrícula. 
 Acionar o botão de Totais na barra de ferramenta para inserir a linha de 
Total. 
 Na linha Total selecionar a opção Agrupado por no campo Código do curso 
e Contar no campo Número de matrícula. A opção Agrupado por no campo 
Código do curso especifica que a contagem deve ser feita agrupado por 
curso. 
 Salvar a consulta. 
 Dar duplo clique na consulta para visualizar o resultado da consulta. 
 Notar que o cabeçalho da coluna da média apareceu como 
ContarDeNúmero de matrícula. Para aparecer o cabeçalho Número de 
alunos, digite “Número de alunos:“ antes do nome do campo Número de 
matrícula. (Número de alunos: Número de matrícula). 
 
 
 
 
 
 
 
15. Listar a média da média de cada aluno 
 
 Criar/Design da Consulta. 
 Na janela Mostrar tabela clicar o botão Consultas e selecionar a consulta do 
exercício 12 (ConsultaMédia). 
 Selecionar os campos: Nome do aluno e Média. 
 Acionar o botão de Totais na barra de ferramenta para inserir a linha de 
Total. 
 Na linha Total selecionar a opção Agrupado por no campo Nome do aluno e 
Média no campo Média. A opção Agrupado por no campo Nome do aluno 
especifica que a média da média deve ser calculada agrupado por aluno. 
 Salvar a consulta. 
 Dar duplo clique na consulta para visualizar o resultado da consulta. 
 Notar que as médias apareceram no formato Número Geral. Para aparecer 
no formato Fixo, basta marcar este campo e acionar o botão Folha de 
Propriedade ou dar um clique no botão do lado direito do mouse e 
selecionar a opção Propriedades. Em seguida, especificar na propriedade 
Formato a opção Fixo e valor 1 na opção Casas Decimais. 
 Notar também que o cabeçalho da coluna da média apareceu como 
MédiaDeMédia. Para aparecer apenas Média, digite “Média:“ antes do 
nome do campo Média (Média: Média). 
 
 
 
 
 
 
 
 
 
16. Listar as seguintes estatísticas (número de alunos, 
média de Nota 1 e média de Nota 2) por disciplina 
segundo curso. 
 
 Criar/Design da Consulta. 
 Na janela Mostrar tabela selecionar a tabela Curso, Aluno, Aproveitamento 
e Disciplina. 
 Selecionar os campos Nome do curso, Nome da disciplina, Número de 
matrícula (tabela Aproveitamento), Nota 1 e Nota 2. 
 Acionar o botão de Totais na barra de ferramenta para inserir a linha de 
Total. 
 Na linha Total selecionar a opção Agrupado por nos campos Nome do 
curso e Nome da disciplina e a opção Contar no campo Número de 
matrícula e a opção Média nos campos Nota 1 e Nota 2. 
 Salvar a consulta. 
 Dar duplo clique na consulta para visualizar o resultado da consulta. 
 Para acertar as casas decimais e o cabeçalho, ver os procedimentos 
descritos nos exercícios anteriores.