Previous Row

[SiGill]

Hi

I need to get the closing stock from the previous day for the same department. See below example.
So for department 111 I need the closing stock for the previous due date. This then becomes the opening stock for that day, hope that makes sense
At the moment I have manually done it, but I need Access query to do it for me.
Any Ideas?

 

[Gasman]

Is that even correct?
You appear to double the Order values?

 

[SiGill]

Is that even correct?
You appear to double the Order values?

This is just example data, ignore the fact that the closing stock doesn't tally up to the opening, arrivals and orders lol
Just need to know how to get the closing stock from the previous row

 

[plog]

ignore the fact that the closing stock doesn't tally up to the opening, arrivals and orders

I can't, because if they are suppose to be in a mathematical relationship then you have set up your table improperly and what you want to achieve with this is the wrong way to do it.

If The OpeningStock of Day 2 is equal to the OpeningStock of Day 1 plus Arrivals of Day 1 minus Orders for Day 1 then you don't look up the prior ClosingStock for Day 1. In fact, you don't "look up" anything, you "add up" all the relevant Arrivals and Orders and caclulate it. To achieve that, Orders and Arrivals should be in the same field (Arrivals having positive numbers and Orders having negative numbers).

So, I think you need to put this query aside and fix your table(s).

 

[SiGill]

Thanks plog, I'll revisit them. When you say 'Arrivals and Orders should be in the same field' what do you mean by that? I need to see them in my output. How can I see them if they are in the same field?

How do I sum the fields of a previous day?

 

[MajP]

In general If there are no gaps you can simply drop the table in twice the the query and join where A.Dept = B.Dept AND A.DueDate = B.DueDate - 1

However if there are gaps then you need to do something like

SELECT A.dept,
       A.duedate,
       A.closingstock,
       (SELECT TOP 1 B.closingstock
        FROM   table1 AS B
        WHERE  ( A.dept = b.dept
                 AND A.duedate > B.duedate )
        ORDER  BY B.duedate DESC,
                  B.id DESC) AS PreviousClose
FROM   table1 AS A
ORDER  BY A.dept,
          A.duedate;

This is in general for getting the previous row information. I will let others comment on the correctness of you table.

 

[SiGill]

In general If there are no gaps you can simply drop the table in twice the the query and join where A.Dept = B.Dept AND A.DueDate = B.DueDate - 1

However if there are gaps then you need to do something like

SELECT A.dept,
       A.duedate,
       A.closingstock,
       (SELECT TOP 1 B.closingstock
        FROM   table1 AS B
        WHERE  ( A.dept = b.dept
                 AND A.duedate > B.duedate )
        ORDER  BY B.duedate DESC,
                  B.id DESC) AS PreviousClose
FROM   table1 AS A
ORDER  BY A.dept,
          A.duedate;

This is in general for getting the previous row information. I will let others comment on the correctness of you table.

Thanks MajP I've just managed to do something similar, nearly got it working

 

[The_Doc_Man]

@SiGill - just as an explanation... IF you are working with tables directly, you need to know that they are not necessarily in any obvious order. Particularly if there have been table updates, the order of appearance of records in a table is not predictable. MajP's suggestion shows you how to impose order where there was none.

The REASON for not having a strong or dependable order in a table is that Access is based on Set Theory, for which the concept of "member order" is irrelevant. Therefore, you need to be aware that TECHNICALLY, when you ask for data from a previous record, there IS no previous record. At least, not reliably so. The query has to impose an order so that you CAN ask for a previous record.

Once you have identified a "previous record" based on your criteria (and date is a perfectly valid criterion for ordering), you can pick multiple fields out of that previous record pretty easily, as shown by MajP.

 

[SiGill]

I am really struggling with this and am starting to pull my hair out, I can't get my head around it.

23rd March is the start of my data. We will be closing on 5945. I need the 24th March Opening Stock to be the 23rds closing stock.
Then I need the 24ths Closing Stock to add on the arrivals and orders. Is it possible? If so how can I do it?

Below is my attempt to get the closing stock. Its takes forever to run, then doesn't give me what I want anyway.

 

[SiGill]

Update. Managed to get the 23rds closing stock with this

SELECT f1.Year, f1.Week, f1.Date, f1.[Combined Dept], (SELECT TOP 1 f2.ClosingStock FROM qryRollingForecastWorking f2 WHERE f2.Date = (f1.Date-1) and f2.[Combined Dept] = f1.[Combined Dept]) AS OpeningStock, f1.Arrivals, f1.Orders, f1.ClosingStock
FROM qryRollingForecastWorking AS f1;

But it still take a while to run. IS there anyway I can make it quicker?

 

[MajP]

First I will say I am not an expert on stock or accounting. That is not my type of DB. Others on this forum are. I do not believe you ever store closing and opening stock, but this is calculated dynamically. However from what I see you are actually trying to store those values in opening stock which this query is not going to do.

