[Resolvido]Soma últimos 12 registros da tabela

Bom dia!

Estou precisando de um auxílio com a questão abaixo:

Tenho uma tabela com três campos: empresa, mês, valor, preciso desenvolver uma função que some somente os últimos 12 registros da tabela por empresa, exemplo: se na tabela tenho 20 registros (20 meses) a função deverá somar os últimos 12 e desconsiderar os 8 primeiros registros.

Desde já agradeço.

Regras do Fórum
1. Tenha paciência com quem coloca aqui as suas duvidas.
2. Clareza nos títulos dos tópicos e duvidas, evite a linguagem MSN.
3. Não colocar diversas vezes a mesma dúvida.
4. Explicar detalhadamente o problema e informar a versão do seu Office + Sistema Operativo.
5. Retorne sempre se deu certo, esse retorno é muito importante.
6. Respeite toda a equipe Staff e demais membros deste fórum.
7. Use sempre o botão Busca, sempre que tiver uma dúvida e antes de abrir tópicos.
8. Não usar palavras como "Urgente". O fórum é livre e ninguém é obrigado a responder com urgência.
9. A publicidade é proíbida e só pode ser feita pelo Administrador ou depois de aprovada pelo Administrador.
10. Se o seu tópico não for respondido, tem o direito de fazer um Up ao final de 24horas!
11. A colocação de Códigos ou Exemplos, devem constar sempre os créditos de quem o elaborou.
12. Fale, não GRITE! Só letras Maiúsculas, na Internet é o mesmo que gritar!
13. É proibido tirar duvidas nas Salas de Repositório, devem abrir um novo tópico nas salas de duvidas, relacionando o Exemplo em causa.
14. É proibido adicionar links externos nos tópicos, que redirecionem para outros sites ("backlinks"), excepto DropBox ou do próprio fórum.
15. É permitido adicionar links externos, nos códigos VBA dentro das Base de Dados.

Por favor, adeque seu título a Regra 12 do Fórum.

Boa tarde

Quer postar a tabela?

Segue tabela.

O campo a ser somado os últimos 12 meses esta denominado como [rbpa] e salvar a soma dos últimos 12 meses no campo [rbt12] do último mês.

Desde já agradeço!

Teste

Assis, acho que não consegui passar o que realmente preciso, vou tentar explicar:

A função deverá somar os últimos 12 meses (de acordo com a data) por empresa e lançar o valor da soma no campo [rbt12] deverá somar os últimos 12 meses anterior ao mês lançado no campo [rbpa], não é somente a soma no final da tabela, exemplo:

No campo [rbt12] do registro do mês 01/2020, deverá estar a somar os meses de 01/2019 até 12/2019, no mês 02/2020 no campo [rbt12] deverá somar os meses de 02/2019 até 01/2020 e assim sucessivamente.

Alterei a tabela e estou anexando novamente.

Boa noite,
Em modo SQL
SELECT Emp as Empresa, Mes, SomaMes, rbt12 FROM (SELECT *, (SELECT Sum(rbpa) FROM Tabela1 as T WHERE Format(periodo,'mmmm-yyyy') between DateAdd('m',-12,Mes) and Mes and Empresa=Emp) as rbt12 FROM (SELECT Empresa as Emp, Format(periodo,'mmmm-yyyy') as Mes,Sum(rbpa) as SomaMes FROM Tabela1 as T GROUP BY Empresa,Format(periodo,'mmmm-yyyy')))

Alexandre boa tarde!

Quase isso, preciso que some de acordo com a ordenação dos meses e não considere o mês atual na soma, somente os 12 meses anteriores, exemplo:


empresa | mês | rbpa | rb12 |

1 01/2019 R$ 100,00 0,00
1 02/2019 R$ 100,00 100,00
1 03/2019 R$ 200,00 200,00
1 04/2019 R$ 500,00 400,00
1 05/2019 R$ 700,00 900,00
1 06/2019 R$ 1000,00 1600,00
1 07/2019 R$ 1500,00 2600,00
1 08/2019 R$ 2000,00 4100,00
1 09/2019 R$ 2500,00 6100,00
1 10/2019 R$ 3000,00 8600,00
1 11/2019 R$ 3500,00 11600,00
1 12/2019 R$ 4000,00 15100,00

