MS Access subtract previous record from current record

[ramirezx@ddmfg.com]

Hi,

I'm trying to run a query and used this code, how ever the first two show 0. Not sure what's causing it. I hope someone can help

-----------------
SELECT tblData.Date,
tblData.[Port MDE End],
[Port MDE End]-


Nz(
(Select Top 1
[Port MDE End]
From
tblData As T
Where
(T.Date <= tblData.Date And T.[Port MDE End] < tblData.[Port MDE End] )
Or
(T.Date < tblData.Date And T.[Port MDE End] <= tblData.[Port MDE End] )
Order By
T.Date Desc,
T.[Port MDE End] Desc),
[Port MDE End]) AS [Total Hours]

FROM
tblData;
-----------------
this is what I get:

Date	Port MDE End	Total Hours
1/6/2023	896	0
1/7/2023	2103	0
1/7/2023	3220	1117
1/8/2023	4218	998
1/8/2023	5435	1217
1/9/2023	6472	1037

[Welshgasman]

That output does not match the SQL?

[CJ_London]

Based on the data provided in the first two columns,



the query you provided works. So the implication is there is data you are not showing


So show your source data

[ramirezx@ddmfg.com]

Hi,

I recopied the code from my query:

-----------------------------------------------------------------------------------------------------------

SELECT tblData.Date, tblData.[Port MDE End], [Port MDE End]-Nz((Select Top 1
[Port MDE End]
From
tblData As T
Where
(T.Date <= tblData.Date And T.[Port MDE End] < tblData.[Port MDE End] )
Or
(T.Date < tblData.Date And T.[Port MDE End] <= tblData.[Port MDE End] )
Order By
T.Date Desc,
T.[Port MDE End] Desc),[Port MDE End]) AS [Total Hours]
FROM tblData;

[ramirezx@ddmfg.com]

Hi,

Here is the table where the data is stored:

Date	Port MDE End
1/6/2023	896
1/7/2023	2103
1/7/2023	3220
1/8/2023	4218
1/8/2023	5435
1/9/2023	6472

Thanks!

[ramirezx@ddmfg.com]

Hi everyone,

Thanks for you help, I found out what the problem was. the Port MDE End field data type was Short Text, when I changed it to Number is working now!

Thanks again everyone for you help!