ESSPS ESSPS - Exercícios sobre Access
Ficha
1
1º - Explorar uma base de dados: Abrir uma base de dados Consultar os dados Inserir, modificar e apagar registos Procurar e substituir dados Filtrar e ordenar registos Crie uma base de dados vazia, com o nome Taqueiro Crie uma tabela nova, pela opção “vista de estrutura” Insira nessa tabela os seguinte campos: Descricao Desc ricao texto tamanho tamanho 20 Preco moeda Unidade Unidade texto tamanho 5 Iva numero Defina a descrição como chave primária Feche a tabela e atribua-lhe o nome T_preços Abra a tabela Insira na tabela os seguinte registos: descricao Parque Taco Soalho Cola Pregos Pregos Cera Verniz Mão de Obra Deslocação
preco 25,00 € 20,00 € 15,00 € 1,50 € 7,50 € 4,00 € 6,00 € 20,00 € 0,20 €
unidade m2 m2 m2 L Kg Kg L h Km
iva 17 17 17 17 17 5 17 17 0
Modifique Verniz para Verniz brilhante , e o preço 6 para 6.5 Crie um novo registo Cola, 0.5, L, 5 Porque o Access não aceitou este novo registo? Altere a descrição para Cola branca e tente de novo Navegue na tabela através dos comandos da barra
Apague o registo com a descrição Parque Ajuste automaticamente a largura das colunas unidade e iva Ponha a coluna preço a seguir à coluna unidade Com o comando Localizar encontre O registo com a descrição cola Todos os registos em que cola apareça na descrição Todos os registos que tenham a letra c na descrição Todos os registos com a letra m em qualquer campo Altere com o comando Substituir as unidades L para Litro Ordene a tabela por ordem crescente das descrições Ordene a tabela por ordem decrescente dos preços Através do comando Filtar por formulário filtre os registos com a unidade m2 Remova o filtro Coloque o cursor num campo Litro e execute o comando Filtrar por selecção Experimente o comando Filtrar excluindo a selecção Abra a janela Filtro avançado (esta grelha é igual à que vai encontrar na criação de pesquisas)
Pág. 1
ESSPS - Exercícios sobre Access
Ficha
Exercício – Os
2
empregados de um gabinete de tradução, decidiram criar uma pequena biblioteca para partilharem os livros que cada um tem. Pretendem assim uma base de dados que registe quem deposita os livros. Considere que todos os livros tem cotas diferentes.
EMPREGADO
1
n DEPOSITAR
LIVRO
Empregado(nome, bi, morada, telefone) Livro(cota, titulo, autor, assunto, editora, ano, bi)
2º - Tabelas no Access: Criar e modificar uma tabela Chave primaria Propriedades de um campo Relação entre tabelas Crie uma base de dados vazia Crie uma tabela nova, pela opção “vista de estrutura” Insira nessa tabela os seguintes campos: nome texto tamanho 40 necessário bi numero inteiro longo 0 decimais necessário morada texto tamanho 100 telefone numero inteiro longo 0 decimais Defina o bi como chave primária Feche a tabela e atribua-lhe o nome T_empregado Crie outra tabela nova, pela opção “vista de estrutura” Insira nessa tabela os seguintes campos: cota numero inteiro longo 0 decimais necessário titulo texto tamanho 40 necessário autor texto tamanho 40 assunto texto tamanho 40 editora texto tamanho 40 ano numero inteiro 0 decimais bi numero inteiro longo 0 decimais necessário Nota: A cota poderia ser “numeração automática” Defina o cota como chave primária Obrigue o campo assunto a assumir valores que constem de uma lista pré-definida: 1. em vez de texto escolher Assistente de Pesquisa 2. clicar eu escrevo os valores que quero 3. escrever na coluna os valores dicionário , política, poesia , diversos 4. verificar nas Propriedades do campo / Pesquisa as alterações Para o campo ano : Legenda ano publicação Regra de validação >1900 Texto de validação O ano de publicação tem de ser superior a 1900 Necessário Não Feche a tabela e atribua-lhe o nome T_livro
Pág. 2
ESSPS - Exercícios sobre Access
Ficha
2
Estabeleça o relacionamento entre as tabelas:
impondo a integridade referencial (repare nas opções possíveis):
Preencha as tabelas com os seguintes dados: nome João Matos Carlos Costa Antonio Silva Rosario Mendes Pedro Castro Rui Almeida cota 5021 4003 5168 2010
titulo Para Francês Alo, Alo Discursos Odisseia
autor J. Almeida Michelle Muito Fala T. Almeida
bi 12345670 12345671 12345678 64578565 85168533 98646456
morada Rua Verde, 100 Rua Preta, 1 Rua Azul, 32 Rua Iniciativa, 20 Rua Oliveiras, 12 Rua Travessa, 13
assunto dicionário diversos política diversos
editora Porto Edit Faz Livros Mac Grao Espacial
telefone 55512 55593 55514 55579 55533 55513 ano publicação 1988 1980 2000 2001
bi 85168533 12345670 12345670 12345671
Altere o ano do livro com a cota 2010 para 1800 (faça ESC para anular a alteração). Altere o bi do livro com a cota 2010 para 333444.
Pág. 3
ESSPS - Exercícios sobre Access A inserção do bi do dono pode ser feita de outra maneira mais simples. Assim vamos obrigar a que um campo só possa assumir valores que constem de outro campo (versão I): 1. na opção Vista de estrutura da tabela T_livro seleccione o campo bi 2. em vez de numero escolher Assistente de Pesquisa 3. clicar quero que a coluna de pesquisa pesquise os valores numa tabela ou consulta 4. tabela ou consulta que fornece os valores: T_empregado 5. campo seleccionado: bi 6. verificar nas Propriedades do campo / Pesquisa as alterações Abra a tabela T_livro e acrescente os seguintes livros: cota titulo autor 2011 No espaço K. Aéreo 5030 Francês Inglês J. Almeida
assunto Diversos dicionário
editora Espacial Porto Edit
ano publicação 1999 1998
bi 12345670 12345678
Agora vamos obrigar a que um campo só possa assumir valores que constem de outro campo (versão II): 1. na opção Vista de estrutura da tabela T_livro seleccione o campo bi 2. em vez de numero escolher Assistente de Pesquisa 3. clicar quero que a coluna de pesquisa pesquise os valores numa tabela ou consulta 4. tabela ou consulta que fornece os valores: T_empregado 5. campo seleccionado: bi e nome 6. optar por ocultar a coluna chave 7. verificar nas Propriedades do campo / Pesquisa as alterações Abra a tabela T_livro e acrescente os seguintes livros: cota titulo 6000 Lusíadas 6001 Lusíadas
autor L. Camões L. Camões
Assunto Poesia Poesia
editora Portuguesa Portuguesa
ano publicação bi 1960 Pedro Castro 1960 Pedro Castro
NOTA : Embora o Access mostre o nome do empregado, está a guardar o numero do bi na base de dados. Agora vamos obrigar a que um campo só possa assumir valores que constem de outro campo (versão III): 1. na opção Vista de estrutura da tabela T_livro seleccione o campo bi 2. em vez de numero escolher Assistente de Pesquisa 3. clicar quero que a coluna de pesquisa pesquise os valores numa tabela ou consulta 4. tabela ou consulta que fornece os valores: T_empregado 5. campo seleccionado: bi e nome 6. optar por não ocultar a coluna chave 7. armazenar o bi 8. verificar nas Propriedades do campo / Pesquisa as alterações Abra a tabela T_livro e acrescente os seguintes livros: cota titulo 5031 Russo Grego 2500 Até que enfim
autor Falador Ultimo
Assunto Dicionário Diversos
editora Faro Edit Livrada
ano publicação 1990 1999
bi 12345678 12345671
Observações:
Esta base de dados vai ser necessária nos exercícios seguintes. O Access guarda automaticamente as alterações efectuadas, logo basta fechar a base de dados para o exercício ficar concluído. O Access não permite fazer “Guardar como” (“Save as...”) da base de dados, logo se criou a base de dados no disco e quiser guardá-la na disquete tem de fechar a base de dados (obrigatoriamente ) e copiar o ficheiro (com extensão “mdb”) com o Explorador do Windows.
Pág. 4
ESSPS - Exercícios sobre Access 3º - Pesquisas no Access: O que é uma pesquisa (consulta ou query)? Criar um pesquisa Pesquisas de selecção Cálculos nas pesquisas Pesquisas de análise cruzada Pesquisas multi-tabela Pesquisas com ordenação Pesquisas por parâmetros Abra a base de dados que criou no exercício anterior Pesquisas de selecção: Crie uma consulta Q_diversos que liste todas os livros cujo assunto é diversos
Crie uma consulta Q_livros_editados_desde_1995 que liste os livros editados depois de 1995
Crie uma consulta Q_livros_com_cota_5000 que liste os livros com cota 5???
Crie uma consulta Q_empregados_com_nome_comecado_por_R que liste os empregados cujo nome comece pela letra R
Cálculos nas pesquisas: Crie uma consulta Q_empregado_depositou_quantos_livros que mostre quantos livros depositou cada empregado
Pág. 5
ESSPS - Exercícios sobre Access Crie uma consulta Q_empregado_depositou_quantos_livros_2 que mostre quantos livros depositou cada empregado, e a coluna onde é mostrada a contagem com o nome Quantidade
Crie uma consulta Q_quantos_anos_tem_o_livro que mostre quantos anos tem cada livro
Pesquisas multi-tabela: Crie uma consulta Q_livros_que_os_empregados_depositaram que mostre uma lista completa de todos os livros que os empregados depositaram
Pesquisas com ordenação: Crie uma consulta Q_livros_que_os_empregados_depositaram_ordenados_por_cota que mostre uma lista completa de todos os livros que os empregados depositaram, ordenada por ordem crescente das cotas
Pesquisas por parâmetros: Criar uma consulta Q_livros_pelo_titulo que pergunte ao utilizador o titulo de um livro e mostre os dados relativos aos livros com esse titulo (o texto do critério, entre parêntesis rectos, tem de ser diferente do nome do campo)
Criar uma consulta Q_livros_publicados_depois_de que pergunte ao utilizador um ano e mostre os dados relativos aos livros que foram publicados depois desse ano. A tabela deve ficar ordenada por ordem decrescente do ano de publicação
Pág. 6
ESSPS - Exercícios sobre Access 4º - Formulários no Access: O que é um formulário? Criar um formulário Propriedades dos controlos Zonas de lista e listas modificáveis Caixas de marcação Grupos de opções Botões associados a comandos Alteração do tipo de controlo Controlos independentes de inserção de dados Formulários multi-tabela Expressões nos formulários Secções de um formulário Filtro de um formulário A partir da base de dados criada nos exercícios anteriores crie os formulá rios seguintes: Mostre todos os livros “diversos” da base de dados: Assistente de formulários Origem: Q_diversos Campos seleccionados: todos Esquema: tabela Estilo: padrão Titulo: F_diversos Mostre todos os livros: Assistente de formulários Origem: T_livro Campos seleccionados: todos Esquema: tabela Estilo: expedição Titulo: F_livros Mostre toda a informação sobre um empregado (ficha do empregado):
Assistente de formulários Origem: Campos seleccionados: todos Esquema: Estilo: Titulo:
T_empregado colunas padrão F_empregado
Mostre toda a informação sobre um empregado e os livros por ele depositados: Assistente de formulários Origem: T_empregado Campos seleccionados: todos do T_empregado + todos do T_livro menos o bi Ver dados: por T_empregado formulário com subformulários Esquema: tabela Estilo: padrão Titulo: F_empregado1 F_empregado1_sub
Pág. 7
ESSPS - Exercícios sobre Access Mostre quantos livros depositou cada empregado e a lista desses livros: Assistente de formulários Origem: Q_empregado_depositou_quantos_livros Campos seleccionados: todos do Q_ empregado_depositou_quantos_livros + + todos do T_livro menos o bi Ver dados: por Q_ empregado_depositou_quantos_livros formulário com subformulários Esquema: tabela Estilo: padrão Titulo: F_empregado2 F_empregado2_sub Mostre toda a informação sobre um livro (ficha do livro):
Assistente de formulários Origem: Campos seleccionados: todos Esquema: Estilo: Titulo:
T_livro colunas pedra F_livro1
Mostre toda a informação sobre um livro e o empregado que o depositou: Assistente de formulários Origem: T_livro Campos seleccionados: todos do T_livro + todos do T_empregado menos o bi Ver dados: por T_livro Esquema: colunas Estilo: pedra Titulo: F_livro2 Um formulário é composto pelas seguintes secções: Cabeçalho do formulário Cabeçalho de página Detalhe Rodapé de página Rodapé do formulário Abra o formulário F_livro2 em modo estrutura. Mude a legenda (nas propriedades) do campo bi para “bi empregado”. Iniba a alteração do campo nome do empregado (propriedade Protegido = sim). Repita a operação para os restantes campos do empregado (morada e telefone). Verifique na vista de formulário que o Access não permite a alteração destes campos. Abra novamente o formulário F_livro2 em modo estrutura. No cabeçalho do formulário insira o titulo (rótulo) “Biblioteca” em Courier New tamanho 20, bold e centrado. Este titulo deve ser mostrado apenas no écran. Insira ainda uma imagem do clipart, ao lado do titulo, que seja visível só no écran. No cabeçalho de página insira o titulo “Registo dos livros depositados:” em tamanho 16, mostrado apenas na impressão. Insira o numero de página no rodapé com o formato “Página N de M” alinhada à direita. Verifique o resultado das alterações introduzidas. Crie um botão (no detalhe) que abra o formulário F_empregado, e que só apareça no écran.
Pág. 8
ESSPS - Exercícios sobre Access 5º - Relatórios no Access: O que é um relatório? Criação de relatórios Formato dos dados Secções de um relatório Ordenação e agrupamento Formatação de um relatório Gestão das quebras de página A partir da base de dados criada nos exercícios anteriores crie os relatórios seguintes: Mostre todos os livros da base de dados: Assistente de relatórios Origem: T_livro Campos seleccionados: todos Nível de agrupamento: nenhum Ordenação: cota (ascendente) Esquema: tabela Estilo: formal Titulo: R_livro Mude o cabeçalho do relatório para “Lista de livros” Mostre todos os empregados da base de dados: Assistente de relatórios Origem: T_empregado Campos seleccionados: todos Nível de agrupamento: nenhum Ordenação: nome (ascendente) Esquema: tabela Estilo: cinzento suave Titulo: R_empregado Mostre cada empregado e a lista dos livros por ele depositados: Assistente de relatórios Origem: Q_livros_que_os_empregados_depositaram Campos seleccionados: todos Ver dados: por T_empregado Nível de agrupamento: nenhum Ordenação: cota (ascendente) Esquema: alinhar à esquerda 1 Estilo: cinzento suave Titulo: R_livros_por_empregado Mude o cabeçalho do relatório para “Biblioteca:” Insira no cabeçalho da página o texto “Livros depositados pelo empregado:” Faça a informação de cada empregado aparecer numa folha separada (Forçar quebra de página “Antes da secção” nas propriedades do cabeçalho)
Pág. 9
ESSPS - Exercícios sobre Access Mostre etiquetas de todos os livros: Inserir relatório + Assistente de etiquetas Origem: T_livro Dimensão: 90.2 x 42.3 Texto: Arial 10 normal Etiqueta:
Ordenação: Titulo:
cota R_etiquetas_para_livro
Mostre um gráfico da percentagem de livros depositados por empregado: Inserir relatório + Assistente de gráficos Origem: Q_empregado_depositou_quantos_livros_2 Campos: nome + quantidade Tipo: gráfico circular Dispor dados: Titulo do gráfico: Deposito de Livros Titulo: R_grafico_circular_deposito_livros_por_empregado
Nota: Indexações (índices ou indexes): Suponha que da análise da sua base de dados, para uma recepcionista que faz as marcações das consultas de vários médicos, resulta a seguinte tabela onde se faz esse registo:
Como existe um campo chamado numero , que é o numero da marcação, esse campo foi escolhido para chave primária. Mas interessa restringir a entrada de dados de modo que para o mesmo médico não haja 2 consultas no mesmo dia à mesma hora. Se a chave primária fosse data+hora+médico o problema estava resolvido porque não são permitidas repetições da chave primária.
Pág. 10
ESSPS- Exercícios sobre Access Para resolver o problema podemos recorrer aos indexes :
e por defeito o Access cria uma indexação relativa à chave primaria da tabela:
Podemos então criar outra indexação de modo a evitar essas repetições indesejadas:
neste caso criamos uma indexação chamada não_repete que garante não existirem repetições do conjunto de valores data/hora/médico.
Pág. 11