Boa tarde,
Será isto
SELECT Emp as Empresa, Mes, SomaMes, rbt12 FROM (SELECT *, (SELECT Sum(rbpa) FROM Tabela1 as T WHERE Format(periodo,'mmmm-yyyy') between DateAdd('m',-13,Mes) and DateAdd('m',-1,Mes) and Empresa=Emp) as rbt12 FROM (SELECT Empresa as Emp, Format(periodo,'mmmm-yyyy') as Mes,Sum(rbpa) as SomaMes FROM Tabela1 as T GROUP BY Empresa,Format(periodo,'mmmm-yyyy')))

Não, a função não somou e tbm não ordenou os meses, preciso que ordene os meses conforme exemplo abaixo.


empresa | mês | rbpa | rb12 |

1 01/2019 R$ 100,00 0,00
1 02/2019 R$ 100,00 100,00
1 03/2019 R$ 200,00 200,00
1 04/2019 R$ 500,00 400,00
1 05/2019 R$ 700,00 900,00
1 06/2019 R$ 1000,00 1600,00
1 07/2019 R$ 1500,00 2600,00
1 08/2019 R$ 2000,00 4100,00
1 09/2019 R$ 2500,00 6100,00
1 10/2019 R$ 3000,00 8600,00
1 11/2019 R$ 3500,00 11600,00
1 12/2019 R$ 4000,00 15100,00

Boa tarde,
Será assim
SELECT * FROM (SELECT Emp as Empresa, Mes, SomaMes, rbt12 FROM (SELECT *, (SELECT Sum(rbpa) FROM Tabela1 as T WHERE periodo between DateAdd('m',-13,Mes) and DateAdd('m',-1,Mes) and Empresa=Emp) as rbt12 FROM (SELECT Empresa as Emp, Format(periodo,'mm-yyyy') as Mes,Sum(rbpa) as SomaMes FROM Tabela1 as T GROUP BY Empresa,Format(periodo,'mm-yyyy')))) ORDER BY Empresa,Year(Mes),Month(Mes)

Isso mesmo, muito obrigado!

Surgiu uma nova demanda agora, preciso implementar na função a seguinte regra: quando o período de meses for menor que 13 meses conforme abaixo:

No 1° mês de atividade: multiplicar a receita do próprio mês por 12;

Nos 11 meses posteriores ao início de atividades: apurar a média aritmética em cada mês e multiplicar por 12: (rbpa / número de meses corridos) x 12 = Receita Total;

empresa | mês | rbpa | rb12 |
cálculo
1 01/2019 R$ 100,00 1200,00 (primeiro mês = 100,00 / 1 *12= 1200,00)
1 02/2019 R$ 100,00 1200,00 (mês anterior= 100,00 / 1 *12= 1200,00)
1 03/2019 R$ 200,00 1200,00 (soma dois primeiros meses = 200,00 / 2 *12= 1200,00)
1 04/2019 R$ 500,00 1600,00 (soma tres primeiros meses = 400,00 / 3 *12= 1600,00)


No 13° mês normaliza e deve ser adotada a receita acumulada nos últimos 12 meses anteriores de acordo com a função
SELECT Emp as Empresa, Mes, SomaMes, rbt12 FROM (SELECT *, (SELECT Sum(rbpa) FROM Tabela1 as T WHERE Format(periodo,'mmmm-yyyy') between DateAdd('m',-12,Mes) and Mes and Empresa=Emp) as rbt12 FROM (SELECT Empresa as Emp, Format(periodo,'mmmm-yyyy') as Mes,Sum(rbpa) as SomaMes FROM Tabela1 as T GROUP BY Empresa,Format(periodo,'mmmm-yyyy')))


Desde já agradeço a atenção.

Bom dia
Coloque a função

Código:

