[Resolvido]Calcular funcionarios activos e guardar dados por anos noutra tabela

Olá, boa tarde,
Estava a procura de ajuda na net e encontrei este fórum, que desde já quero dar os parabéns pela excelente atitude de entreajuda.
Eu estou a desenvolver uma pequena aplicação em Access para registo da formação frequentada pelos funcionários, e estou com dificuldade em conseguir calcular uns dados.
Tenho uma tabela em Access “Funcionarios” onde estão registados vários dados entre os quais a “data de entrada” e a “data de saída”.
Tenho que criar um gráfico com os trabalhadores activos em cada ano. Para isso preciso de calcular esse número e guardar numa tabela nova apenas os dados relativos ao “Ano” e ao “Nº Funcionarios”, só que estou com dificuldade em encontrar uma solução.
Será que me podem dar uma ajuda para definir o código necessário, ou se eventualmente poderei calcular estes valores de outra forma?

Muito obrigada,
Carmo

Olá Maria do Carmo, seja bem vinda ao forum.
Quando à sua dúvida, se você precisa apenas desses dois dados, a data de entrada e saída, não precisa criar outra tabela para isso. Você pode buscar os dados diretamente na tabela já existente, ou pode construir uma consulta parametrizada com eventuais critérios que se fizerem necessários.

olá, muito obrigada,

Acho que não me expliquei bem, pois o que pertendo é ter o nº total, por exemplo:
ano nºfunc
2000 120
2001 100
2002 105
etc...
o que eu tenho agora, é uma lista de registos por funcionario onde constam as colunas data de saida e entrada.

obg
cumpts,

Você pode criar uma consulta à partir da tabela, pegando o código e o ano. Depois, você cria um novo campo na consulta, colocando assim no cabeçalho:

Total: DContar("*";"NomeDaTabela";"Ano=" & [CampoAno])

Teste e retorne.

Neste link, consulte os tópicos referente a consultas >> referência cruzada:

Access - Sistemática de Links

as minhas desculpas mas acho que ainda não é isso, porque o que eu tenho é uma tabela com

id, nome, dtentrada, dtsaida
1 a 02-01-2007
2 b 31-08-2008 31-12-2009
3 c 05-05-2009
4 d 15-10-2009
5 e 01-02-2010
...etc

o que eu preciso é de ter é uma contagem deste tipo:
ano nºfunc
2007 1
2008 2
2009 4
2010 3

Obg

Acho que entendi. Você quer saber quantos funcionários tinham trabalhando a cada ano certo? Isso deve contar funcionários que trabalharam uma parte do ano como funcionário ativo no respectivo ano? Por exemplo, um funcionário que trabalhou até março de 2009 entra como funcionário ativo em 2009?

Veja esse pequeno exemplo que fiz com as informações que você passou:

Olá Dilson

obrigada pela sua ajuda, mas a contagem tem de contemplar o total activo em cada ano, ou seja, enquanto não houver data de saida assinalada, esse funcionario entra na contagem de todos os anos, assim no exemplo que fez o ano de:
2007 tem o total de 1;
2008 tem que ter o total de 2, pois tem que contemplar o funcionario que entrou em 2007 e que ainda está no activo em 2008, mais o funcionario que entrou em 2008;
2009 tem que ter o total de 4, pois as saidas independentemente do mes/dia, são para ser consideradas apenas no ano seguinte;
2010 tem de ter um total de 3 func activos, pois um terminou funções em 2009.

mais uma vez muito obrigada,
carmo

Olá Criquio

É isso mesmo.

muito obrigada pela ajuda,

carmo

Creio que você pode obter o resultado desejado usando a dica que dei anteriormente em um consulta:

Total: DContar("*";"NomeDaTabela";"Ano=" & [CampoAno])

Depois, basta criar o relatório agrupado por ano.

Bom dia

Já estive às voltas com a expressão que indicaste, mas não estou a entender como defino a contagem para me dar o total activo nos diferentes anos uma vez que enquanto não houver data de saida, esse funcionario tem que aparecer na contagem de todos os anos.
Com esta expressão estou apenas a conseguir contar os que entraram em determinado ano, não me repete a contagem de um funcionario nos vários anos, desde que ele entra até que saí.

Preciso que seja feita uma contagem do tipo:

Ano=2007
conta todos os registos existentes na base que tenham ["anoEntrada"<=2007 e "anoSaida">2007];
Ano=2008
conta todos os registos existentes que tenham ["anoEntrada"<=2008 e "anoSaida">2008];
...
e isto para todos os anos, até há data actual.
...

E penso que é necessário que estes dados fiquem guardados (ano; quantidade), para que depois possa criar um grafico.

mais uma vez obrigada,
Carmo

Teria como criar uma cópia do bd, retirar os elementos, deixando apenas a tabela em questão com alguns dados e postar para darmos uma olhada?

Não há problema nenhum, mas para não criar mais confusão o pequeno exemplo que o Dilson fez serve para testar o que pertendo. Os resultados esperados são os que dou na resposta ao Dilson.
Pode ser?

Cumpts,
Carmo

Mcc agora ficou entendido como pretende.

Até a noite se não tiver conseguido, eu tentarei adaptar para você.

Olá Dilson

Olha em SQL eu tenho o seguinte código e funciona como quero, não sei é adaptar para VB..

.....
drop table #qtd_trabalhadores
CREATE TABLE #qtd_trabalhadores
(ano int,
qtd int )

declare @ano as int;
declare @anoFim as int;
set @ano = 2000;
set @anoFim = 2012;

WHILE (@ano <= @anoFim)
BEGIN
INSERT INTO #qtd_trabalhadores (ano, qtd)
SELECT @ano, COUNT(*)
FROM Funcionarios
WHERE YEAR(Anoinicio) <= @ano
AND @ano <= YEAR(anofim)

