O Microsoft Access é, indiscutivelmente, a ferramenta mais poderosa em todo o pacote do Microsoft Office, mas isso confunde (e às vezes assusta) os usuários avançados do Office. Com uma curva de aprendizado mais íngreme do que o Word ou o Excel, como é que alguém deveria encobrir o uso dessa ferramenta? Esta semana, Bruce Epper vai olhar para alguns dos problemas suscitados por esta questão de um dos nossos leitores.
Um leitor pergunta:
Estou tendo problemas para escrever uma consulta no Microsoft Access.
Eu tenho um banco de dados com duas tabelas de produtos contendo uma coluna comum com um código de produto numérico e um nome de produto associado.
Desejo descobrir quais produtos da Tabela A podem ser encontrados na Tabela B. Quero adicionar uma coluna denominada Resultados que contenha o nome do produto da Tabela A, se existir, e o nome do produto da Tabela B, quando ele não existir na Tabela A.
Você tem algum conselho?
Resposta de Bruce:
O Microsoft Access é um Sistema de Gerenciamento de Banco de Dados (DBMS) projetado para uso em máquinas Windows e Mac. Ele utiliza o mecanismo de banco de dados Jet da Microsoft para processamento e armazenamento de dados. Ele também fornece uma interface gráfica para os usuários, o que praticamente elimina a necessidade de entender a Linguagem de Consulta Estruturada (SQL).
SQL é a linguagem de comando usada para adicionar, excluir, atualizar e retornar informações armazenadas no banco de dados, bem como modificar componentes do banco de dados, como adicionar, excluir ou modificar tabelas ou índices.
Ponto de partida
Se você ainda não tem alguma familiaridade com o Access ou outro RDBMS, sugiro que você comece com esses recursos antes de prosseguir:
- Então, o que é um banco de dados? Então, o que é um banco de dados, afinal? [MakeUseOf Explains] Então, o que é um banco de dados, afinal? [MakeUseOf Explains] Para um programador ou um entusiasta de tecnologia, o conceito de banco de dados é algo que pode realmente ser considerado como garantido. No entanto, para muitas pessoas, o conceito de banco de dados em si é um pouco estranho .... Leia mais onde o Ryan Dube usa o Excel para mostrar os fundamentos dos bancos de dados relacionais.
- Um guia rápido para começar com o Microsoft Access 2007 Um guia rápido para começar com o Microsoft Access 2007 Um guia rápido para começar com o Microsoft Access 2007 Leia mais, que é uma visão geral de alto nível do Access e os componentes que compõem um banco de dados do Access.
- Um rápido tutorial para tabelas no Microsoft Access 2007 Um rápido tutorial para tabelas no Microsoft Access 2007 Um tutorial rápido para tabelas no Microsoft Access 2007 Leia mais analisa a criação de seu primeiro banco de dados e tabelas para armazenar seus dados estruturados.
- Um rápido tutorial sobre consultas no Microsoft Access 2007 Um rápido tutorial sobre consultas no Microsoft Access 2007 Um rápido tutorial sobre consultas No Microsoft Access 2007, o Read More examina as médias para retornar partes específicas dos dados armazenados nas tabelas do banco de dados.
Ter uma compreensão básica dos conceitos fornecidos nesses artigos tornará o seguinte um pouco mais fácil de digerir.
Relações de banco de dados e normalização
Imagine que você está administrando uma empresa que vende 50 tipos diferentes de widgets em todo o mundo. Você tem uma base de clientes de 1.250 e, em um mês médio, vende 10.000 widgets para esses clientes. Atualmente, você está usando uma única planilha para acompanhar todas essas vendas - efetivamente, uma única tabela de banco de dados. E todo ano adiciona milhares de linhas à sua planilha.
As imagens acima fazem parte da planilha de acompanhamento de pedidos que você está usando. Agora diga que ambos os clientes compram widgets de você várias vezes por ano, então você tem muito mais linhas para os dois.
Se Joan Smith se casar com Ted Baines e tomar seu sobrenome, cada linha que contenha seu nome precisa ser mudada. O problema é agravado se você tiver dois clientes diferentes com o nome 'Joan Smith'. Acabou de se tornar muito mais difícil manter seus dados de vendas consistentes devido a um evento bastante comum.
Usando um banco de dados e normalizando os dados, podemos separar itens em várias tabelas, como estoque, clientes e pedidos.
Apenas olhando para a parte cliente do nosso exemplo, removeríamos as colunas para Nome do cliente e Endereço do cliente e as colocaríamos em uma nova tabela. Na imagem acima, também resolvi melhor as coisas para obter um acesso mais granular aos dados. A nova tabela também contém uma coluna para uma chave primária (ClientID) - um número que será usado para acessar cada linha nesta tabela.
Na tabela original em que removemos esses dados, adicionaríamos uma coluna para uma chave estrangeira (ClientID), que é o link para a linha correta contendo as informações desse cliente específico.
Agora, quando Joan Smith muda seu nome para Joan Baines, a mudança só precisa ser feita uma vez na tabela Cliente. Todas as outras referências de tabelas unidas irão puxar o nome do cliente e um relatório que está procurando o que Joan comprou nos últimos 5 anos receberá todos os pedidos sob seus nomes de solteira e casados sem ter que mudar como o relatório é gerado .
Como benefício adicional, isso também reduz a quantidade total de armazenamento consumido.
Tipos de junção
O SQL define cinco tipos diferentes de junções: INNER, LEFT OUTER, RIGHT EXTER, FULL OUTER e CROSS. A palavra-chave OUTER é opcional na instrução SQL.
O Microsoft Access permite o uso de INNER (padrão), LEFT OUTER, RIGHT OUTER e CROSS. FULL OUTER não é suportado como tal, mas usando LEFT OUTER, UNION ALL e RIGHT OUTER, ele pode ser falsificado ao custo de mais ciclos de CPU e operações de E / S.
A saída de uma união CROSS contém todas as linhas da tabela à esquerda, emparelhadas com todas as linhas da tabela da direita. A única vez que vi uma associação CROSS usada é durante o teste de carga de servidores de banco de dados.
Vamos dar uma olhada em como as junções básicas funcionam, então as modificaremos para atender às nossas necessidades.
Vamos começar criando duas tabelas, ProdA e ProdB, com as seguintes propriedades de design.
O AutoNumeração é um número inteiro longo de incremento automático atribuído às entradas conforme elas são adicionadas à tabela. A opção Texto não foi modificada, por isso aceitará uma cadeia de texto com até 255 caracteres.
Agora, preencha-os com alguns dados.
Para mostrar as diferenças em como os 3 tipos de junção funcionam, excluí as entradas 1, 5 e 8 do ProdA.
Em seguida, crie uma nova consulta indo em Criar> Design de Consulta . Selecione ambas as tabelas na caixa de diálogo Mostrar tabela e clique em Adicionar e, em seguida, em Fechar .
Clique em ProductID na tabela ProdA, arraste-o para ProductID na tabela ProdB e solte o botão do mouse para criar o relacionamento entre as tabelas.
Clique com o botão direito do mouse na linha entre as tabelas que representam o relacionamento entre os itens e selecione Join Properties .
Por padrão, o tipo de junção 1 (INNER) é selecionado. A opção 2 é uma união LEFT OUTER e 3 é uma união RIGHT OUTER.
Vamos olhar primeiro para a junção INNER, então clique em OK para descartar o diálogo.
No designer de consulta, selecione os campos que queremos ver nas listas suspensas.
Quando executamos a consulta (o ponto de exclamação vermelho na faixa de opções), ele mostrará o campo ProductName de ambas as tabelas com o valor da tabela ProdA na primeira coluna e ProdB na segunda.
Observe que os resultados mostram apenas valores em que ProductID é igual nas duas tabelas. Embora exista uma entrada para ProductID = 1 na tabela ProdB, ela não aparece nos resultados, pois ProductID = 1 não existe na tabela ProdA. O mesmo se aplica ao ProductID = 11. Ele existe na tabela ProdA, mas não na tabela ProdB.
Usando o botão Exibir na faixa de opções e alternando para o SQL View, você pode ver a consulta SQL gerada pelo designer usada para obter esses resultados.
SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA INNER JOIN ProdB ON ProdA.ProductID = ProdB.ProductID; Voltando ao Design View, altere o tipo de junção para 2 (LEFT OUTER). Execute a consulta para ver os resultados.
Como você pode ver, todas as entradas na tabela ProdA são representadas nos resultados, enquanto apenas as que estão em ProdB que possuem uma entrada ProductID correspondente na tabela ProdB aparecem nos resultados.
O espaço em branco na coluna ProdB.ProductName é um valor especial (NULL), pois não há um valor correspondente na tabela ProdB. Isso será importante mais tarde.
SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA LEFT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID; Tente a mesma coisa com o terceiro tipo de junção (DIREITA EXTERIOR).
Os resultados mostram tudo a partir da tabela ProdB enquanto está mostrando valores em branco (conhecidos como NULL) em que a tabela ProdA não possui um valor correspondente. Até agora, isso nos aproxima dos resultados desejados na pergunta de nosso leitor.
SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID; Usando funções em uma consulta
Os resultados de uma função também podem ser retornados como parte de uma consulta. Queremos que uma nova coluna chamada 'Resultados' apareça no nosso conjunto de resultados. Seu valor será o conteúdo da coluna ProductName da tabela ProdA se ProdA tiver um valor (não é NULL), caso contrário, deverá ser obtido da tabela ProdB.
A função IF imediata (IIF) pode ser usada para gerar esse resultado. A função leva três parâmetros. A primeira é uma condição que deve ser avaliada como um valor Verdadeiro ou Falso. O segundo parâmetro é o valor a ser retornado se a condição for True e o terceiro parâmetro for o valor a ser retornado se a condição for False.
A construção completa da função para a nossa situação é assim:
IIF(ProdA.ProductID Is Null, ProdB.ProductName, ProdA.ProductName) Observe que o parâmetro de condição não verifica a igualdade. Um valor Nulo em um banco de dados não possui um valor que possa ser comparado a qualquer outro valor, incluindo outro Nulo. Em outras palavras, Nulo não é igual a Nulo. Sempre. Para superar isso, nós verificamos o valor usando a palavra-chave 'Is'.
Poderíamos também ter usado 'Is Not Null' e alterado a ordem dos parâmetros True e False para obter o mesmo resultado.
Ao colocar isso no Criador de Consultas, você deve digitar toda a função na entrada Campo :. Para conseguir criar a coluna "Resultados", você precisa usar um alias. Para fazer isso, preencha a função com 'Resultados:', como mostrado na captura de tela a seguir.
O código SQL equivalente para fazer isso seria:
SELECT ProdA.ProductName, ProdB.ProductName, IIF(ProdA.ProductID Is Null, ProdB.ProductName, ProdA.ProductName) AS Results FROM ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID; Agora, quando executarmos essa consulta, produziremos esses resultados.
Aqui vemos para cada entrada em que a tabela ProdA tem um valor, esse valor é refletido na coluna Resultados. Se não houver uma entrada na tabela ProdA, a entrada de ProdB aparecerá em Results, que é exatamente o que o nosso leitor pediu.
Para obter mais recursos para aprender sobre o Microsoft Access, confira Como aprender sobre o Microsoft Access: 5 Recursos on-line gratuitos Como aprender sobre o Microsoft Access: 5 Recursos on-line gratuitos Como aprender sobre o Microsoft Access: 5 Recursos on-line gratuitos Você precisa gerenciar uma grande quantidade De dados? Você deve procurar no Microsoft Access. Nossos recursos de estudo gratuitos podem ajudá-lo a começar e aprender as habilidades para projetos mais complexos. Consulte Mais informação .