Function calcRb12(intEmpresa As Integer, dtData As Date) As Double
    '--------------------------------------------------------------'
    '   código criado por Alexandre Neves, do Fórum MaximoAccess   '
    '     utilize o código livremente mas mantenha os créditos     '
    '--------------------------------------------------------------'
    Dim Rst As DAO.Recordset, RstTotal As DAO.Recordset, bytMes As Byte
    Set Rst = CurrentDb.OpenRecordset("SELECT periodo,rbpa FROM Tabela1 WHERE empresa=" & intEmpresa)
    bytMes = 0
    Do
        bytMes = bytMes + 1
        If dtData = Rst("periodo") Then
            If bytMes = 1 Then
                calcRb12 = Rst("rbpa") * 12
                GoTo FimRst
            ElseIf bytMes < 13 Then
                Set RstTotal = CurrentDb.OpenRecordset("SELECT Sum(rbpa) FROM Tabela1 WHERE empresa=" & intEmpresa & " and periodo<#" & Format(dtData, "mm-dd-yyyy") & "#")
                calcRb12 = 12 * (RstTotal(0) / (bytMes - 1))
                GoTo FimRst
            Else
                Set RstTotal = CurrentDb.OpenRecordset("SELECT Sum(rbpa) FROM Tabela1 WHERE empresa=" & intEmpresa & " and periodo between DateAdd('m',-13,#" & Format(dtData, "mm-dd-yyyy") & "#) and DateAdd('m',-1,#" & Format(dtData, "dd-mm-yyyy") & "#)")
                calcRb12 = RstTotal(0)
                GoTo FimRst
            End If
            Exit Do
        End If
        Rst.MoveNext
    Loop
FimRst:
    Set Rst = Nothing
End Function


Consulta em SQL
SELECT empresa, format(periodo,'mm-yyyy') AS Mes, rbpa, calcRb12(Empresa,periodo) AS rb12 FROM Tabela1 ORDER BY empresa, Year(periodo), Month(periodo);

Alexandre boa tarde!

Ocorreu erro de execução na linha:

calcRb12 = 12 * (RstTotal(0) / (bytMes - 1))

Boa noite,
Não sei que tabela está a usar. Usei a que o Assis anexou

Utilizei a mesma, pode anexar a que utilizou?

Desde já agradeço!

Consegui identificar o erro, funcionou, porém a partir do mês 13 deverá somar os últimos 12 meses, a soma do 14 mês (02/2021) está incorreta, deverá considerar os últimos 12 e assim sucessivamente, conforme o exemplo abaixo.


Mes rbpa rb12 realizado calculo correto
01-2020 R$ 100,00 R$ 1.200,00 R$ 1.200,00
02-2020 R$ 160.000,00 R$ 1.200,00 R$ 1.200,00
03-2020 R$ 720.000,00 R$ 960.600,00 R$ 960.600,00
04-2020 R$ 1.800.000,00 R$ 3.520.400,00 R$ 3.520.400,00
05-2020 R$ 100,00 R$ 8.040.300,00 R$ 8.040.300,00
06-2020 R$ 4.800.000,00 R$ 6.432.480,00 R$ 6.432.480,00
07-2020 R$ 3.000.000,00 R$ 14.960.400,00 R$ 14.960.400,00
08-2020 R$ 900.000,00 R$ 17.966.057,14 R$ 17.966.057,14
09-2020 R$ 5.000,00 R$ 17.070.300,00 R$ 17.070.300,00
10-2020 R$ 600,00 R$ 15.180.266,67 R$ 15.180.266,67
11-2020 R$ 1.000,00 R$ 13.662.960,00 R$ 13.662.960,00
12-2020 R$ 2.000,00 R$ 12.421.963,64 R$ 12.421.963,64
01-2021 R$ 370.000,00 R$ 11.388.800,00 R$ 11.388.800,00
02-2021 R$ 100,00 R$ 11.388.800,00 R$ 11.758.700,00
03-2021 R$ 160.000,00 R$ 11.388.700,00 R$ 11.598.800,00
04-2021 R$ 720.000,00 R$ 11.228.700,00 R$ 11.038.800,00
05-2021 R$ 1.800.000,00 R$ 10.508.700,00 R$ 9.958.800,00
06-2021 R$ 100,00 R$ 8.708.700,00 R$ 11.758.700,00
07-2021 R$ 4.800.000,00 R$ 8.708.600,00 R$ 6.958.800,00
08-2021 R$ 3.000.000,00 R$ 3.908.600,00 R$ 8.758.800,00

Bom dia
Isso terá a ver com formatação da data na função
Por agora, estou muitissimo ocupado mas tente ajustar a formatação da data

