Formulário com Filtragem

Por vezes é necessário introduzir dados num formulário, onde existem campos cuja origem de dados é uma lista grande, sendo necessário efectuar filtragem nos dados de origem de acordo com outro campo já inserido.

Vamos a um exemplo:
 
Utilizando a BD das freguesias vamos supor que queremos registar uma lista de escolas, sendo uma das informações o Concelho e a Freguesia.

    1- Na BD Freguesias criar uma tabela Escolas com os campos ID (Numeração Automática), Nome (Texto), Concelho (Texto),Freguesia (Texto).
        Embora cada freguesia tenho um concelho associado, neste exemplo, vou incluir na tabela Escolas o campo Concelho. Este campo vai ocupar espaço redundante, mas ajuda a clarificar.

   2 - Criar as ligações conforme o exemplo das Freguesias. Uma ligação entre Concelho e codConcelho da tabela Concelhos e outra entre Freguesia e codFreguesia da tabela Freguesias.

   3- Criar um formulário usando o assistente, para a tabela Escolas e em vista Tabela (formulário contínuo). Para alterar o formulário tem mudar para vista de estrutura.

   4- Se foi colocada a origem de dados correctamente nos campos Freguesia e Concelho, no formulários irá parecer estes campos ligados a comboboxes. Caso contrário mudar para combobox, usando o botão direito do rato.
      Na combo do Concelho a origem de dados deve ser  SELECT codconcelho,concelho FROM Concelhos ORDER BY concelho, com coluna pendente 1,  número de colunas 2 e largura das colunas 0.
      Para a combo da Freguesia seguir o mesmo método com a origem de dados SELECT codfreguesia,freguesia FROM Freguesias WHERE codconcelho=concelho ORDER BY freguesia.
      Mude para a vista formulário para visualizar e pode inserir e editar registos.

Embora assim funcione, não tem o efeito desejado, porque precisa actualizar o registo para que a lista de freguesias seja actualizada.

Usando uma linha de código em VBA é possível fazer a lista de freguesias actualizar sem ter de actualizar o registo. 
Com a combo Concelho seleccionada vá às propriedades no separador Evento e no evento ao fazer clique, escolha [Procedimento de Evento] e clique nos (...). Irá parecer o editor VBA com o cursor no procedimento  Concelho_Click.  Insira a seguinte linha de código:

Freguesia.RowSource = "SELECT codfreguesia,freguesia FROM Freguesias WHERE codconcelho='" & Nz(Concelho, "") & "' ORDER BY freguesia"

O procedimento todo, depois de fazder uma quebra na linha para melhor visualizar, fica assim:

Private Sub Concelho_Click()
    Freguesia.RowSource = "SELECT codfreguesia,freguesia FROM Freguesias " _
        & "WHERE codconcelho='" & Nz(Concelho, "") & "' ORDER BY freguesia"
End Sub

Mude para vista formulário e teste.

Este modelo está disponível em Escolas-01, onde já foi incluído o distrito que faz a filtragem no concelho.

Qualquer dúvida, deixe um comentário.

SQL

SQL - Structured Query Language

( Linguagem de Consulta Estruturada)

Na criação de bases de dados em Access, é de grande utilidade a utilização de expressões SQL. É muito usada para pesquisar informação guardada nas tabelas, mas pode também ser usada para estruturar a própria base de dados.

O SQL possui instruções de definição, manipulação e controlo.

- As instruções de definição são as que permitem criar, modificar e apagar tabelas e relações entre as tabelas.

- As instruções de manipulação são as mais usadas e permitem introduzir, modificar e apagar dados, e acima de tudo obter dados através de critérios de pesquisa.

- As instruções de controlo são que estabelecem a segurança da base de dados, nomeadamente gerir permissões a nível de utilizador.

Definição	Manipulação	Controlo
CREATE TABLE ALTER TABLE DROP TABLE CREATE INDEX	INSERT INTO SELECT INTO UPDATE DELETE SELECT UNION TRANSFORM PARAMETER	ALTER DATABASE CREATE GROUP DROP GROUP CREATE USER DROP USER ADD USER GRANT PRIVILEGE REVOKE PRIVILEGE

A sintaxe do SQL utiliza palavras reservadas, que não devem ser usadas para nomear quaisquer objectos da base de dados.

