Solved Update query to get the last reorder date

[raziel3]

Good day everyone, can I get some help writing an update query for the REORDERDATE field to give me the following results

+------+---------------------------+------+-----------+-----------+---------+----------+-------------+
| POID |            SKU            | PONO |  PODATE   | SUBTOTAL  |   VAT   | QUANTITY | REORDERDATE |
+------+---------------------------+------+-----------+-----------+---------+----------+-------------+
|  474 | BE 404 OFF WHITE 1 GALLON |  358 | 04-Oct-22 | $1,835.50 | $229.44 |       10 | 10-Oct-22   |
| 1635 | BE 404 OFF WHITE 1 GALLON |  530 | 27-Oct-22 | $1,468.40 | $183.55 |        8 |             |
| 1132 | BE 404 OFF WHITE 1 GALLON |  464 | 19-Oct-22 | $1,835.50 | $229.44 |       10 | 27-Oct-22  |
|  672 | BE 404 OFF WHITE 1 GALLON |  385 | 10-Oct-22 | $1,835.50 | $229.44 |       10 | 19-Oct-22   |
+------+---------------------------+------+-----------+-----------+---------+----------+-------------+

 

[arnelgp]

try:

SELECT POID, SKU, PONO, PODATE, SUBTOTAL, VAT, QUANTITY,
(SELECT TOP 1 PODATE FROM yourTable AS T1 WHERE T1.SKU = yourTable.SKU AND T1.POID > yourTable.POID ORDER BY T1.POID ASC)
AS REORDERDATE FROM yourTable;

 

[plog]

You don't store calculatable data. So, REORDERDATE shouldn't be a field in your table you update, but a field in a query.

Then when you need that value. You reference the query. Looks like you got the SQL for that query

 

[raziel3]

You don't store calculatable data. So, REORDERDATE shouldn't be a field in your table you update, but a field in a query.

Then when you need that value. You reference the query. Looks like you got the SQL for that query

Ok, noted

Thanks @arnelgp

 

[ebs17]

Look at that

SELECT
   T1.POID,
   T1.PODATE,
   MIN(T2.PODATE) AS REORDERDATE
FROM
   TableX AS T1
      LEFT JOIN TableX AS T2
      ON T1.PODATE < T2.PODATE
GROUP BY
   T1.POID,
   T1.PODATE

 

[arnelgp]

to update you can try creating a function in a module:

Public Function fncReorderDate(ByVal SKU As String, ByVal POID As Variant)
    With CurrentDb.CreateQueryDef("", "SELECT TOP 1 PODATE FROM yourTable " & _
                "WHERE SKU = @1 AND VAL(POID) > VAL(@2) ORDER BY POID;")
        .Parameters(0) = SKU
        .Parameters(1) = POID
        With .OpenRecordset
            If Not .EOF Then
                fncReorderDate = .Fields(0)
            Else
                fncReorderDate = Null
            End If
        End With
    End With
            
End Function

your Update Query:

UPDATE yourTable SET yourTable.REORDERDATE = fncReorderDate([SKU],[POID]);

 

[raziel3]

Didn't realize that people were still answering since I marked it as "Solved".

I decide to take @plog advice. So I made a query

SELECT ORDERDETAIL.SKU, ORDERDETAIL.PONO, ORDERDETAIL.PODATE, ORDERDETAIL.BACKORDERAMT, (SELECT TOP 1 PODATE FROM ORDERDETAIL AS T1 WHERE T1.SKU = ORDERDETAIL.SKU AND T1.POID > ORDERDETAIL.POID ORDER BY T1.POID ASC) AS REORDERED
FROM ORDERDETAIL
WHERE (((ORDERDETAIL.BACKORDERAMT)<>0))
ORDER BY ORDERDETAIL.PODATE;

and based the report off that. No need to put the Reorder Date in the main table.