Lição
6 - Consultas de Alteração, Exclusão e Acréscimo
de Dados
Outros tipos de consultas e suas utilizações:
Neste ítem veremos outros 4 tipos de consultas do
Microsoft Access:
Consulta criar tabela
Consulta atualização
Consulta acréscimo
Consulta
exclusão
A maneira de construir cada um destes tipos de consultas é
bastante simples. O Importante é que saibamos qual o tipo
de consulta utilizar na resolução de um determinado
problema. Abaixo temos uma descrição de cada um dos
tipos de consultas:
Consulta criar tabela: Definimos uma consulta
como sendo do tipo Consulta criar tabela, através do
menu consultas no modo estrutura. Ao definirmos a consulta como
sendo do tipo Criar tabela, o Microsoft Access solicita que
seja digitado um nome para a tabela que será criada e
irá conter os dados retornados pela consulta. Para que
a consulta seja executada e a tabela criada, não basta
irmos para o modo Folha De Dados (como fazíamos nas consultas
de seleção e de referência cruzada). O Modo
folha de dados, neste caso, simplesmente nos mostra os registros
que serão colocados na tabela a ser criada. Para executar
uma Consulta criar tabela (e em conseqüência criar
a tabela) você tem que executar a consulta. Para executar
uma consulta do tipo Consulta criar tabela, basta dar um clique
no botão executar consulta: Botão com um Ponto
de Exclamação vermelho ( ! ). Ao clicar neste
botão, o Microsoft Access avisa quantas linhas serão
adicionadas a nova tabela e pede confirmação,
basta clicar no botão Sim para confirmar a execução
da consulta. Caso já exista uma tabela com o nome da
tabela que está sendo criada, o Microsoft Access avisa
que todos os registros anteriores serão excluídos
e pede confirmação.
Existem diversas situações práticas em que
temos necessidade de utilizar uma consulta criar tabela:
Transferência de Dados consolidados para outros programas,
banco de dados, planilhas, etc
Criação de tabelas auxiliares de utilização
temporária
Transferência de dados para um disquete ou fita para
transferência dos dados para outros sistemas
Extração de dados para envio por e-mail ou
outra forma de envio automaticamente.
Consulta atualização: Definimos
uma consulta como sendo do tipo Consulta atualização,
através do menu consultas no modo estrutura. Ao definirmos
uma consulta como sendo do tipo atualização, surge
uma nova linha "Atualizar para", no modo estrutura
da consulta. Um exemplo prático para utilização
de consultas de atualização poderia ser uma situação
em que temos que aumentar os valores de um campo em 20%. Por
exemplo, poderíamos criar uma consulta de atualização
para aumentar em 20% o valor do Frete de todos os pedidos. Outro
exemplo, poderíamos criar uma consulta de atualização
para substituir todos as ocorrências de EUA para PaísDeDestino
por Estados Unidos da América. Para executar uma Consulta
de atualização você tem que executar a consulta.
Para executar uma consulta do tipo Consulta atualização,
basta dar um clique no botão executar consulta: Botão
com um Ponto de Exclamação vermelho ( !
). Ao clicar neste botão, o Microsoft Access avisa quantas
linhas serão atualizadas e pede confirmação,
basta clicar no botão Sim para confirmar a execução
da consulta. Você também pode especificar critérios
em uma consulta de seleção. Por exemplo, apenas
aumentar em 20 % os Pedidos cujo frete for maior do que R$ 20,00.
Devemos tomar o cuidado de não executar uma mesma consulta
de atualização diversas vezes. No nosso exemplo
anterior, cada vez que a consulta for executada, o valor do
Frete será aumentado em 20 %.
Consulta acréscimo: Definimos uma
consulta como sendo do tipo Consulta acréscimo, através
do menu consultas no modo estrutura. Também conhecida
como consulta de anexação. Bastante semelhante
a consulta de criação de tabela, apenas com a
diferença que ao invés de criar uma nova tabela
ou eliminar os registros caso a tabela já exista, a consulta
de acréscimo, anexa o resultado da consulta aos registros
já existentes em uma tabela especificada. A exemplo das
consultas criar tabela e de atualização, a consulta
de acréscimo também necessita ser executada. Ir
para o modo folha de Dados, simplesmente nos mostra quais os
registros serão anexados a tabela especificada, porém
para anexar os registros precisamos executar a consulta. Para
que a consulta de acréscimo tenha sucesso, algumas condições
devem ser atendidas: O Número e o tipo dos campos da
consulta e da tabela onde serão anexados devem ser compatíveis.
O Nome dos campos, não necessita, obrigatoriamente, ser
igual, embora esta seja um prática recomendada. Podemos
utilizar este tipo de consulta para irmos acumulando registros
em uma tabela. Por exemplo, podemos criar um consulta que crie
uma tabela com o total de vendas mensais e utilizar uma consulta
de anexação para consolidar os resultados em um
tabela de vendas anuais.
Consulta exclusão: Definimos uma consulta
como sendo do tipo Consulta exclusão, através
do menu consultas no modo estrutura. Utilizamos uma consulta
exclusão para eliminarmos registros em uma determinada
tabela. Devemos tomar cuidado ao definirmos uma consulta do
tipo exclusão, pois se não especificarmos nenhum
critério de filtragem, todos os registros da tabela em
questão serão excluídos, embora o Microsoft
Access nos peça confirmação antes de efetivar
a exclusão. A exemplo dos outros tipos de consultas vistos
neste ítem, não basta ir para o Modo Folha de
Dados. Para executar a consulta você precisa dar um clique
no botão executar (botão com um Ponto de Exclamação
vermelho). Por exemplo, poderíamos criar uma consulta
do tipo exclusão para eliminarmos todos os Pedidos, cujo
valor do frete fosse inferior a R$ 10,00. Ou poderíamos
criar uma consulta para eliminar todos os registros cuja diferença,
em dias, entre a DataDoPedido e a DataDeEntrega for maior do
que 15 dias.
Conforme observaremos mais adiante, o Microsoft Access utiliza um
ícone diferente para cada um dos tipos de consultas. Agora
chegou a hora de exercitarmos um pouco.
Exemplo 01:
Criar uma consulta do tipo Criação de Tabela, a qual
contenha os campos NúmeroDoPedido, NomeDaEmpresa e Frete,
com os pedidos para o ano de 1994 (Ano da DataDoPedido). Transforme
a consulta em uma Consulta criar tabela, a qual deverá criar
uma tabela chamada "Valores de Frete".
Para criar a consulta proposta faça o seguinte:
Crie uma nova consulta baseada nas tabelas Pedidos e Clientes.
Adicione os campos NúmeroDoPedido, NomeDaEmpresa e
Frete. Classifique a consulta em ordem crescente do NomeDaEmpresa.
Adicione uma coluna para extrair o ano da DataDoPedido e adicione
um critério para pegar apenas os pedidos para o ano de
1994
Salve a consulta com o nome de Criar Pedidos 1994
Transforme a consulta em uma Consulta criar tabela. Para isto
dê um clique no menu consultas e clique na opção
Consulta criar tabela. Feito isso irá surgir a janela
indicada na figura abaixo, a qual está solicitando que
seja digitado o nome da tabela a ser criada. Digite Valores
de Frete e dê um clique em OK.
Digite o nome da tabela a ser criada
pela Consulta criar tabela.
Feito isso vá para o modo folha de dados e observe
o número de registros. Estes serão os registros
que irão estar na tabela Valores de Frete, quando esta
for criada ao "executarmos" a consulta.
Volte para o modo estrutura e execute a consulta (dando um
clique no botão com o ponto de exclamação
vermelho). Como a tabela ainda não existe, o Microsoft
Access executa a consulta sem nenhuma mensagem de aviso.
Feche a consulta e dê um clique na guia Tabelas. Verifique
se foi criada uma tabela com o nome de Valores de Frete.
Abra esta tabela e observe que foram adicionados somente os
registros cujo Ano da DataDoPedido é igual a 1994, conforme
havíamos definido na consulta Criar Pedidos 1994.
Nunca é demais salientar que para que a consulta seja executada,
não basta ir para o modo folha de dados, precisamos clicar
no botão executar consultas.Além disso se executarmos
a consulta novamente, o Microsoft Access elimina a tabela criada
anteriormente e cria uma nova tabela com o resultado da consulta.
Dê um clique na guia Consultas e observe que o Microsoft Access
97 usa diferentes ícones para os diferentes tipos de consultas,
conforme indicado na figura abaixo:
Diferentes ícones para os diferentes
tipos de consultas.
Marque a consulta Criar Pedidos 1994 e dê um clique no botão
Estrutura, para abrí-la no modo estrutura.
Dê um clique no botão executar consulta. Como já
existe a tabela Valores de Frete, o Microsoft Access avisa que a
tabela existente será excluída e uma nova será
criada, conforme indicado pela figura abaixo. Basta clicar em sim
que a tabela existente será excluída e a nova será
criada.
O Microsoft Access pedindo confirmação
para excluir a tabela existente.
Ao clicar em Sim, a tabela anterior será excluída
e uma nova tabela será criada, a qual conterá os registros
retornados como resultado da consulta.
Vamos exercitar um pouco mais.
Exemplo 02: Criar uma consulta
do tipo Consulta atualização, a qual altere o valor
do campo Frete da tabela Pedidos, da seguinte maneira:
Frete menor ou igual a 15 -> Aumento
de 10 %
Frete maior do que 15 -> Aumento de 20 %
Para criar a consulta proposta faça o seguinte:
Crie uma nova consulta baseada na tabela Pedidos
Adicione o campo Frete. Observe que estamos adicionando apenas o
campo que queremos atualizar.
Transforme a consulta em uma Consulta atualização.
Para isso dê um clique no menu Consulta
e depois clique na opção Consulta atualização.
Observe que uma nova linha Atualizar para foi adicionada a estrutura
da consulta
Salve a consulta com o nome de Atualiza Frete
Na Linha Atualizar Para, coloque a seguinte expressão:
SeImed([Frete]<=15;[Frete]*1,1;[Frete]*1,2)
Utilizamos novamente a função Selmed para testarmos
se o frete for menor ou igual a 15, aumentamos em 10 % ( [Frete]*1,1),
caso contrário aumentamos em 20 % ( [Frete]*1,2).
Vá para o modo Folha de Dados e Observe os Valores do Frete.
Ao ir para o modo Folha de Dados a consulta ainda não foi
executada e portanto, os valores do frete ainda não foram
atualizados. Para atualizar os valores, precisamos executar a consulta,
clicando no botão com o Ponto de Interrogação
vermelho.
Volte para o modo estrutura e dê um clique no botão
Executar. O Microsoft Access executa a consulta e atualiza os valores.
Observe que nenhuma mensagem é emitida.
Voltando para o modo estrutura você poderá constatar
que os valores do frete foram alterados em relação
aos valores anteriores, conforme definido na consulta.
Nunca é demais salientar que devemos tomar cuidado com as
consultas de atualização. Se executarmos novamente
a consulta deste exemplo, mais 10 % será adicionado aos registros
com frete menor ou igual a 15 e mais 20 % será adicionado
aos registros com frete maior do que 15. Cada vez que a consulta
é executada, os registros são alterados, conforme
definido pela consulta.
Por isso que é uma prática comum não salvar
as consultas do tipo atualização. Apenas criamos as
consultas, executamos uma vez conforme a necessidade e saímos
sem salvar, para que não haja o perigo de executarmos a consulta
mais vezes, o que seria indesejável.
Também nunca é demais salientar que a consulta de
atualização precisa ser executada, não basta
ir para o modo Folha de Dados.
Exemplo 03:
Crie uma consulta com os campos NúmeroDoPedido, NomeDaEmpresa
e Frete, a qual filtre apenas os Pedidos para 1995. Transforme esta
consulta em uma consulta de acréscimo, a qual adiciona os
registros de 1995 aos registros já existentes de 1994 na
tabela Valores de Frete.
Para criar a consulta proposta faça o seguinte:
Ao invés de criar a consulta a partir do zero, vamos utilizar
a consulta Criar Pedidos 1994 e apenas alterar o critério
para 1995. Vamos salvar a consulta com o nome de Criar Pedidos 1994.
Para isso, marque a consulta Criar Pedidos 1994 e dê um clique
no botão Estrutura. Altere o critério na coluna Ano([DataDoPedido])
para 1995. Salve a consulta como Anexar Pedidos 1995. Para isso
dê um clique no menu Arquivo e um clique na Opção
Salvar como/exportar. Na janela que surge digite o nome da consulta
Anexar Pedidos 1995, conforme indicado na figura abaixo:
Salvando a consulta com o nome Anexar
Pedidos 1995.
Transforme esta consulta em uma consulta de Anexação.
Para isso dê um clique no menu Consulta e um clique na opção
Consulta acréscimo. O Microsoft Access abre uma janela perguntando
a qual consulta você deseja anexar os registros com os pedidos
de 1995. Escolha Valores de Frete, conforme indicado na figura abaixo:
Escolha o nome da Tabela onde serão
anexados os registros de 1995.
Para que os registros sejam anexados, você precisa executar
a consulta. Para isso dê um clique no botão executar.
O Microsoft Access anexa os registros de 1995 aos registros de 1994
já existentes na tabela Valores de Frete.
Observe no modo estrutura, como os campos estão na mesma
ordem, tanto na consulta quanto na tabela, o Microsoft Access já
detecta qual campo corresponde a qual campo, conforme indicado na
figura abaixo:
Associação entre os campos
da Consulta e da Tabela.
Observe que o campo Ano([DataDoPedido]) não é incluído
na tabela Valores de Frete, pois o mesmo não é nem
sequer exibido na consulta, pois funciona apenas como um filtro
para a consulta.
Também temos que ter o cuidado de não executar
a consulta mais do que uma vez, pois senão, cada vez
que a consulta for executada, todos os pedidos de 1995 serão
anexados a tabela Valores de Frete, o que causará duplicação
dos dados.
Feche a consulta de Anexação e observe na figura abaixo
os diversos tipos de consultas, indicados pelos diferentes ícones.
Nos veremos no ítem sobre macros, no curso de Access Avançado
(entre março e junho de 2003) a automação de
tarefas através do uso de Macros e consultas do tipo acréscimo,
criar tabela, atualização e exclusão. Por exemplo,
a medida que o tempo passa pode ser necessário fazer um arquivamento
mensal dos Pedidos. Isso pode ser feita através de uma consulta
de anexação que anexa os pedidos do mês que
passou a tabela de arquivamento , seguida de uma consulta que exclui
os pedidos que foram arquivados. Estas consultas podem executar
em seqüência através da utilização
de uma Macro, a qual pode ser programada para executar todo dia
1º de cada mês.
Exemplo 04:
Criar uma consulta que exclua todos os Pedidos cujo PaísDeDestino
seja Dinamarca.
Para criar a consulta proposta faça o seguinte:
Crie uma nova consulta baseada na tabela Pedidos
Adicione o campo PaísDeDestino
Transforme a consulta em uma Consulta exclusão. Para isso
dê um clique no menu consulta e depois um clique na opção
Consulta exclusão.
Na linha critério do campo PaísDeDestino, digite Dinamarca.
É muito importante que você não esqueça
o critério, pois caso contrário todos os Pedidos serão
eliminados.
Para excluir os registros você precisa executar a consulta.
Dê um clique no botão executar. O Microsoft Access
informa quantos registros serão excluídos e pede confirmação.
Salve a consulta com o nome de Exclui Pedidos
Feche a consulta, vá para a guia Tabelas e abra a tabela
Pedidos. Verifique se realmente os Pedidos para Dinamarca foram
excluídos.
Você deve ter bastante cuidado na hora de especificar os critérios
em uma consulta de exclusão. Antes de executar a consulta
e realmente excluir os registros, você pode ir para o Modo
Folha de dados e observar os registros que o Microsoft Access retorna,
pois são estes os registros que serão excluídos
quando a consulta for executada.
Pode acontecer de você receber uma mensagem de erro na hora
de executar uma consulta de exclusão. Normalmente estas mensagens
estão ligadas a problemas nos relacionamentos. Por exemplo,
se você estiver excluindo clientes do Brasil e o relacionamento
entre Clientes e Pedidos estiver configurado para Propagar exclusões
em cascata, o Microsoft Access tentará excluir os Pedidos
(na tabela Pedidos) dos clientes que estão sendo excluídos.
Se a tabela Pedidos estiver em uso por alguma outra consulta ou
formulário, o Microsoft Access não conseguirá
excluir os pedidos e retornará uma mensagem de erro. Isso
aumenta em complexidade, uma vez que a tabela Pedidos se relaciona
com Detalhes do Pedido, a qual poderia estar em uso e assim por
diante.
Vamos exercitar um pouco para que possamos fixar os conceitos apresentados
neste tópico.
EXERCÍCIOS:
01) Criar uma consulta do tipo Criar Tabela, a qual crie um tabela
com os campos NúmeroDoPedido, NomeDaEmpresa, DataDoPedido,
DataDeEntrega e PaísDeDestino. O Nome da Tabela será
"Pedidos Demora Entrega". A consulta deverá selecionar
apenas os registros cuja diferença entre a DataDeEntrega
e DataDoPedido seja maior do que 25 dias.
02) Criar uma consulta de Atualização que altere o
campo PreçoUnitário na tabela Produtos da seguinte
maneira: Se o PreçoUnitário for menor do que R$ 20,00,
terá uma aumento de 5 %, caso contrário o aumento
será de 10 %.
03) Criar uma consulta de anexação que anexa a tabela
Valores de Frete, os Pedidos de 1996. Lembre que esta tabela possui
os campos NúmeroDoPedido, NomeDaEmpresa e Frete.
04) Criar uma consulta de exclusão a qual elimina todos os
Pedidos com valor de Frete inferior a R$ 10,00 da tabela Valores
de Frete.
OBSERVAÇÕES SOBRE O TUTORIAL:
O tutorial é composto de teoria e exemplos práticos,
passo-a-passo. Para acompanhar todas as lições desse
tutorial, você deve copiar o arquivo curso_av.mdb, o qual
está disponível para Download, no formato compactado
(.zip), no endereço a seguir:
CLIQUE AQUI PARA COPIAR O
ARQUIVO NECESSÁRIO PARA ACOMPANHAR OS EXEMPLOS DO TUTORIAL
Para acompanhar os exemplos desse tutorial você já
deve conhecer os conceitos básicos de criação
de consultas no Microsoft Access. Para detalhes sobre a criação
de consultas básicas no Microsoft Access, consulte o Curso
Básico de Access. Um bom estudo a todos.
Em caso de dúvidas, sobre o conteúdo e os exemplos
do tutorial, ou para enviar sugestões sobre novos tópicos
a serem incluídos, entre em contato através do e-mail:
webmaster@juliobattisti.com.br.
Somente serão respondidas dúvidas referentes às
questões e tópicos contidos no tutorial. Não
serão respondidas dúvidas sobre tópicos gerais
do Microsoft Access ou sobre sistemas em desenvolvimento.
Clique aqui para voltar ao início
da página
|
