Home-theater-designers
O Microsoft Access é indiscutivelmente a ferramenta mais poderosa em todo o pacote Microsoft Office, mas ainda assim confunde (e às vezes assusta) os usuários avançados do Office. Com uma curva de aprendizado mais íngreme do que o Word ou Excel, como alguém pode imaginar o uso dessa ferramenta? Esta semana, Bruce Epper analisará alguns dos problemas estimulados por esta pergunta de um de nossos leitores.
Um leitor pergunta:
Estou tendo problemas para escrever uma consulta no Microsoft Access. 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. Quero descobrir quais produtos da Tabela A podem ser encontrados na Tabela B. Desejo adicionar uma coluna chamada Resultados que contém o nome do produto da Tabela A, se existir, e o nome do produto da Tabela B, quando não existe 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 usuários que quase elimina a necessidade de entender Structured Query Language (SQL).
SQL é a linguagem de comando usada para adicionar, excluir, atualizar e retornar informações armazenadas no banco de dados, bem como modificar os componentes principais 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 comece com estes recursos antes de prosseguir:
- Então, o que é um banco de dados? onde Ryan Dube usa o Excel para mostrar o básico dos bancos de dados relacionais.
- Um guia rápido para começar a usar o Microsoft Access 2007 que é uma visão geral de alto nível do Access e dos componentes que compõem um banco de dados do Access.
- Um tutorial rápido para tabelas no Microsoft Access 2007 dá uma olhada na criação de seu primeiro banco de dados e tabelas para armazenar seus dados estruturados.
- Um tutorial rápido sobre consultas no Microsoft Access 2007 examina os meios de retornar partes específicas dos dados armazenados nas tabelas do banco de dados.
Ter uma compreensão básica dos conceitos fornecidos nestes artigos tornará o seguinte um pouco mais fácil de digerir.
Relações e normalização de banco de dados
Imagine que você dirige 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 vende, em média, 10.000 widgets para esses clientes. No momento, você está usando uma única planilha para rastrear todas essas vendas - na verdade, uma única tabela de banco de dados. E todos os anos adiciona milhares de linhas à sua planilha.
As imagens acima fazem parte da planilha de rastreamento de pedidos que você está usando. Agora, digamos que ambos os clientes comprem widgets de você várias vezes por ano, de forma que você tenha muito mais linhas para ambos.
Se Joan Smith se casar com Ted Baines e usar seu sobrenome, cada linha que contém o nome dela agora precisa ser alterada. O problema é agravado se acontecer de você ter dois clientes diferentes com o nome 'Joan Smith'. Acabou de se tornar muito mais difícil manter os dados de vendas consistentes devido a um evento bastante comum.
Usando um banco de dados e normalizando os dados, podemos separar os itens em várias tabelas, como estoque, clientes e pedidos.
Apenas olhando para a parte do cliente de nosso exemplo, removeríamos as colunas de Nome do cliente e Endereço do cliente e as colocaríamos em uma nova tabela. Na imagem acima, também separei as coisas melhor para 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 que se vincula à linha apropriada contendo as informações para este cliente específico.
Agora, quando Joan Smith muda seu nome para Joan Baines, a mudança só precisa ser feita uma vez na mesa de Clientes. Todas as outras referências de tabelas unidas obterão o nome do cliente adequado e um relatório que analisa o que Joan comprou nos últimos 5 anos obterá todos os pedidos com seus nomes de solteira e casada, sem ter que alterar a forma como o relatório é gerado .
Como um benefício adicional, isso também reduz a quantidade geral de armazenamento consumido.
Tipos de junção
O SQL define cinco tipos diferentes de junções: INNER, LEFT OUTER, RIGHT OUTER, 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, pode ser falsificado ao custo de mais ciclos de CPU e operações de E / S.
A saída de uma junção CROSS contém cada linha da tabela esquerda emparelhada com cada linha da tabela direita. A única vez que vi uma junção CROSS usada foi durante o teste de carga de servidores de banco de dados.
Vamos dar uma olhada em como as junções básicas funcionam e, em seguida, vamos modificá-las para atender às nossas necessidades.
Vamos começar criando duas tabelas, ProdA e ProdB, com as seguintes propriedades de design.
A AutoNumeração é um inteiro longo de incremento automático atribuído às entradas à medida que são adicionadas à tabela. A opção Texto não foi modificada, por isso aceitará uma string de texto de até 255 caracteres.
Agora, preencha-os com alguns dados.
Para mostrar as diferenças em como os três tipos de junção funcionam, excluí as entradas 1, 5 e 8 do ProdA.
Próximo, criar uma nova consulta indo para Criar> Design de Consulta . Selecione ambas as tabelas na caixa de diálogo Mostrar Tabela e clique em Adicionar , então 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 na linha entre as tabelas que representam a relação entre os itens e selecione Propriedades de União .
Por padrão, o tipo de junção 1 (INNER) é selecionado. A opção 2 é uma junção LEFT OUTER e 3 é uma junção RIGHT OUTER.
Vamos examinar a junção INNER primeiro, então clique em OK para descartar a caixa de 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), ela 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 onde ProductID é igual em ambas as tabelas. Embora haja 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 a ProductID = 11. Ele existe na tabela ProdA, mas não na tabela ProdB.
Ao usar o botão Visualizar na faixa de opções e alternar para a Visualização SQL, 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 para a Visualização de design, altere o tipo de junção para 2 (LEFT OUTER). Execute a consulta para ver os resultados.
Como você pode ver, cada entrada na tabela ProdA é representada nos resultados, enquanto apenas aquelas no ProdB que têm 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 (RIGHT OUTER).
Os resultados mostram tudo da tabela ProdB enquanto ela mostra valores em branco (conhecidos como NULL) onde a tabela ProdA não tem um valor correspondente. Até agora, isso nos aproxima dos resultados desejados na pergunta do 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 em 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, ele deve ser obtido da tabela ProdB.
A função IF Imediato (IIF) pode ser usada para gerar este resultado. A função leva três parâmetros. A primeira é uma condição que deve ser avaliada como verdadeiro ou falso. O segundo parâmetro é o valor a ser retornado se a condição for True, e o terceiro parâmetro é o valor a ser retornado se a condição for False.
A construção de função completa para 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 tem um valor que possa ser comparado a qualquer outro valor, incluindo outro Nulo. Em outras palavras, Null não é igual a Null. Sempre. Para superar isso, em vez disso, verificamos o valor usando a palavra-chave 'Is'.
Também poderíamos ter usado 'Is Not Null' e alterado a ordem dos parâmetros True e False para obter o mesmo resultado.
Ao colocar isso no Query Designer, você deve digitar a função inteira na entrada Campo :. Para fazer com que ele crie a coluna 'Resultados', você precisa usar um alias. Para fazer isso, inicie a função com 'Resultados:', conforme visto na imagem 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, ela produzirá esses resultados.
aplicativos de mensagens de texto grátis para tablets Android
Aqui, vemos que para cada entrada em que a tabela ProdA possui um valor, esse valor é refletido na coluna Resultados. Se não houver uma entrada na tabela ProdA, a entrada do ProdB aparecerá em Resultados, que é exatamente o que nosso leitor pediu.
Para obter mais recursos para aprender sobre o Microsoft Access, consulte o artigo de Joel Lee, como aprender sobre o Microsoft Access: 5 recursos online gratuitos.
Compartilhado Compartilhado Tweet O email Vale a pena atualizar para o Windows 11?
O Windows foi redesenhado. Mas isso é suficiente para convencê-lo a mudar do Windows 10 para o Windows 11?
Leia a seguir Tópicos relacionados- Produtividade
- Pergunte aos especialistas
Bruce brinca com eletrônica desde os anos 70, computadores desde o início dos anos 80 e responde com precisão a perguntas sobre tecnologia que não usou nem viu o tempo todo. Ele também se irrita tentando tocar violão.
Mais de Bruce EpperAssine a nossa newsletter
Junte-se ao nosso boletim informativo para dicas de tecnologia, análises, e-books grátis e ofertas exclusivas!
Clique aqui para se inscrever