Exemplo de utilização do SQL

Para começar abrir o Access e criar uma nova base de dados. Criar uma consulta em modo de estrutura e mudar para vista SQL.

Vamos criar uma tabela Compras como exemplo para exercitar as instruções SQL:

CREATE TABLE Compras (

ID COUNTER CONSTRAINT IDPk PRIMARY KEY,

Compra TEXT(30) NOT NULL,

Data DATE NOT NULL,

Quantidade INTEGER,

Total CURRENCY NOT NULL,

Descricao MEMO

);

Carregar no botão executar para que a tabela seja criada.

Para introduzir dados:

INSERT INTO Compras (Compra,Data,Quantidade,Total)

VALUES('Café',#2012-04-15#,1,0.60)

ou

INSERT INTO Compras

VALUES( 2,'Cerveja',#2012-04-16#,2 ,2.0,’Com um Amigo’)

O texto a inserir tem de estar entre aspas(‘). Para as datas podemos usar o cardinal(#) ou aspas(‘).

Para alterar valores já introduzidos: UPDATE Compras SET Total=3.0 WHERE ID=2

Para apagar registos: DELETE * FROM Compras WHERE Data=#2012-04-15#


Imaginemos que depois de alguns registos temos uma tabela assim:

ID	Compra	Data	Quantidade	Total	Descricao
1	Café	15-04-2012	1	0.60 €
2	Cerveja	16-04-2012	2	3.00 €	Com um Amigo
3	Almoço	17-04-2012	1	9.50 €
4	Camisa	18-04-2012	1	25.00 €
5	Combustível	18-04-2012	1	20.00 €
6	Café	17-04-2012	1	0.60 €
7	Almoço	19-04-2012	1	11.00 €
8	Cerveja	17-04-2012	3	3.00 €
9	Café	19-04-2012	2	1.20 €

Sobre estes registos podemos pesquisar de várias formas, consoante a informação que pretendemos.

Todos os registos: SELECT * FROM Compras
Quantidade de registos: SELECT COUNT(*) FROM Compras
Quantidade de artigos: SELECT SUM(Quantidade) FROM Compras
Total gasto: SELECT SUM(Total) FROM Compras
Ou tudo junto: SELECT COUNT(*),SUM(Quantidade),SUM(Total) FROM Compras

Por data: SELECT * FROM Compras WHERE Data=#2012-04-17#
Por compra: SELECT * FROM Compras WHERE Compra = 'Café'
ou: SELECT * FROM Compras WHERE Compra LIKE 'Café'

Resumo por datas com campos nomeados, usando agregação de registos:

SELECT Data,COUNT(*) AS Compras,SUM(Quantidade) AS Artigos,SUM(Total) AS [Valor Total] FROM Compras GROUP BY Data ORDER BY Data

Guardar ficheiros na base de dados

Uma base de dados guarda na maior parte dos casos dados em textos, números, valores e datas. Mas por vezes é preciso guardar ficheiros, quer seja de imagens, documentos, pdfs e etc.

Existem várias situações:

     1-     Um único ficheiro para cada registo.

a.      Criar um campo do Tipo ObjectoOLE e inserir o ficheiro nesse campo.

b.      Criar um campo do tipo texto e guardar o nome do ficheiro.

c.      Não criar campos. Apenas criar uma pasta e guardá-la de algum modo na bd e nomear os ficheiros dessa pasta com nome ou numero indicado num campo de índice único, de preferência o campo com a chave primária.

2-     Um número fixo de ficheiros para cada registo.

a.      Criar campos do Tipo ObjectoOLE e inserir os ficheiros nesses campos.

b.      Criar campos do tipo texto e guardar os nomes dos ficheiros.

c.      Não criar campos. Apenas criar pastas, uma para cada tipo de ficheiro, e guardá-la de algum modo na bd e nomear os ficheiros dessas pastas com nome ou numero indicado num campo de índice único, de preferência o campo com a chave primária.

3-     Um número variável de ficheiros para cada registo.

a.      Criar uma tabela relacionada, e nessa tabela criar um campo do tipo ObjectoOLE e inserir os ficheiros.

b.      Criar uma tabela relacionado, e nessa tabela criar um campo do tipo texto para inserir o nome dos ficheiros.

c.      Não criar a tabela. Apenas criar pastas, uma para cada registo, com nome ou numero indicado num campo de índice único, de preferência o campo com a chave primária. Dentro de cada uma dessas pastas colocar os ficheiros correspondentes.

A situação 2.c pode ser idêntica à 3.c

Os campos do tipo ObjectoOLE, se forem ficheiros grandes ou em grande número, vai aumentar o tamanho da bd, e levar a um desempenho mais fraco causando eventualmente erros que podem comprometer a integridade dos dados guardados ou até corromper a bd.

Guardar o nome dos ficheiros num campo de texto tem à primeira vista algumas desvantagens, quando queremos mudar de local a bd e os ficheiros. Neste caso temos duas hipóteses; ou criamos pastas dentro da pasta que contem a bd, ou guardamos nalgum lugar o nome e localização da pasta.

Para guardar o nome e localização da pasta ou pastas, podemos criar uma tabela para as registar ou criar propriedades na bd para lá inserir essa informação.

Dou como exemplo a base de dados Artistas.mdb criada para exemplificar a situação 1.c

A bd Artistas.mdb usa procedimentos em VBA para inserir imagens numa pasta com o nome idêntico ao campo Nome da tabela Artistas.

Esta bd possui uma tabela, um formulário, uma macro de nome AutoExec que é executada ao abrir a bd, e um modulo VBA com as funções e procedimentos usados.

As imagens ficam numa pasta cujo directório é guardado numa propriedade criada na base de dados e apresentada no rodapé do formulário, com um botão para a alterar.

Para cada registo de artista pode existir uma imagem. Quando não existe imagem, é exibido o texto ‘SEM IMAGEM’ e fica activado o botão para inserir.

Ao inserir imagem abre uma caixa de dialogo para poder escolher a imagem mo computador. Depois de escolher a imagem é feita uma cópia para a pasta das fotos, no entanto deixei o código para caso queira poder ser apenas movida e renomeada a imagem.

Nesta bd incluí procedimentos em VBA para manipular o nome e directório dos ficheiros, escolha de ficheiros e pastas, obtenção de variáveis de sistema e controlo da janela do Access. Grande parte destes procedimentos são baseados em funçõe API do Windows, as quais estão documentadas e com link para a página do MSDN Library

Modelo de Entidades e Relacionamentos - MER

O Access é um sistema de gestão de dados relacional, e possui um modelo de entidades e relacionamentos, entre as diversas tabelas (MER), com apresentação em diagrama (DER).

As tabelas contêm o armazenamento de dados de uma base de dados. É nelas que é guardada toda a informação que depois pode ser consultada.

A informação contida em cada tabela é organizada em linhas e colunas, mais conhecidas por registos e campos.

O número de registos em cada tabela é ilimitado até a bd atingir a máximo de 2Gb.

Os 2Gb dariam perfeitamente para guardar o nome, BI, NIF e endereço de todos os portugueses. Mesmo assim o limite é facilmente ultrapassável, porque é sempre possível ligar tabelas de outros ficheiros.

Cada campo de uma tabela pode conter um determinado tipo de dados, sendo os mais usados numérico, data e hora e texto.

Pode ver aqui a lista completa de tipos de dados do Acess 2007:

http://msdn.microsoft.com/en-us/library/bb208866(v=office.12).aspx

Para exemplificar o uso de diversas tabelas ligadas entre si de acordo com o MER vou indicar como criar uma pequena base de dados com baseada na lista de freguesias disponível no portal das finanças.

Criar uma base de dados vazia;
      Abrir o Access e criar uma base de dados vazia.

      Criar as tabelas para freguesias, concelhos e distritos;

Tabela Distritos com os campos

                  coddistrito texto tamanho 2,

                  distrito texto tamanho 200,

                  chave primária em coddistrito.

      Tabela Concelhos
                        codconcelho texto tamanho 4,
                        coddistrito texto tamanho 2,
                        concelho texto tamanho 200,
                        chave primária em codconcelho;

Tabela Freguesias

                 codfreguesia texto tamanho 6,

                 codconcelho texto tamanho 4,

                 freguesia texto tamanho 200,

                 chave primária em codfreguesia.

Criar as relações entre as tabelas;

Ir a Ferramentas da Base de Dados e clicar em Relações;

Se aparecer alguma relação entre tabelas com nomes estranhos, clica com o botão direito em cima da tabela e escolha ocultar tabela;

Na barra de ferramentas clicar em mostrar tabelas e mostrar as 3 tabelas criadas antes;

Arrastar o campo coddistrito da tabela Distritos para o campo coddistrito da tabela Concelhos e na janela que vai aparecer confirmar os nomes dos campos e tabelas, e marcar a opção impor integridade referencial, assim como propagar a actualização. Fazer o mesmo para o campo codconcelho das tabelas Concelhos e Freguesias.

As linhas entre os campos das tabelas possuem do lado da chave primária o número 1 e do outro lado o símbolo infinito. Esse é o tipo de ligação um-para-muitos.

As relações devem ficar tal qual a imagem:

      Descarregar o ficheiro de Excel com as freguesias aqui.

      Link: http://info.portaldasfinancas.gov.pt/NR/rdonlyres/B365CA49-D07B-4FCD-B9BF-8FADCA0EF528/0/coddistconcfreg.xls

 Ligar a folha do Excel ao Access;

 Abrir o ficheiro e eliminar as duas linhas iniciais deixando os nomes dos campos como cabeçalho, guardar e fechar.

Utilizar a ferramenta de importar folha de calculo do excel, em dados externos, escolhendo o ficheiro e a opção de ligação à origem. Indicar que os cabeçalhos serão os nomes dos campos, sem chave primária e no final o nome xlsFreguesias.

Desta forma os dados continuam no excel, mas podem ser consultados no Access. As tabelas ligadas a folhas do excel não permitem alterar os dados, mas ligadas a tabelas de outras bases de dados já permite.

Criar consultas para os nomes e códigos das freguesias, concelhos e dos distritos;

Barra de ferramentas Criar, Consulta, modo SQL

consultaDistritos:

SELECT coddistrito, min(distrito) FROM xlsFreguesias GROUP BY coddistrito;

consultaConcelhos:

SELECT coddistrito & codconcelho AS codigo,  coddistrito, Min(concelho) AS NomeConcelho FROM xlsFreguesias GROUP BY coddistrito, codconcelho;

consultaFreg:

SELECT coddistrito & codconcelho & codfreguesia AS codigo, coddistrito & codconcelho as codconc, Min(freguesia) AS Nomefreguesia FROM xlsFreguesias GROUP BY coddistrito,codconcelho,codfreguesia;

Copiar os dados das consultas para as tabelas.

Começar pela tabela dos distritos. Copiar todas as linhas da consultaDistritos para a tabela Distritos;

Depois os dados da consultaConcelhos para a tabela concelhos;

Por fim da consultaFreg para a tabela Freguesias.

Apagar as consultas e a tabela xlsFreguesias, visto já não serem necessárias.

Se quiserem criar uma consulta para aparecer a informação tal qual na folha de Excel, é só criar uma consulta com a seguinte expressão SQL:

SELECT F.codfreguesia AS Código, D.coddistrito AS CodDist, distrito AS NomeDistrito, RIGHT(F.codconcelho,2) AS CodConc, concelho AS NomeConcelho, RIGHT([codfreguesia],2) AS CodFreg, freguesia AS NomeFreguesia

FROM Concelhos AS C, Distritos AS D, freguesias AS F

WHERE C.coddistrito=D.coddistrito AND F.codconcelho=C.codconcelho;

Ao abrir a tabela Concelhos o campo coddistrito mostra o código de 4 caracteres referente ao registo de distrito, mas é preferível ver o nome do distrito. Para vamos à estrutura da tabela e selecionamos o campo coddistrito, nas propriedades em baixo mudamos para o separador pesquisa e mudamos as propriedades de acordo com a imagem:

O mesmo pode ser feito na tabela freguesias campo codconcelho.

O ficheiro Freguesias.accdb pode ser encontrado aqui:

                 http://www.box.com/s/mfq10csb2t0njs6ybvn0

Da mesma forma criei um ficheiro Codigo Postal.accdb, a partir de ficheiros de texto disponibilizados no site dos CTT