running total using sum does not work

[zezo2021]

Hello
why the expression on the query not work
Thanks in Advance

 

[plog]

Define 'not work'. It's runs for me and produces a total exactly as what I expect it to based on the logic in it.

Instead of an explanation, I suggest you give me the actual data you expect that query to produce.

 

[zezo2021]

Thanks for reply
I need to running subtract not Total
the DB contains Acutial Data
1571
1572
1573

the first row should be 0
the second Should be 1
The third should be one

it's not accumulated total

I need to subtract the Total

I can do accumulated total using report running sum = over all
I don't need running sum
----------
I need running minus

 

[Pat Hartman]

Then subtract instead of add.

 

[zezo2021]

Then subtract instead of add.

the expression
Minus: DSum("NewHours","TableHours"," [ID]<=" & [ID])-[NewHours]
what should I change

 

[mike60smart]

Thanks for reply
I need to running subtract not Total
the DB contains Acutial Data
1571
1572
1573

the first row should be 0
the second Should be 1
The third should be one

it's not accumulated total

I need to subtract the Total

I can do accumulated total using report running sum = over all
I don't need running sum
----------
I need running minus

You need a field named StartHours then in the query you would use [NewHours]-[StartHours]

 

[zezo2021]

I need to subtract the first cell from the second and the second from the third etc

 

[bob fitz]

I need to subtract the first cell from the second and the second from the third etc

Can you give us some detail of what your data is about and the need of these calculations. Perhaps a screen shot of your relationships as well.
To me, your reference to cells smacks of at db designed like a spreadsheet.

 

[plog]

Again, an explanation is not the best way to communicate this.

Show me exactly all the data you want the query to produce based on what is in the table you uploaded. If needed take a screenshot of the existing query and put the correct expected values where they should go.

 

[zezo2021]

مرة أخرى، التوضيح ليس هو أفضل طريقة للتواصل.

أظهر لي بالضبط كل البيانات التي تريد أن ينتجها الاستعلام بناءً على ما هو موجود في الجدول الذي قمت بتحميله. إذا لزم الأمر، التقط لقطة شاشة للاستعلام الحالي وضع القيم المتوقعة الصحيحة حيث يجب وضعها.

 

[plog]

You need to use the database you uploaded to demonstrate. The database you uploaded has no ID=21. So either upload that database or provide a screenshot using the existing one.

 

[zezo2021]

You need to use the database you uploaded to demonstrate. The database you uploaded has no ID=21. So either upload that database or provide a screenshot using the existing one.

 

[plog]

This is getting painful. The screenshot is of your table.

I need to know what you expect your query to return.

 

[June7]

You want Subtract to be the result? You want the difference between consecutive records? This not really a "running" value.
This is not simple in Access. Review http://allenbrowne.com/subquery-01.html#AnotherRecord

Consider:
SELECT TableHours.ID, TableHours.NewHours, TableHours.DateAdd,
[NewHours] - Nz(DMax("NewHours","TableHours","[ID]<" & [ID]), NewHours) AS Minus
FROM TableHours;

SELECT TableHours.ID, TableHours.NewHours, TableHours.DateAdd,
[NewHours]-Nz((SELECT TOP 1 NewHours FROM TableHours AS Dupe WHERE Dupe.ID<TableHours.ID ORDER BY ID DESC),NewHours) AS Minus
FROM TableHours;

ID	NewHours	DateAdd	Minus
1	1571	12/31/2024	0
14	1572	1/2/2024	1
20	1573	1/3/2025	1
21	1580	1/3/2025	7

The second version might be a little faster but both will perform slowly with large dataset.

Now in a report you could do a RunningSum on the Minus field.

 

[zezo2021]

You want Subtract to be the result? You want the difference between consecutive records? This not really a "running" value.
This is not simple in Access. Review http://allenbrowne.com/subquery-01.html#AnotherRecord

Consider:
SELECT TableHours.ID, TableHours.NewHours, TableHours.DateAdd,
[NewHours] - Nz(DMax("NewHours","TableHours","[ID]<" & [ID]), NewHours) AS Minus
FROM TableHours;

SELECT TableHours.ID, TableHours.NewHours, TableHours.DateAdd,
[NewHours]-Nz((SELECT TOP 1 NewHours FROM TableHours AS Dupe WHERE Dupe.ID<TableHours.ID ORDER BY ID DESC),NewHours) AS Minus
FROM TableHours;

ID	NewHours	DateAdd	Minus
1	1571	12/31/2024	0
14	1572	1/2/2024	1
20	1573	1/3/2025	1
21	1580	1/3/2025	7

The second version might be a little faster but both will perform slowly with large dataset.

Now in a report you could do a RunningSum on the Minus field.

Thank you so much for your efforts
Great
Done
I test the first sentence of SQL and work correctly

 

[mike60smart]

Is this what you are after?