Soma Acumulada por Transação e Conta, ordenado por Data

Olá, boa tarde.

Eu gostaria de criar uma consulta pela qual eu desejava ver a soma acumulada do valor (neste caso é "Diferença"), organizado por conta e por ordem de data.
Ou seja, uma conta pode ter no dia 01/01/2020 o valor de -10, no dia seguinte -20 e -30, e eu queria fazer a soma acumulada, -10, -30, -60, assim sucessivamente. Cada registo tem um identificador: [ID Transação], que pode não estar ordenado pela ordem que quero.

Gostaria de ajuda para completar o meu código SQL já que não está a dar os resultados que eu pretendia.

O código atual do meu SQL é:

1. SELECT A.Diferença, A.[ID Transação], A.Data, A.Número_Conta, Sum(B.Diferença) AS Saldo
2. FROM Consulta8 AS A
3. INNER JOIN Consulta8 AS B ON (A.Número_Conta=B.Número_Conta) AND (A.Data>=B.Data)
4. GROUP BY A.Diferença, A.[ID Transação], A.Data, A.Número_Conta, A.[ID Transação]
5. ORDER BY A.Número_Conta, A.Data DESC;

O problema pelo qual me encontro é que eu posso ter transações dentro do mesmo dia, só que se eu acrescentar à linha 3 AND (A.[ID Transação]>=[ID Transação]) não me irá calcular a soma acumulada por conta e por ordem descendente de data, vai dar outros resultados por causa da ordem do [ID Transação].
Isto porque dentro do mesmo dia, o [ID Transação] pode não estar ordenado.

zcorneliu escreveu:dentro do mesmo dia, o [ID Transação] pode não estar ordenado.

Se o ID Transação não estará ordenado, então não há o que fazer para calcular corretamente os valores de um mesmo dia.

A sugestão é acrescentar na data também a hora.

Obs.: Cuidado! Procure não usar palavras reservadas para nomear campos. Veja o link abaixo
https://www.maximoaccess.com/t198-palavras-reservadas-do-access

Não pode ser pela hora, porque 2 transações pertencem a uma mesma "compra", ou seja.

Isto é como o sistema de Débito e Crédito.

Tenho uma "transação" (é assim que o chamo, pode ser outro nome) onde um crédito equivale a uma débito, pelo que será sempre ao mesmo tempo, daí o ID ser o identificador.

Vou anexar uma imagem com as relações em causa.

Eu não sei se dá para utilizar a sintaxe CASE ELSE ou o Count quando for datas repetidas...

Já estou à algum tempo tentar resolver este problema....

Está aqui a BD com alguns exemplos.

Eu queria a soma acumulada por Conta do campo [Diferença].

Uma solução possível mas que traz outros problemas é criar um relatório.

Na base de dados que envio em anexo, o relatório chamado "Consulta8" está disposto como eu quero. A 2.ª coluna chamada "Diferença" é o saldo da conta.

Mas o problema surge quando eu quero dispor a informação de outra forma como por exemplo no relatório chamado "Consulta9", onde tento utilizar a função DPesquisar, mas dá-me erro.

Boa tarde
Não entendi. No relatório consulta8 diz que está como quer, então onde está a sua dúvida?

Eu queria, por exemplo, dispor a informação agrupando 1⁰ por Mês ou só de um mês pedido.
Isso depois não dá o saldo real acumulado como no relatório da Consulta8.

Só disponibilizou dados dum mês, o que não são significativos para a finalidade que pretende
No entanto, deverá funcionar assim:

Coloque o seguinte código (atenção aos nomes reservados do Access)

Código:

Private Sub Report_Open(Cancel As Integer)
    Dim strMes As String
    strMes = InputBox("Introduza o mês a listar (formato mmaaaa)" & vbCr & "Para listar tudo mantenha em branco")
    Me.Filter = "Format(Data,'mmyyyy')=" & strMes
    Me.FilterOn = True
End Sub

Não seria possível fazer uma consulta?
Preferia que fosse uma consulta para uma maior flexibilidade para o uso da informação.

Boa tarde
SELECT tblContasDasEntidades.Número_Conta, tblCompras.Data, TABELA_MOVIMENTOS.Quantidade, TABELA_MOVIMENTOS.[Débito Preço Unitário], TABELA_MOVIMENTOS.[Crédito Preço Unitário], [Débito Preço Unitário]-[Crédito Preço Unitário] AS Diferença, TABELA_MOVIMENTOS.[ID Transação]
FROM (TABELA_MOVIMENTOS INNER JOIN tblCompras ON TABELA_MOVIMENTOS.[ID Compra] = tblCompras.[ID Compra]) INNER JOIN tblContasDasEntidades ON TABELA_MOVIMENTOS.[ID Conta Movimento] = tblContasDasEntidades.ID_Conta_Movimento_ContaEnt WHERE Format(Data,'mmyyyy')=[Introduza o mês a listar no formato mmaaaa] ORDER BY tblContasDasEntidades.Número_Conta, tblCompras.Data DESC;