Sim, consegui identificar o erro, funcionou, porém a partir do mês 13 deverá somar os últimos 12 meses, a soma do 14 mês (02/2021) está incorreta, deverá considerar os últimos 12 e assim sucessivamente, conforme o exemplo abaixo.


Mes - rbpa - rb12 -realizado - calculo correto
01-2020 R$ 100,00 - R$ 1.200,00 - R$ 1.200,00
02-2020 R$ 160.000,00 - R$ 1.200,00 - R$ 1.200,00
03-2020 R$ 720.000,00 - R$ 960.600,00 - R$ 960.600,00
04-2020 R$ 1.800.000,00 - R$ 3.520.400,00 - R$ 3.520.400,00
05-2020 R$ 100,00 - R$ 8.040.300,00 - R$ 8.040.300,00
06-2020 R$ 4.800.000,00 - R$ 6.432.480,00 - R$ 6.432.480,00
07-2020 R$ 3.000.000,00 - R$ 14.960.400,00 - R$ 14.960.400,00
08-2020 R$ 900.000,00 - R$ 17.966.057,14 - R$ 17.966.057,14
09-2020 R$ 5.000,00 - R$ 17.070.300,00 - R$ 17.070.300,00
10-2020 R$ 600,00 - R$ 15.180.266,67 - R$ 15.180.266,67
11-2020 R$ 1.000,00 - R$ 13.662.960,00 - R$ 13.662.960,00
12-2020 R$ 2.000,00 - R$ 12.421.963,64 - R$ 12.421.963,64
01-2021 R$ 370.000,00 - R$ 11.388.800,00 - R$ 11.388.800,00
02-2021 R$ 100,00 - R$ 11.388.800,00 - R$ 11.758.700,00
03-2021 R$ 160.000,00 - R$ 11.388.700,00 - R$ 11.598.800,00
04-2021 R$ 720.000,00 - R$ 11.228.700,00 - R$ 11.038.800,00
05-2021 R$ 1.800.000,00 - R$ 10.508.700,00 - R$ 9.958.800,00
06-2021 R$ 100,00 - R$ 8.708.700,00 - R$ 11.758.700,00
07-2021 R$ 4.800.000,00 - R$ 8.708.600,00 - R$ 6.958.800,00
08-2021 R$ 3.000.000,00 - R$ 3.908.600,00 - R$ 8.758.800,00

Boa noite,
Pelo tempo que anda por cá já podia saber corrigir o erro, não apenas localizá-lo
Veja agora

Código:

Function calcRb12(intEmpresa As Integer, dtData As Date) As Double
    '--------------------------------------------------------------'
    '   código criado por Alexandre Neves, do Fórum MaximoAccess   '
    '     utilize o código livremente mas mantenha os créditos     '
    '--------------------------------------------------------------'
    Dim Rst As DAO.Recordset, RstTotal As DAO.Recordset, bytMes As Byte
    Set Rst = CurrentDb.OpenRecordset("SELECT periodo,rbpa FROM Tabela1 WHERE empresa=" & intEmpresa)
    bytMes = 0
    Do
        bytMes = bytMes + 1
        If dtData = Rst("periodo") Then
            If bytMes = 1 Then
                calcRb12 = Rst("rbpa") * 12
                GoTo FimRst
            ElseIf bytMes < 13 Then
                Set RstTotal = CurrentDb.OpenRecordset("SELECT Sum(rbpa) FROM Tabela1 WHERE empresa=" & intEmpresa & " and periodo<#" & Format(dtData, "mm-dd-yyyy") & "#")
                calcRb12 = 12 * (RstTotal(0) / (bytMes - 1))
                GoTo FimRst
            Else
                Set RstTotal = CurrentDb.OpenRecordset("SELECT Sum(rbpa) FROM Tabela1 WHERE empresa=" & intEmpresa & " and periodo<#" & Format(dtData, "mm-dd-yyyy") & "# and periodo>#" & Format(DateAdd("m", -13, dtData), "mm-dd-yyyy") & "#")
                calcRb12 = RstTotal(0)
                GoTo FimRst
            End If
            Exit Do
        End If
        Rst.MoveNext
    Loop
FimRst:
    Set Rst = Nothing
End Function

Deu certo, muito obrigado!