Adding cascading data from a column in Access

Question

Adding cascading data from a column in Access

Navigation

#1 by (1 votes)

1

Hello

I need to perform a query that returns me 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 the records are over. Always taking the result of the subtraction of the products that came with the products that came out and adding up with the balance of the registry immediately above.

I have no idea where to start ...

vba ms-access

asked by anonymous 04.12.2015 / 17:23

1 answer

How to create custom stages with Capistrano 3? Error java.lang.NoClassDefFoundError in Java WEB with JSF

score 1 · Answer 1

Hello

Considering that the structure of the table containing the data looks 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 YearDate column, 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;

Query result:

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