O objetivo é fazer a soma cumulada linha a linha, agrupado pelas contas e a soma acumulada tinha de ser de data descendente, ou seja, somar do mais antigo para o mais recente.

É como este código SQL. Mas o problemas deste código é que faz por data e não por ID Transação (nota: o ID Transação não está ordenado como a data porque pode ser inserido um novo com uma data mais antiga).

SELECT A.Diferença, A.[ID Transação], A.Data, A.Número_Conta, Sum(B.Diferença) AS Saldo
FROM Consulta8 AS A INNER JOIN Consulta8 AS B ON (A.Número_Conta=B.Número_Conta) AND (A.Data>=B.Data)
GROUP BY A.Diferença, A.[ID Transação], A.Data, A.Número_Conta, A.[ID Transação]
ORDER BY A.Número_Conta, A.Data DESC;

Bom dia
No começo da dúvida pediu por data e agora diz que não é por data mas por id de transacção?
Com os dados que indicou, explique que resultado pretende

A dúvida inicial foi sempre fazer a soma acumulada por lD Transação (é o que identifica a linha).

O código que utilizo é este e faz a soma acumulada por conta E por data, mas como pode haver uma data repetida, não irá fazer a soma linha à linha,

Por exemplo o resultado deste código é:

ID Transação ; Número_Conta ; Data ; Diferença
1 ; 125 ; 1/2/2020 ; 1
2 ; 125 ; 2/2/2020 ; 2
3 ; 125 ; 2/2/2020 ; 2
4 ; 125 ; 3/2/2020 ; 3
5 ; 125 ; 4/2/2020 ; 4

SELECT A.Diferença, A.[ID Transação], A.Data, A.Número_Conta, Sum(B.Diferença) AS Saldo
FROM Consulta8 AS A INNER JOIN Consulta8 AS B ON (A.Número_Conta=B.Número_Conta) AND (A.Data>=B.Data)
GROUP BY A.Diferença, A.[ID Transação], A.Data, A.Número_Conta, A.[ID Transação]
ORDER BY A.Número_Conta, A.Data DESC;

Boa noite,
Tem de explicar os valores e datas que indicou
Não entendo de onde vêm os valores nem as datas que indicou

tblCompras tem: Chave primária: [ID Compra] e [Data]

TABELA_MOVIMENTOS tem: Chave primária (automática): [ID Transação], [ID Compra], [ID Conta Movimento] , [Quantidade], [Débito Preço Unitário] e [Crédito Preço Unitário]

Estão ligadas pelo [ID Compra]

tblContasDasEntidades tem: Chave Primária: [ID Conta Movimento] e [Número Conta]

Está liga à tabela TABELA_MOVIMENTOS pelo [ID Conta Movimento]




Criei a consulta onde obtenho um novo campo calculado: [Diferença] que é = [Quantidade]*([Débito Preço Unitário] - [Crédito Preço Unitário])

O que queria era a soma acumulada do campo [Diferença] por cada [Número Conta] e por [Data] descendente.

Na TABELA_MOVIMENTOS se o [Débito Preço Unitário] maior que 0, o valor do [Crédito Preço Unitário] é 0; o contrário também.



Eu posso criar vários registos na tabela TABELA_MOVIMENTOS em que [Número Conta] e [Data] sejam iguais, por que, num mesmo [ID Compra] posso ter no mesmo [Número Conta] com um valor de [Débito Preço Unitário] e [Crédito Preço Unitário] iguais ou diferentes... numa mesma [Data]. Daí eu criar o campo [Diferença] e querer o soma acumulada, para saber o "Saldo" da conta.

Bom dia
Em nenhuma tabela encontro os dados que indicou

Esta é uma nova Base de Dados.

Pode ver na "Consulta9" que na conta número 15162521255, no campo [Saldo] o valor que queria era 50€ -> 66€ -> 71€ mas está 50€ -> 66€ -> 66€ porque tem a mesma data (eu estou a ver de baixo para cima).

Boa tarde
Tem de explicar tudo de com base nos mesmos dados
Com os últimos dados, não vejo como quer que apareça 50€ -> 66€ -> 71€ . De onde vem o 66?

Não tenho comigo agora a base da e dados, mas é
50+16= 66
66+5=71
Etc...
É a soma acumulada do campo [Diferença] ... por cada Conta ....
O 66 está a repetir porque são da mesma data...

Já consegui descobrir como fazer o que queria a partir deste website:

http://azlihassan.com/apps/articles/microsoft-access/queries/running-sum-total-count-average-in-a-query-using-a-correlated-subquery

Eu queria perguntar se podia criar uma restrição nos formulários.

Eu queria que restringir a introdução de dados no formulário "frmlCompras".

Ou seja, eu queria que o Total do campo [Débito] e [Crédito] do subformulário "frmlCompras_Movimentos sub" fossem iguais.

Ou seja, por cada [ID_Compra] tem que se ter um Total de [Crédito] e [Débito] iguais.