SET @ano = @ano + 1
select * from #qtd_trabalhadores......

Consegui um resultado pelo relatório, veja:

olá Dilson,

não dá certo, primeiro porque voce esta a excluir os funcionarios que tem data de saida, ao colocar na query esse campo com criterio "é nulo", se reparar no relatorio não aparece funcionarios activos para o ano de 2008, quando deveriam estar dois, o que entrou em 2007 e o que entrou em 2008, pois este só tem data de saida em 2009.

Se tirar o critério "é nulo" os valores dariam o acumulado certo, mas isso era se não houvesse saidas...

E por outro lado com os dados em relatório, como posso depois utilizar os dados de forma automatica para criar um grafico?

muito obg,
mcc

Ok.

Achei interessante essa sua dúvida.

Tentarei outras maneiras, o que conseguir, aviso você.

Bom dia Dilson,

olha estava aqui a tentar resolver a questão via vba, o pior é que não sei mesmo nada de vba, mas fui retirando dicas de outras dúvidas, mas sinceramente não estou a conseguir, será que dá para dares uma olhadela e ver como é possivel fazer desta maneira:

Dim db As DAO.Database
Dim ano As Integer
Dim anofim As Integer
Dim strSQL As String
Dim numreg As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset("qry_FuncionariosExpandidos") 'onde tenho os dados a ler
numreg = rs.RecordCount

ano = 2000 'define o ano de inicio da contagem
anofim = Year(Date) 'termina quando o ano for igual ao actual


Do While ano <= anofim 'faz enquanto o ano for menor igual à data actual
strSQL = "INSERT INTO tbl_FuncActivosAno (ano, qtdActivos) IN 'C:\Formacao_DGTF.accdb'" & "SELECT qry_FuncionariosExpandidos.anoEntrada, numreg " & " FROM qry_FuncionariosExpandidos WHERE qry_FuncionariosExpandidos.anoEntrada<= ano And ano <= qry_FuncionariosExpandidos.anoSaida" 'a ideia aqui é que sejam acrescentados os registo na tbl_funcActivosAno cujo anoEntrada seja <= ao ano que está na variavel ano e ao mesmo tempo que o valor da variavel ano seja <= ao anoSaida
ano = ano + 1 'incrementa a variavel ano
Loop

End Sub

Olá MCC

Li e pareceu-me alguma incongruência entre o que pretende e o que o código SQL apresentado faz.
Aqui, à pressa, adaptei o código SQL apresentado que diz fazer o que pretende. Falta a parte que não completou

Sub CriaTabelaMCC()
Dim AnoInicial As Integer
Dim AnoFinal As Integer

AnoInicial = 2000
AnoFinal = 2012

CurrentDb.Execute "DROP TABLE qtd_trabalhadores;"
CurrentDb.OpenRecordset "INSERT INTO qtd_trabalhadores (ano, qtd) SELECT " & AnoInicial & ", COUNT(*) FROM Funcionarios WHERE YEAR(Anoinicio) <=" & AnoInicial & " AND " & AnoInicial & " <= YEAR(anofim);"
AnoInicial = AnoInicial + 1
While AnoInicial <= AnoFinal
CurrentDb.Execute "INSERT INTO qtd_trabalhadores (ano, qtd) SELECT " & AnoInicial & ", COUNT(*) FROM Funcionarios WHERE YEAR(Anoinicio) <=" & AnoInicial & " AND " & AnoInicial & "<= YEAR(anofim)"
AnoInicial = AnoInicial + 1
Loop
'select * from #qtd_trabalhadores...... 'código incompleto
End Sub

Caso não seja isto, explique detalhadamente:
- nome da tabela (ou consulta), tipo de dados e caminho da bd de origem dos dados
- nome da tabela e campos que pretende criar.

Cumprimentos,

Boa noite Alexandre,

Muito obrigada pela ajuda, estive a experimentar o codigo que mandou mas deve faltar qualquer coisinha, ou eu não estou a fazer bem.

Envio em anexo um bd só com a tabela funcionarios com dados teste.

A função criada está no evento ao abrir do formulario entrada.

Quanto ao código em SQL, a última linha do código não era nada...fiz uma bd teste em sql só com a tabela funcionarios para experimentar, corri aquele código e funcionava bem, criava a tabela direitinha com os valores certos.

obg,
mcc

Olá, mcc
Sou do Porto, serás minha vizinha?

Aqui vai:

Dim AnoInicial As Integer
Dim AnoFinal As Integer

AnoInicial = 1990
AnoFinal = 2012

CurrentDb.Execute "DROP TABLE qtd_trabalhadores"
CurrentDb.Execute "SELECT " & AnoInicial & " as ano, COUNT(dtentrada) as qtd INTO qtd_trabalhadores FROM Funcionarios WHERE YEAR(dtentrada) <=" & AnoInicial & " AND (IsNull(DtSaida) or " & AnoInicial & " <= YEAR(dtsaida));"
AnoInicial = AnoInicial + 1
Do While AnoInicial <= AnoFinal
CurrentDb.Execute "INSERT INTO qtd_trabalhadores (ano, qtd) SELECT " & AnoInicial & ", COUNT(*) FROM Funcionarios WHERE YEAR(dtentrada) <=" & AnoInicial & " AND (IsNull(DtSaida) or " & AnoInicial & " <= YEAR(dtsaida));"
AnoInicial = AnoInicial + 1
Loop
Cumprimentos,

Perfeito

Muito obrigada, que enorme ajuda, estou mesmo muito agradecida.
Somos quase vizinhos, sou de Lisboa

Obrigada a todos pela vossa atitude, pelo tempo que disponibilizam, pela valiosa informação que partilham.

Continuação do excelente trabalho