AS EMPRESAS ESTÃO "DESESPERADAS" POR ESTE TIPO DE PROFISSIONAL... - VOCÊ É UM DELES?
MEGA FORMAÇÃO EM INFRAESTRUTURA DE TI - O Conhecimento que Vira Dinheiro - CLIQUE AQUI
| « Anterior | Δ Página principal | ¤ Índice | Próxima » |
| Curso Grátis - Access 2007 Avançado, Macros e Programação VBA Autor: Júlio Battisti |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Lição 04 - Capítulo 01 - T�cnicas Avan�adas em Consultas - Parte 1 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Neste item veremos diversas técnicas avançadas na Utilização de Consultas. Aprenderemos a adicionar colunas calculadas ao resultados das consultas, utilização de funções de Domínio para critérios em consultas, utilização de critérios avançados, dentre outras técnicas. Veremos os seguintes tópicos:
5.1 - Adição de Colunas com Valores Calculados:Vamos, inicialmente, relembrar alguns conceitos básicos sobre Consultas no Microsoft Access 2007. Para Criar uma consulta basta clicar na Guia Criar -> " Assistente de Consultas" ou “Design da Consulta” . Clique na segunda opção “Design da Consulta” será apresentado a tela indicada na figura abaixo:
Nesta caixa de diálogo é mostrado todas as tabelas do banco. E você terá que escolher qual ( ou quais ) tabela a Consulta será baseada. Para isso faça: Selecione a tabela que fará parte da consulta e clique no botão No nosso exemplo, adicionaremos a tabela “Detalhes do Pedido”. Após a adição da tabela, dê um clique no botão Será exibida a Janela indicada na figura a seguir, com uma consulta em Branco e a grade para a construção da consulta. O Microsoft Office Access 2007chama essa grade de QBE ( Query By Example ). Observe que a tabela "Detalhes Do Pedido" já aparece adicionada na parte superior. Caso você tenha esquecido de adicionar alguma tabela que seja necessária para a Consulta, não precisa iniciar o processo desde o início. Você pode adicionar uma consulta a qualquer momento, para isto, basta dar um clique no botão
Em cada coluna da parte inferior você adiciona os campos que irão aparecer na Consulta. Para adicionar um campo, basta dar um clique duplo sobre o nome do campo ou arrastá-lo até a coluna onde o mesmo será colocado. Os campos aparecerão na listagem na mesma ordem em que forem adicionadas na grade. Caso você , por engano, adicione um mesmo campo duas vezes, o mesmo irá aparecer duas vezes na listagem. Observe que na parte de baixo nos temos diversas linhas para cada campo. Na tabela abaixo segue a descrição de cada uma destas linhas.
Seguindo o nosso exemplo, adicionaremos a tabela Pedidos e depois passaremos a adicionar campos a nossa consulta. Adicione a tabela pedidos. Para isto dê um clique no botão
Classifique a listagem em ordem Crescente do campo Número do Pedido. A sua grade deve ficar como indicado na próxima figura:
NOTA: Até agora trabalhamos com a opção "Design da consulta”. Toda e qualquer alteração ( Inclusão de Campo, Critérios, Campos Calculados, etc) na consulta somente pode ser feita no Modo Design, ou diretamente através do código SQL conforme veremos mais tarde. Para ver o resultado da consulta que foi construída até o momento, basta dar um clique no botão Para o nosso exemplo, os resultados deverão ser semelhantes aos exibidos na próxima figura.
Através do botão Modo de exibição Total_Item = ([PreçoUnitário]*[Quantidade])*(1-[Desconto]) Ao efetuarmos cálculos, colocamos o nome dos campos que farão parte dos cálculos entre colchetes. Embora este procedimento somente seja obrigatório quando o nome dos campos possuir espaços em branco, é recomendável, por questão de clareza e padronização, que sempre utilizemos os colchetes. As quatro operações básicas são representadas pelos seguintes símbolos:
A Utilização dos parênteses embora não obrigatória, cumpre a função de deixar mais clara a ordem em que as operações serão aplicadas. Os parênteses também podem ser utilizados para definir a seqüência correta das operações. No nosso exemplo, primeiro multiplicamos [PreçoUnitário] pela quantidade, e o resultado é multiplicado com o resultado da operação ( 1 - [Desconto]). Para adicionar esta campo calculado, vá para a primeira coluna em branco, ao lado do campo [DataEntrega] e na linha Campo, digite: Total_Ìtem: ([PreçoUnitário]*[Quantidade])*(1-[Desconto]) O Texto que aparece antes dos dois pontos ( :) é simplesmente uma legenda para o campo, é o título que aparecerá para a coluna no modo Folha de Dados. O que vem depois dos dois pontos é o cálculo propriamente dito. Antes de vermos os resultados vamos formatar esta nova coluna calculada, para que ele exiba os dados no formato de moeda. Para isto faça o seguinte:
De volta ao Modo Design, vamos fazer mais uma alteração na nossa consulta. Vamos fazer com que o Microsoft Office Access 2007agrupo os registros de cada pedido e faça a soma de todos os ítens de cada pedido. Na listagem final teremos o número do pedido, código do cliente e data de entrega aparecendo uma única vez para cada pedido e no campo Calculada ( Total_Ítem ) teremos a soma de todos os ítens do pedido. Para realização deste processo faça o seguinte:
E se ao invés do total por pedido nos quiséssemos, simplesmente, contar o número de ítens por Pedido? Muito simples. Dê um clique no executar para voltar ao Modo Design, na linha Total, no Campo Total_Item, ao invés de Soma, selecione Contar. Clique com o direito sobre a coluna Total_Item, no menu que surge clique em propriedades. Altere a Propriedade formato para "Número Geral", dê um clique no "x" para fechar a janela de propriedades e pronto. Volte para o modo Folha de Dados e observe os resultados. Conforme indicado pela próxima figura, ao invés de somar os ítens de cada nota, o Microsoft Office Access 2007está contando o Número de Ítens em cada nota.
Estudamos neste tópico, como adicionar campos calculados a uma consulta. Para isto basta colocar em uma coluna em branco o seguinte:
Onde Nome_Coluna é simplesmente o nome que irá aparecer no cabeçalho da coluna e depois do dois pontos ( : ) a fórmula de cálculo. Podemos usar qualquer função do Microsoft Office Access 2007para a realização de Cálculos. O Microsoft Office Access 2007apresenta algumas centenas de funções para a realização de Cálculos. Durante este curso iremos ver uma série de funções. A referência completa de todas as funções pode ser encontrada no Ajuda que acompanha o Microsoft Access 2007. Clique no menu Ajuda (ícone localizado no lado direito superior Agora iremos fazer alguns exercícios sobre "Campos Calculados em Consultas". Vamos aprender a utilizar funções que manipulam valores de Data e de String.
Exercício: Neste exercício iremos fazer algumas alterações na Consulta "Totais por Pedido e Observar os Resultados obtidos." Estigante Para fazer algumas alterações na consulta "Totais por Pedido:" faça:
Dias: [DataEntrega]-[DataPedido]
Ano_Pedido: Ano([DataPedido])
Mês_Pedido: Mês([DataPedido])
1 ou 3 ou 8 ou 9
Neste tópico foi mostrado para você passo - a - passo com o objetivo de revisarmos alguns aspectos básicos das consultas. Nos próximos tópicos não iremos repetir todo o procedimento para criar uma consulta, adicionar campos e colocar critérios, vamos focar mais nos aspectos específicos de cada tópico, salientando a utilização e aplicabilidade de cada uma das técnicas que estiverem sendo apresentadas. 5.2 - Utilização de Critérios avançados, operadores e funções de domínioNeste tópico iremos apresentar diversas técnicas avançadas em Pesquisas com o Microsoft Access 2007. Aprenderemos a utilizar Operadores, tais como Like ( Como), In ( Em) , Between ( Entre); aprenderemos a utilizar os caracteres curinga "*" e "?". Também veremos a utilização das chamadas funções de agregação e domínio ( Mín, Máx, Dcount, etc). Vamos seguir a metodologia utilizada no tópico anterior, ou seja, vamos criando consultas de exemplo e explicando os critérios e técnicas utilizados. Usaremos como base para este tópico o Banco de Dados "curso_av.mdb". Para início de conversa vamos criar uma consulta que exiba somente os pedidos para os meses de Janeiro e Fevereiro de 2009 (DataPedido). Para criar a consulta proposta faça o seguinte:
(Mês([DataPedido])=1 Ou Mês([DataPedido])=2) E Ano([DataPedido])=2009
Agora vamos alterar um pouco a nossa Consulta para adicionar mais critérios. Vamos manter o critério adicionado no item anterior ( para filtrar apenas os registros dos meses de Janeiro e Fevereiro do ano de 2009) e adicionar mais um critério para que somente sejam exibidos os pedidos cuja CidadeDestino seja Franca-SP ou Salvador-BA. Para adicionar este critério faça o seguinte:
" Franca-SP " Ou " Salvador-BA "
OBS : Ao colocarmos critérios em mais de um campo - No nosso exemplo colocamos critérios nos campos DataPedido e CidadeDestino - O Microsoft Office Access 2007liga os critérios através de um conectivo "E". Isto significa que para um registro aparecer na listagem ele tem que satisfazer todos os critérios estabelecidos na linha critério, no nosso exemplo, o mês da DataPedido deve ser Janeiro ou Fevereiro, o ano deve ser 2009 e a CidadeDestino deve ser Franca-SP Ou Salvador-BA. Vamos continuar fazendo alterações na Consulta Pedidos. Agora vamos pedir para que o Microsoft Office Access 2007exiba uma listagem de todos os pedidos cujo mês seja Janeiro ou Fevereiro, o ano 2009 e a Cidade seja Franca-SP e também sejam exibidos, todos os Pedidos cuja Cidade seja Salvador-BA, independentemente da DataPedido. Para obter esta listagem faça o seguinte:
IMPORTANTE: Esta consulta tem como objetivo salientar a utilização da linha "Ou" no Modo Design da consulta. Critérios colocados nesta linha, são unidos com os critérios colocados na linha de cima através de um conectivo Ou. Por isso que deslocamos o critério Salvador-BA para esta linha. Com isso estamos dizendo ao Microsoft Office Access 2007para pesquisar todos os pedidos cuja mês da DataPedido for igual a Janeiro ou Fevereiro e ano igual a 2009, com CidadeDestino igual a Franca-SP ( Linha Critério) "Ou" os Pedidos cuja CidadeDestino for Salvador-BA, independentemente da DataPedido ( Linha Ou). Se colocarmos mais de um critério dentro da linha Ou ( em diferentes campos ), os mesmos são ligados através de um conectivo lógico E. Como regra geral podemos colocar da seguinte maneira: Critérios Dentro da mesma linha -> Ligados por um conectivo E Agora vamos adicionar mais alguns campos e começar a utilizar pesquisas mais avançadas. Inicialmente vamos adicionar o campo NomeDaEmpresa, da tabela Clientes ( Para isto precisaremos adicionar a tabela Clientes à nossa consulta). Depois utilizaremos o Operador Like para exibir somente os Pedidos para clientes cujo nome inicie pelas letras A, B, C, D, E, F ou G. Para criar a consulta proposta faça o seguinte:
Como "[A-G]*"
Existem alguns detalhes importantes a respeito da última consulta que devemos analisar:
Operador Like -> Pesquisa um determinado padrão em campos de texto; Algumas observações importantes sobre o uso do Like: Quando um intervalo de caracteres é especificado, estes devem aparecer em uma ordem de classificação crescente (do menor para o maior). [A-Z] é um padrão válido, mas [Z-A] não é. Um ponto de exclamação (!) no início de lista de caracteres significa que uma correspondência é realizada se qualquer caractere, com exceção daqueles em lista de caracteres, for encontrado. Quando utilizado fora de colchetes, o ponto de exclamação corresponde a si mesmo. Por exemplo o seguinte critérios Como "[!A-G]*", pediria para o Microsoft Office Access 2007listar todos os pedidos, menos aqueles cuja primeira letra do campo NomeDaEmpresa esteja na faixa de A até Z. Na prática o Microsoft Office Access 2007listaria apenas os pedidos cuja primeira letra do campo NomeDaEmpresa, fosse de H até Z. Você pode encontrar mais detalhes sobre o operador Like no Help do Microsoft Access 2007. Iremos ver mais alguns usos do mesmo, através de exemplos nas próximas consultas. Abaixo temos uma tabela com os caracteres curinga que podem ser utilizados com o operador Like: Caractere(s) em padrão Coincide com expressão; Vamos ver mais alguns exemplos de utilização do Operador Like. Utilize o Operador Like para obter uma listagem de todos os Pedidos para empresas que tenham as letras as letras "Sa" no início do campo NomeDaEmpresa. Para criar a consulta proposta faça o seguinte:
Observe que se o NomeDaEmpresa possuir "BE" em qualquer outra parte, que não seja o início do nome, este critério não irá pegar. Além disso experimente colocar o "BE" todo em letra minúsculas. Você verá que para o Microsoft Office Access 2007não fará diferença. Observe, também, que mesmo que você digitar Like no critério, após sair da linha critério, o Microsoft Office Access 2007troca o Like por um Como ( Tradução de Like para o Português). Vamos aprimorar um pouco mais a utilização e o nosso conhecimento sobre o operador Like (Como). Crie uma consulta que selecione apenas os Pedidos para as empresas cujo campo NomeDaEmpresa possua a palavra "Th" em qualquer parte do nome. Para criar a consulta proposta faça o seguinte:
Como "*Th*"
Observe a maneira como utilizamos o operador Como: Como "*Th*" . Com a utilização deste critério, estamos informando ao Microsoft Office Access 2007que desejamos uma listagem somente dos pedidos para as empresas que possuam a palavra Th em qualquer parte do nome, não importando o que vem antes ( * ) ou o que vem depois ( * ). Esta é uma das utilizações mais comuns para o operador Como. Por exemplo, se você tiver um campo NomeDoContribuinte e quiser pesquisar todos os contribuintes que tenham a palavra José no nome ( José Da Silva, Maria José, Aparecido José, etc), bastaria colocar o seguinte critério no campo NomeDoContribuinte: Como "*José*". Caso você desejasse apenas aqueles que possuem Da Silva no final do nome ( José da Silva, Maria Aparecida da Silva, Antônio Carlos da Silva , etc), bastaria colocar o seguinte critério: Como "*Da Silva". Observe que não vai o * depois do Da Silva, pois queremos somente aqueles em que o Da Silva aparece no final do nome e não em qualquer parte do nome. Vamos continuar explorando a utilização do Operador Como, agora em conjunto com outros operadores ( Ou e E) Crie uma consulta que selecione apenas os Pedidos para as empresas cujo campo NomeDaEmpresa possua a palavra "Th" em qualquer parte do nome e que o NomeDaEmpresa começa com as letras M ou N. Para criar a consulta proposta faça o seguinte:
Como "*Th*" E Como "[M-N]*"
A primeira parte de critério é igual a utilizada na consulta anterior ( Como "*Th*" ), a qual pesquisa todas as empresas que contenham a palavra Th em qualquer parte do nome. A Segunda parte de critério ( Como "[M-N]*"), ligada a primeira parte pelo operador E, indica que a primeira letra do nome deve estar na faixa de M até N ([M-N]), não importando o que vem depois (*). Como as duas partes estão ligadas pelo operador E, ambas as condições devem ser satisfeitas para que o registro seja selecionado, isto é, o NomeDaEmpresa deve ter a palavra Th em qualquer parte do nome e também deve iniciar com a letra M ou com a Letra N, para que seja selecionado. A seguir um resumo sobre a utilização dos caracteres curingas: Sobre utilizar caracteres curinga para procurar valores parciais ou coincidentes. Você utiliza caracteres curinga como marcadores de outros caracteres quando você está especificando um valor que deseja localizar e conhece apenas parte do valor. Deseja localizar valores que comecem com uma letra específica ou coincidam com um determinado padrão. Você pode utilizar os caracteres a seguir nas caixas de diálogo Localizar e Substituir, ou em consultas, comandos e expressões, para localizar coisas tais como valores de campo, registros ou nomes de arquivo.
Observações: Ao utilizar caracteres curinga para procurar um asterisco (*), um ponto de interrogação (?), um sinal numérico (#), um colchete de abertura ([) ou um hífen (-), você deve colocar o item que você está procurando entre colchetes. Por exemplo, para procurar um ponto de interrogação, digite [?] na caixa de diálogo Localizar. Quando você estiver procurando um hífen e outros caracteres simultaneamente, coloque o hífen antes ou depois de todos os outros caracteres dentro dos colchetes. (Entretanto, se houver um ponto de exclamação (!) depois do colchete de abertura, coloque o hífen depois do ponto de exclamação). Quando você estiver procurando um ponto de exclamação ou um colchete de fechamento, não é necessário colocá-los entre colchetes. Você não pode procurar os colchetes de abertura e fechamento ([ ]) juntos porque o Microsoft Office Access 2007interpreta esta combinação como uma seqüência de comprimento zero. Se você estiver procurando valores em uma tabela que não seja do Microsoft Access 2007, como, por exemplo, uma tabela do Microsoft SQL Server, pode ser necessário utilizar caracteres curinga diferentes. Verifique a documentação da fonte de dados para obter maiores informações. Vamos construir mais algumas consultas para fixar bem a utilização do Operador Como. Crie uma consulta que selecione apenas os Pedidos para as empresas cujo campo NomeDaEmpresa inicie com a letra C e termina com a letra E. Para criar a consulta proposta faça o seguinte:
Como "C*O"
Muitas vezes, na utilização do operador Como existem duas ou mais maneiras de obter o mesmo resultado. Por exemplo, se você quiser uma listagem de todas as empresas cujo nome inicie na faixa de A até F. Você pode usar o critério tradicional: Como "[A-F]*". Outra maneira é informar ao Microsoft Office Access 2007que você quer todas as empresas, menos aquelas cuja primeira letra esteja na faixa de G até Z. Neste caso você usaria o seguinte critério: Como "[!G-Z]*" , o qual informa ao Microsoft Office Access 2007para listar todas as empresas que Não Iniciem com a primeira letra na faixa de G até Z, que é o mesmo que dizer para trazer as que iniciem na faixa da A até F. O Ponto de exclamação é que informa o "Não". Você pode combinar operadores Como, utilizando o Operador E e o operador Ou. Vamos explorar um pouco mais a utilização do Operador Como, devido a sua grande utilização e importância. Crie uma consulta que selecione apenas os Pedidos para as empresas cujo campo NomeDaEmpresa inicie com a letra B, a terceira letra seja um A ou todos os Pedidos para as Companhias cuja última letra do nome seja N, independente da letra inicial e da terceira letra. Para criar a consulta proposta faça o seguinte:
Como "C?A*" Ou Como "*O"
Observe a utilização do Ou ao invés do E. A Primeira parte ( Como "C?A" )diz para o Microsoft Office Access 2007trazer a listagem dos que tem na primeira letra um C, não importa o que tem na segunda ( ? ) e na terceira um A . Já a segunda parte do critério ( Como "O*" ), informa ao Microsoft Office Access 2007para listar aquelas empresas cuja nome termina com a letra O. Como os dois critérios estão ligados por um Ou, isto significa que para estar na listagem, basta que o NomeDaEmpresa atenda a um dos critérios, isto é, tenha na primeira letra um C e na terceira um A, ou que termine com O. Você pode observar isto na listagem que existem empresas que não terminam com O, porém atendem ao primeiro critério e existem empresas que não iniciam com C e tem na terceira letra um A, porém atendem ao segundo critério. Isto comprova que quando duas condições estiverem ligadas pelo conectivo Ou, todos os registros que atenderem a uma das condições, estará na listagem. Se atender as duas condições melhor ainda, também estará na listagem. A única hipótese de um registro não estar na listagem é se não atender a nenhuma das duas condições. Vamos começar a utilizar alguns outros operadores e funções do Microsoft Office Access 2007para especificar critérios em consultas. Criar uma consulta que exiba somente os registros cujo NúmeroDoPedido esteja entre 4001 e 4013 e que a CidadeDestino seja Uberlândia-MG ou Salvador-BA. Para criar a consulta proposta faça o seguinte:
Entre 4001 E 4013
Uberlândia-MG Ou Salvador-BA
Temos dois aspectos importantes a serem observados nesta consulta. Primeiro a utilização do Operador "ENTRE". Este Operador, normalmente, é utilizado para pesquisar valores dentro de uma determinada faixa, sendo que o valor inicial é o limite inferior e o valor final ( Após o E ) é o limite superior. No campo CidadeDestino colocamos o critério Uberlândia-MG Ou Salvador-BA, para pesquisar aqueles pedidos somente para Uberlândia-MG ou Salvador-BA. Observe que quando dois ou mais critérios são especificados na linha critério, o Microsoft Office Access 2007 liga os mesmos através de um operador E. No nosso exemplo para que um registro seja selecionado ele deverá ter o Número do Pedido entre 4001 e 4013 E também ter como CidadeDestino Uberlândia-MG ou Salvador-BA. Uma vez que os caracteres curinga, como *, são tratados como literais, você não pode utilizá-los com o operador Between...And. Por exemplo, você não pode utilizar 980* e 989* para localizar todos os códigos postais que começam com 980 e 989. Em vez disso, você tem duas alternativas: pode adicionar uma expressão para a consulta que pegue os três caracteres da esquerda do campo de texto e utilizar Between...And nesses caracteres, ou pode preencher os valores superior e inferior com caracteres extras — neste caso, 98000 a 98999, ou 98000 a 98999 – 9999 se estiver utilizando códigos postais estendidos. (Você deve omitir o – 0000 dos valores inferiores pois, caso contrário, 98000 será excluído se alguns códigos postais tiverem seções e outros não). Criar uma consulta que exiba somente os registros cujo NúmeroDoPedido esteja entre 4001 e 4013 e que a DataPedido esteja no período de Janeiro à Fevereiro de 2010. Para criar a consulta proposta faça o seguinte:
(Mês([DataPedido]) Entre 1 E 2) E Ano([DataPedido])=2010
Entre 4001 E 4013
Agora começou a ficar mais divertido este treinamento. Observe o critério que utilizamos no campo DataPedido. Primeiro utilizamos a função Mês para extrair somente o Mês da DataPedido. A função Mês retorna uma valor Numérico ( Jan=1, Fev=2, Mar=3, etc). Utilizamos o Operador Entre para exigir que somente fossem selecionados os registros cujo Mês esteja na faixa de 1 a * ( Janeiro a Fevereiro). Também precisamos fixar que queremos apenas o ano de 2010 utilizamos um operador E ( uma vez que as duas condições precisam ser atendidas - Mês de Janeiro a Fevereiro para o Ano de 2010) e o critério Ano ([DataPedido]) = 2010. Fizemos uso da função Ano para extrair apenas o Ano da Data e compará-lo com 2010. Com a combinação dos dois critérios obtemos o resultado desejado para a DataPedido: Somente pedidos de Janeiro a Fevereiro para o Ano de 2010. No campo NúmeroDoPedido, utilizamos o operador Entre para limitar os Pedidos com NúmeroDoPedido entre 4001 e 4013 para o Período especificado anteriormente. Podemos notar que a utilizando funções em conjunto com os operadores, iremos construir consultas com critérios de filtragem bastante sofisticados. Vamos explorar um pouco mais as possibilidades de utilização das funções em conjunto com os operadores. Criar uma consulta que exiba somente os registros cujo NomeDaEmpresa tenha CO como as duas primeiras letras. Ao invés de utilizar o operador Como, utilize a função Esquerda. Para criar a consulta proposta faça o seguinte:
Esquerda([NomeDaEmpresa];2)="CO"
Claro que, facilmente, poderíamos obter os mesmos resultados, simplesmente utilizando o operador Como da seguinte maneira: Como "CO*". O Objetivo é salientar a utilização da função Esquerda. Esta função recebe dois parâmetros: O Nome do Campo sobre o qual a mesma vai atuar e o número de caracteres que ele deve retornar iniciando a esquerda do campo. Os parâmetros são separados por ponto e vírgula. Abaixo temos mais alguns exemplos de utilização da função esquerda:
Além da função esquerda, temos as funções Direita e Meio. A função direita, inicia a direita de um campo e retorna um número determinado de valores. A sua sintaxe é conforme indicado abaixo: Direita([NomeDoCampo];NúmeroDeCaracteres) A função Meio permite que seja retornado um determinado número de caracteres a partir de uma posição específica dentro de um campo. Por exemplo posso retornar 4 caracteres, a partir do 5 caracter. A Sua sintaxe é conforme indicado abaixo: Meio([NomeDoCampo];PosiçãoOndeIniciar;NúmeroDeCaracteres) Na tabela a seguir você verá diversos exemplos de utilização das funções Direita, Esquerda e Meio:
Estas funções são bastante úteis e podem ser utilizadas em diversas situações práticas do dia-a-dia. Veremos, na parte final do curso, quando for visto Visual Basic for Applications, uma rotina para verificação do DV do CPF. Nesta rotina faremos um uso intensivo das funções Direita, Esquerda e Meio. Vamos supor que você queira criar uma nova coluna, que vamos chamar de CódigoClientePaís. Este código será formado pelas 3 primeiras letras do campo NomeDaEmpresa e pelas três últimas letras do campo CidadeDestino. Para criarmos esta nova coluna, teremos que utilizar as funções Esquerda ( para extrair as 3 primeiras letras do campo NomeDaEmpresa) e a função direita ( para extrair as três últimas letras do campo CidadeDestino). Além disto teremos que usar o Operador & para concatenar as duas partes. O Operador & é utilizado no Microsoft Office Access 2007para concatenar duas ou mais strings. Apenas lembrando do tópico inicial sobre consultas, podemos, sempre que necessário, criar novos campos nas consultas, os quais são derivados de dados de outros campos. Tanto podemos criar campos que efetuem cálculos ( por exemplo {PreçoUnitário]*[Quantidade} ) quanto campos que realizam operações com Strings, conforme está sendo proposto no nosso exemplo de juntar as três primeiras letras do campo NomeDaEmpresa, com as três últimas letras do campo CidadeDestino, para formarmos um novo campo que vamos chamar de CódigoApuraçãoEspecial. Então, Mãos à Obra! Criar uma consulta que adicione um campo chamado CódigoApuraçãoEspecial, o qual seja formado pelas três primeiras letras do campo NomeDaEmpresa e pelas três últimas letras do campo CidadeDestino. Para criar a consulta proposta faça o seguinte:
CódigoApuraçãoEspecial: Esquerda([NomeDaEmpresa];3) & Direita([CidadeDestino];2)
Relembrando o que já havíamos visto no início do capítulo sobre consultas: Quando Estivermos adicionando campos calculados ou derivados de outros campos, o que vier antes dos dois pontos é simplesmente o nome que irá aparecer no modo Folha De Dados, conforme pode ser visualizado na figura anterior. O que vem depois dos dois pontos é a expressão que ira gerar os valores para o campo CódigoApuraçãoEspecial. A primeira parte da expressão: Esquerda([NomeDaEmpresa];3), retorna as três primeiras letras do campo NomeDaEmpresa, as quais são concatenadas pelo operador & com as duas últimas letras do campo CidadeDestino, as quais são retornadas através da utilização da função Direita([CidadeDestino];2). Outro fato importante a lembrar é que esta coluna não fará parte de nenhuma tabela e os dados nela contidos, não ficam armazenados no banco de dados. Toda vez que a consulta for executada, o Microsoft Office Access 2007irá calcular os valores desta coluna, utilizando a expressão indicada anteriormente. Lembre que um dos princípios básicos de projeto de banco de dados é não armazenar campos calculados. Vamos sofisticar um pouco mais a nossa consulta. Crie uma consulta que adicione um campo chamado CódigoApuraçãoEspecial, o qual seja formado pelas três primeiras letras do campo NomeDaEmpresa e pelas duas últimas letras do campo CidadeDestino, mais um hífen ( - ), mais a palavra Barato se o frete for menor ou igual à R$15,00 ou a palavra Caro se o frete for maior do que R$ 15,00. Para criar a consulta proposta faça o seguinte:
CódigoApuraçãoEspecial: Esquerda([NomeDaEmpresa];3) & Direita([CidadeDestino];2) & "-" & SeImed([Frete]<=15;"Barato";"Caro")
A novidade nesta consulta é a utilização da função SeImed, conforme indicado abaixo: SeImed([Frete]<=15;"Barato";"Caro") Esta função possue três parâmetros. O Primeiro faz um teste ( [Frete]<=15). Caso o teste retorne um valor verdadeiro, ou seja, o campo Frete tenha uma valor menor ou igual 15, o segundo parâmetro ("Barato") é retornado. Caso o teste ([Frete]<=15) seja falso, o terceiro parâmetro ("Caro") é retornado. Novamente utilizamos o operador & para concatenar as diversas partes que formam o campo. Observe, também, que para adicionar o hífen ( "-" ), colocamos o mesmo entre aspas. Sempre que quisermos concatenar uma string, precisamos colocá-la entre aspas. Vamos exercitar um pouco mais a função SeImed ( IF em Português). Crie uma consulta que adicione um campo chamado ImpostoFrete, o qual seja calculado da seguinte maneira: Se o frete for menor ou igual a R$15,00, o Imposto será de 2 Reais, caso contrário, o imposto será de 5 Reais. Para criar a consulta proposta faça o seguinte:
ImpostoFrete: SeImed([Frete] Entre 0 E 15;0,15;0,2) Vá para o modo Folha de Dados e observe os resultados, conforme indicado na figura a seguir:
Além do uso da função Selmed, temos mais dois pontos importantes a observar nesta consulta. Lembrando do primeiro item sobre consultas, você deve formatar a coluna ImpostoFrete, para que a mesma exiba os valores no formato de moeda. Para fazer isto, volte ao modo design, clique com o botão direito sobre a coluna, no menu que surge clique em propriedades. Na janela que surge em formato escolha Unidade Monetária e em casas decimais escolha 2, dê um clique em OK para fechar a janela das propriedades. Observe que dentro da função Selmed, ao invés de utilizarmos um teste de comparação, utilizamos o operador Entre dentro da função Selmed para testar se o Frete está entre 0 e 15. Você pode ver que existem enormes possibilidades de utilização de operadores em conjunto com as funções. Você pode encontrar mais informação sobre Operadores e Funções no ajuda do Microsoft Access 2007. Vamos aprender a utilizar mais um operador, o Operador IN ( Em). Crie uma consulta que liste apenas os Pedidos para as Cidade(Uberlândia-MG,Castanhal-PA ou Salvador-BA). Utilizar o Operador IN. Para criar a consulta proposta faça o seguinte:
IN ("Uberlândia-MG";"Castanhal-PA";"Salvador-BA")
Poderíamos ter construído esta consulta utilizando o Operador OU. Em termos de funcionalidade utilizar – Uberlândia-MG Ou Castanhal-PA Ou Salvador-BA, ou utilizar IN (“Uberlândia-MG”;”Castanhal-PA”;”Salvador-BA”), não faz diferença nenhuma. Na prática quando temos uma lista grande de valores a serem comparados, a utilização do Operador IN(EM) torna-se mais prática e de mais fácil entendimento. E se nos quiséssemos uma consulta que listasse os pedidos para todos as Cidades, menos para o Uberlândia-MG, Castanhal-PA ou Salvador-BA. Bastaria colocar o seguinte critério, no campo CidadeDestino. Negado IN("Uberlândia-MG";"Castanhal-PA";"Salvador-BA") Neste caso utilizamos o operador negado, para informar ao Microsoft Office Access 2007que queremos todos aqueles pedidos, exceto aquelas ( Negado aqueles) cujo campo CidadeDestino seja Uberlândia-MG,Castanhal-PA ou Salvador-BA. Vamos explorar um pouco mais a linha de totais em consultas, juntamente com o uso das chamadas funções de domínio ou funções agregadas. Volte para o Modo Design e dê um clique no botão 5.3 - Sobre funções agregadas e outras opções na linha Total da grade da estrutura da consulta Das 12 opções na linha Total da grade de estrutura da consulta, 9 são funções agregadas. Todas as funções, exceto Primeiro e Último, são explicadas na tabela a seguir. Para considerações especiais sobre a utilização das funções Primeiro e Último, vá no ajuda ->funções. As outras três opções da lista são explicadas na segunda tabela. Observação: As funções agregadas não incluirão em seus cálculos registros que contenham valores em branco (Nulos). Por exemplo, a função Contar retorna uma contagem de todos os registros sem valores Nulos. Existe uma forma de contar valores Nulos e você pode converter valores Nulos em zeros para que sejam incluídos em um cálculo.
As Outras Opções disponíveis estão indicadas na tabela abaixo:
Vamos criar algumas consultas que utilizam diversas opções da linha Total, de tal forma que possamos fixar a utilização destas funções. Crie uma consulta que calcule o total de Frete por CidadeDestino. Para criar a consulta proposta faça o seguinte:
O Agrupar Por pede ao Microsoft Office Access 2007agrupem todos os pedidos para uma Mesma CidadeDestino, e a Soma pede para que o Microsoft Office Access 2007Some o valor do Frete para todos os Pedidos de uma Mesma Cidade. É importante salientar que para termos acesso a estas opções de Agrupar Por e Soma é necessário que a linha Total esteja sendo exibida. Poderíamos, ao invés de calcular a Soma, calcular a média dos Pedidos. Para isto basta voltar ao Modo Design e trocar Soma por Média, na linha Total do campo Frete. Crie uma consulta que conte o número de Pedidos por Cidade de Destino. Para criar a consulta proposta faça o seguinte:
Observe que para Contar o Número de Pedidos, bastou alterarmos no campo Frete, na linha Total de Somar para Contar. Poderíamos da Mesma maneira utilizar qualquer uma das outras funções. Você também poderia adicionar um critério para que somente fossem retornados os registros para Cidades com mais de 50 Pedidos. Para isto basta colocar o seguinte critério no campo Frete: > 50. Agora vamos criar algumas consultas que envolvem tanto as totalizações quanto cálculos na mesma consulta. Criaremos uma consulta que irá calcular o total de vendas por Cidade de Destino. Para isto iremos criar uma consulta que calcula o subtotal para cada item de cada pedido ( [Quantidade]*[PreçoUnitário]*(1-[Desconto]) e depois totalizar por Pedido. Depois construiremos uma segunda consulta que ira totalizar estes subtotais por Cidade de Destino. Com isso estaremos aprendendo um novo conceito relativo a consultas: O Microsoft Office Access 2007permite que seja criada uma consulta, baseada em outra consulta. Crie uma consulta baseada na tabela Detalhes Do Pedido, a qual calcule o subtotal para cada item, e some todos os subtotais para obter o total por pedido, utilizando a seguinte fórmula, a qual leva em consideração o desconto: SubTotal: ([PreçoUnitário]*[Quantidade])*(1-[Desconto]) Para criar a consulta proposta faça o seguinte:
SubTotal: ([PreçoUnitário]*[Quantidade])*(1-[Desconto])
Utilizamos, na criação desta consulta, duas técnicas já conhecidas. Criamos um campo calculado, utilizando a fórmula indicada anteriormente. Apenas para lembrar, o que vem antes dos dois pontos é apenas o Nome da Coluna calculada, depois dos dois pontos é que vem a fórmula de Cálculo. Também utilizamos a linha de Total para Agrupar todos os itens do mesmo pedido e pedir para que o Microsoft Office Access 2007fizesse a soma destes itens. Salve a consulta com o Nome de "Calcula Subtotais" e feche-a. Crie uma nova consulta, baseada na Consulta "Calcula Subtotais" e na tabela Pedidos. Esta consulta ira fazer o somatório de todos os pedidos, agrupados por Cidade De Destino.
NOTA: Observe que como rótulo da coluna o Microsoft Office Access 2007coloca SomaDeSubtotal, isto é , ao nome do campo (SubTotal), é adicionada a palavra SomaDe. Para alterar este rótulo, basta acessar as propriedades da coluna ( no Modo Design ) e no campo Legenda, digitar o texto desejado. Por exemplo, volte para o Modo Design, clique com o direito sobre a coluna dos subtotais, clique em Propriedades. Na janela que surge , digite Total por Cidade, no campo Legenda, dê um clique no x para fechar. Volte para o modo folha de dados e observe que o título da coluna já deve ter se alterado para Total por Cidade. Agora que já conhecemos melhor a criação e utilização de Critérios avançados, operadores e funções, vamos fazer alguns exercícios para fixar os conceitos apresentados. CRIAR AS SEGUINTES CONSULTAS:
03) Alterar a consulta do item anterior, para que ao invés do número de pedidos por trimestre, seja exibido o número de pedidos por mês para o ano de 2010. 04) Criar uma consulta que exiba o total de vendas por Vendedor, para o ano de 2010. 05) Criar uma consulta que exiba todas os Pedidos ( NúmeroDoPedido, DataPedido, DataEntrega, Frete e CidadeDestino) para o ano de 2010 e que o número de dias entre a DataPedido e a DataEntrega seja maior do que 7. 06) Alterar a consulta do item anterior de tal forma que somente sejam exibidos os pedidos para Clientes cujo NomeDaEmpresa tenha a primeira letra no intervalo de A até P e cujo Cidade Destino não seja Uberlândia-MG ou Uberaba-MG. 5.4 - Consultas Parametrizadas como Critérios:Até este momento, estivemos trabalhando com consultas nas quais colocávamos, diretamente, no Modo Design, os critérios desejados. Podemos criar consultas mais genéricas, as quais ao serem executadas nos solicitam que seja digitado um determinado valor para um determinado critério. Por exemplo, ao invés de colocarmos Salvador-BA como critério para o campo CidadeDestino, podemos fazer com que o Microsoft Office Access 2007solicite um valor para o critério, cada vez que a consulta for executada. Isso torna a consulta bem mais ágil, pois cada vez que executarmos a mesma, podemos digitar um critério diferente, de tal maneira que não precisamos entrar no Modo Design para digitar um critério diferente. Este tipo de consulta é chamada de "Consulta Parametrizada". Para criarmos uma consulta parametrizada, ao invés de digitarmos diretamente um valor para o critério, digitamos uma expressão do seguinte tipo: [Digite o Valor para o Cidade Destino :] Por exemplo, na linha critério, do campo CidadeDestino, podemos digitar a expressão indicada anteriormente. O texto dentro dos colchetes é simplesmente um texto explicativo, que será exibido quando a consulta for executada e o valor do Parâmetro for solicitado. Este texto serve como orientação para os usuários que forem utilizar a consulta. Podemos utilizar expressões deste tipo em mais de um campo em uma mesma consulta. Por exemplo se você quiser que o Microsoft Office Access 2007solicite o nome do CidadeDestino, uma data inicial e uma data final, de tal forma que somente sejam retornados os Pedidos para a Cidade digitada e dentro do período especificado, você pode fazer isso sem maiores problemas. Existem algumas limitações com a utilização de parâmetros. Por exemplo, se você utilizar uma expressão para solicitar que o usuário digite um CPF. Ao digitar o CPF, o usuário terá que digitar os pontos e o traço da formatação do CPF, uma vez que não podemos colocar uma máscara de entrada para o parâmetro, de tal forma que o usuário somente precise digitar os números. De uma forma geral a utilização de consultas parametrizadas aumenta em muito a flexibilidade das consultas no Microsoft Access 2007, pois não precisamos criar uma consulta diferente para cada critério que vamos utilizar. Ao invés disso podemos criar uma única consulta parametrizada, cada vez que executarmos a mesma o Microsoft Office Access 2007nos solicita o valor para o parâmetro. Vamos usar a mesma abordagem do tópico anterior, isto é, vamos criar uma série de consultas que salientam os diversos aspectos da utilização de consultas parametrizadas. No final deste tópico serão propostos alguns exercícios para fixação dos conceitos apresentados. Nunca é demais salientar que maiores informações sempre podem ser obtidas na Ajuda do Microsoft Access 2007, o qual apresenta uma referência completa de todas as funções e operadores, bem como diversos exemplos sobre consultas. Crie uma consulta que liste o NúmeroDoPedido, DataPedido, Frete e CidadeDestino. Fazer com que ao ser executada a consulte solicite que seja digitado um nome de Cidade e que sejam retornados somente os Pedidos para a Cidade digitada. Para criar a consulta proposta faça o seguinte:
[Digite a Cidade Desejada:]
Cada vez que você executar esta consulta, o Microsoft Office Access 2007irá solicitar que você digite um valor para o campo CidadeDestino, valor este que será utilizado como critério de seleção. Altere a consulta criada anteriormente, para que além de pedir que seja digitado a Cidade, a mesma peça para que seja digitada uma Data Inicial e uma Data Final e exiba somente os Pedidos para a Cidade digitada e dentro do período especificado. Para criar a consulta proposta faça o seguinte:
Entre [Data Inicial:] E [Data Final:]
Observe a utilização do Operador Entre em conjunto com os parâmetros, na seguinte expressão: Entre [Data Inicial:] E [Data Final:] Quando executamos a consulta e digitamos os valores para a Data Inicial (01/01/2010) e para a Data Final (31/01/2010), o Microsoft Office Access 2007coloca estes valores na expressão e o critério se transforma em Entre #01/01/2010/# E #31/01/2010#. O Microsoft Office Access 2007substitui os parâmetros pelos valores digitados. O sinal de # é adicionado pelo Microsoft Access 2007, pois se tratam de campos do tipo Data e valor do tipo data deve vir delimitado, para efeitos de critérios de pesquisa em consultas, pelos caracteres #, no início e no final da data. Para criar a consulta proposta faça o seguinte:
Como '*'+[Digite a Cidade Desejada (* para todos):]+'*'
Observe que alteramos o parâmetro na Cidade Destina em dois pequenos detalhes. Adicionamos um asterisco antes e um depois do parâmetro que vem dentro dos colchetes. Isso para que o Microsoft Office Access 2007aceite o * ( Um caracter coringa) como sendo todos as Cidade destino. Também alteramos o texto dentro dos colchetes ( Digite a Cidade Desejada (* para todos):), apenas para orientar o usuário sobre como proceder, quando da execução da consulta. O Principal benefício da utilização de parâmetros, é que os mesmos tornam nossas consultas mais flexíveis. Não precisamos, cada vez que a consulta for executada, entrar no Modo Design e alterar o parâmetro, uma vez que o Microsoft Office Access 2007solicita um valor para o Parâmetro ( Ou parâmetros), cada vez que a consulta for executada. Além disso, o usuário final não deve ter acesso a estrutura das consultas, tabelas, formulários, etc. Para que possamos impedir que o usuário acesse a estrutura de uma consulta, temos que possibilitar executa as consultas com diferentes parâmetros, conforme for necessário. Isso é possibilitado pela utilização das consultas parametrizadas. Veremos mais sobre impedir o acesso a estrutura, no ítem sobre segurança. EXERCÍCIOS:
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| « Anterior | Δ Página principal | ¤ Índice | Próxima » |
Universidade do Access - Curso Completo de Access
com tudo para você dominar o Access - do Básico ao
Avançado - até a Criação de Sistemas Profissionais
Completos - Passo a Passo - Tela a Tela
Aplica-se ao Access 2019, 2016, 2013 e 2010!
Para todos os detalhes, acesse:
|
MEGA FORMAÇÃO EM INFRAESTRUTURA DE TI (Online, Vitalício, Prático e Atualizado)! |
|
|
NÃO PROCURE VAGAS, SEJA PROCURADO! |
|
Para Todos os Detalhes, Acesse:
https://juliobattisti.com.br/curso-infra-ti.asp
|
Contato: Telefone: (51) 3717-3796 | E-mail: webmaster@juliobattisti.com.br | Whatsapp: (51) 99627-3434
Júlio Battisti Livros e Cursos Ltda | CNPJ: 08.916.484/0001-25 | Rua Vereador Ivo Cláudio Weigel, 537 - Universitário, Santa Cruz do Sul/RS, CEP: 96816-208
Todos os direitos reservados, Júlio Battisti 2001-2026 ®
LIVRO: MACROS E PROGRAMAÇÃO VBA NO EXCEL 2016 - CURSO COMPLETO E PRÁTICO
DOMINE A PROGRAMAÇÃO VBA NO EXCEL - 878 PÁGINAS - CLIQUE AQUI
.Lembre que você pode marcar mais do que uma tabela ao mesmo tempo, utilizando o Mouse em conjunto com as teclas CTRL ou SHIFT, conforme descrito no item: "Para Definir Relacionamentos no Microsoft Access 2007"
.
, será aberta a janela para adicionar tabelas, conforme indicado na figura anterior.
, na caixa de diálogo mostrar consulta dê um clique na tabela Pedidos e depois dê um clique no botão Adicionar. Dê um clique no botão Fechar. Feito isso a tabela Pedidos será adicionada a sua grade. Observe que existe um relacionamento do tipo "Um para Vários" entre Pedidos ( lado) um e "Detalhes do Pedido" ( lado vários). Isso indica que um determinado pedido pode conter vários ítens .Adicione os seguintes campos:
. Este botão permite que você alterne entre o Modo Design da consulta e o modo "Folha de Dados". No modo folha de dados é que você visualiza o resultado da consulta.
, retorne para o modo o Modo Design. De volta ao Modo Design, vamos adicionar uma Coluna Calculada ( Até que enfim, afinal este é o objetivo deste tópico). Adicionaremos uma coluna que calcula o total de cada ítem. Este total é calculado utilizando a fórmula abaixo indicada:
para ir para o Modo "Folha de Dados". Veja que foi adicionada uma coluna com o título Total_Ítem, do tipo Moeda, conforme o esperado. Observe que continua aparecendo uma linha para cada ítem de cada pedido. Dê um clique no botão com o Diskete para salvar a consulta. Ao ser solicitado o nome da consulta digite: Totais por Pedido e dê um clique em OK.
e volte para o Modo Design. 
, este botão exibirá uma linha adicional no Modo Design: A Linha de Totais. Esta linha que irá nos permitir fazer a soma de todos os ítens de cada pedido. 
). Abrirá uma janela “Ajuda do Access” clique em sumário 
, aparecerá todas as opções do sumário. Dê um clique em Automação e Programação -> Funções. Observe que aparecerá várias funções para que você possa aprender e praticar um pouco mais. Você encontrará uma referência completa a todas as funções disponíveis, classificadas em ordem alfabética, conforme indicado pela próxima figura.
, este botão exibirá uma linha adicional no Modo Design: A Linha de Totais. Esta linha nos permite a utilizar diversas funções para a realização de cálculos e totalizações nas tabelas. Abaixo, segue um pequeno resumo de cada uma das opções da linha de totais:
, para exibir a linha de totais;
, para exibir a linha de totais;
