Add cascading data from a column in Access

Asked

Viewed 920 times

1

Hello!

I need to make a query that returns the weekly balance of certain products in stock. For example:

Ano/Semana | Saíram | Entraram | Saldo
______________________________________
2015/01    | 4      | 10       | [Entraram] - [Saíram]
2015/02    | 7      | 2        | ([Entraram] - [Saíram]) + Saldo da linha acima

And so did the records. Always taking the result of subtraction of the products that came in with the products that came out and adding with the balance of the record immediately above.

I have no idea where to start...

vba ms-access

  • Dude, I tried to format your table. Since I was a little confused I don’t know if it’s right, if I’m wrong let me know that I undo the editing =)

  • That’s it! Thanks, it’s my first question to the site...

  • It’s I saw it’s your first question. Quiet, in time you get the hang of it.

1 answer

1


Hello!

whereas the structure of the table containing the data is something like:

Data        AnoSemana  Sairam  Entraram
01/01/2016  2016/01     5       10
01/02/2016  2016/06     3       8
01/03/2016  2016/10    9       6
01/04/2016  2016/14    3       5

Note: if you do not have the column Year-week, you can use the following function: Format([data];"yyyy") & "/" & Format(PartData("ww";[data]);"00").

Follow the SQL query to generate the statement per week:

SELECT 
    ESTOQUE.AnoSemana
    , ESTOQUE.ENTRARAM
    , ESTOQUE.SAIRAM
    , (SELECT Sum(Nz(ENTRARAM)-Nz(SAIRAM))
       FROM ESTOQUE AS TB
       WHERE TB.AnoSemana<=ESTOQUE.AnoSemana) AS SALDO
FROM ESTOQUE
ORDER BY ESTOQUE.Data;

Outcome of the consultation:

AnoSemana   ENTRARAM    SAIRAM  SALDO
2016/1      10          5       5
2016/6      8           3       10
2016/10     6           9       7
2016/14     5           3       9

Browser other questions tagged vba ms-access

You are not signed in. Login or sign up in order to post.