if you want to save the opening and closing instead of dynamically calculating this, that can be done extremely fast in a recordset loop. I can demo this if you send me that table. Could be a db or excel.
Again I am not recommending this because I am not sure if this is the way to do stock. I defer to the experts here. But I can demo if you want.
The problems with storing opening and closing is that if you go back and change orders, arrivals it will not recalculate automatically. That is the problem with any stored calculation.

 

[SiGill]

First I will say I am not an expert on stock or accounting. That is not my type of DB. Others on this forum are. I do not believe you ever store closing and opening stock, but this is calculated dynamically. However from what I see you are actually trying to store those values in opening stock which this query is not going to do.

if you want to save the opening and closing instead of dynamically calculating this, that can be done extremely fast in a recordset loop. I can demo this if you send me that table. Could be a db or excel.
Again I am not recommending this because I am not sure if this is the way to do stock. I defer to the experts here. But I can demo if you want.
The problems with storing opening and closing is that if you go back and change orders, arrivals it will not recalculate automatically. That is the problem with any stored calculation.

Hi MajP The only record I am storing is the initial closing stock, the rest is calculated. Unfortunately I can't send you the DB as the data is sitting in SQL tables. I am wondering whether I stick them all in the same field, I just clicked what plog meant now when he mentioned putting them in same field. and then do a few queries based on them, then piece them back together.

 

[MajP]

The only record I am storing is the initial closing stock, the rest is calculated.

Thats good. Can you do a small Excel dump like the screen shot?

 

[plog]

Here's how you achieve what you want with my table structure:

YourTable
Dept, DueDate, Stock
111, 23/3/21, 5945
111, 24/3/21, 3000
111, 24/3/21, -616
111, 25/3/21, -735
111, 26/3/21, -697
111, 27/3/21, 2728
111, 27/3/21, -525

First you make a subquery to get all the unique date/department combinations:

SELECT YourTable.Dept, YourTable.DueDate
FROM YourTable
GROUP BY YourTable.Dept, YourTable.DueDate;

Name that 'sub1'. Then you can use a correlated subquery to get the Opening and Closing Stock for each Dept/Date by simply summing the Stock value with this query:

SELECT sub1.Dept, sub1.DueDate
  , (SELECT SUM(Stock) FROM YourTable WHERE YourTable.DueDate<=sub1.DueDate AND sub1.Dept=YourTable.Dept) AS ClosingStock
  , NZ((SELECT SUM(Stock) FROM YourTable WHERE YourTable.DueDate<sub1.DueDate AND sub1.Dept=YourTable.Dept),0)*1 AS OpeningStock
FROM sub1;

 

[SiGill]

Here's how you achieve what you want with my table structure:

YourTable
Dept, DueDate, Stock
111, 23/3/21, 5945
111, 24/3/21, 3000
111, 24/3/21, -616
111, 25/3/21, -735
111, 26/3/21, -697
111, 27/3/21, 2728
111, 27/3/21, -525

First you make a subquery to get all the unique date/department combinations:

SELECT YourTable.Dept, YourTable.DueDate
FROM YourTable
GROUP BY YourTable.Dept, YourTable.DueDate;

Name that 'sub1'. Then you can use a correlated subquery to get the Opening and Closing Stock for each Dept/Date by simply summing the Stock value with this query:

SELECT sub1.Dept, sub1.DueDate
  , (SELECT SUM(Stock) FROM YourTable WHERE YourTable.DueDate<=sub1.DueDate AND sub1.Dept=YourTable.Dept) AS ClosingStock
  , NZ((SELECT SUM(Stock) FROM YourTable WHERE YourTable.DueDate<sub1.DueDate AND sub1.Dept=YourTable.Dept),0)*1 AS OpeningStock
FROM sub1;

Here's how you achieve what you want with my table structure:

YourTable
Dept, DueDate, Stock
111, 23/3/21, 5945
111, 24/3/21, 3000
111, 24/3/21, -616
111, 25/3/21, -735
111, 26/3/21, -697
111, 27/3/21, 2728
111, 27/3/21, -525

First you make a subquery to get all the unique date/department combinations:

SELECT YourTable.Dept, YourTable.DueDate
FROM YourTable
GROUP BY YourTable.Dept, YourTable.DueDate;

Name that 'sub1'. Then you can use a correlated subquery to get the Opening and Closing Stock for each Dept/Date by simply summing the Stock value with this query:

SELECT sub1.Dept, sub1.DueDate
  , (SELECT SUM(Stock) FROM YourTable WHERE YourTable.DueDate<=sub1.DueDate AND sub1.Dept=YourTable.Dept) AS ClosingStock
  , NZ((SELECT SUM(Stock) FROM YourTable WHERE YourTable.DueDate<sub1.DueDate AND sub1.Dept=YourTable.Dept),0)*1 AS OpeningStock
FROM sub1;

Thanks Plog. That makes sense. I'll give that a go.