Running Sum & Minus.

[cap.zadi]

Hi every one



I need help to calculate running sum and minus between values as the DB is enclosed.

a. The running sum should be of "Duration" from TablRHours1 based on each "RHID" key of TablRHours
b. The difference should be between 1st value and 2nd value of "Temp" from TableRHours1 and divided by "Duration(hours)" in minutes and so on, to fill the data in the field "TempPerMin".

Any earliest help would be appreciated.


Regards

Zee

[June7]

Bing: Access running sum
Review https://support.microsoft.com/en-us/help/290136/how-to-create-a-running-totals-query-in-microsoft-access

Be aware, query with domain aggregate function can perform very slowly with large dataset.

[cap.zadi]

Dear June

Thanks for your feedback.

Its hard to get the idea for my Demo DB. It would be appreciated, if you can help out by coding in given demo DB as per requirements.

Thanks a lot.

[June7]

SELECT TableRHours1.RHID, TableRHours.Descp, TableRHours1.Temp, Val(DSum("Temp","TableRHours1","RHID=" & [TableRHours1].[RHID] & " AND ID<=" & [ID])) AS TempPerMin
FROM TableRHours INNER JOIN TableRHours1 ON TableRHours.RHID = TableRHours1.RHID;

[cap.zadi]

Hi

Thanks for the efforts and it work fantastic grouped to RHID.
but it was required for Duration not for temp so i changed as below.

RS: Val(DSum("Duration","TableRHours1","RHID=" & [TableRHours1].[RHID] & " AND ID<=" & [ID]))

Can you help out for the question "b" as below
The difference should be between 1st value and 2nd value of "Temp" from TableRHours1 and divided by "Duration(hours)" in minutes and so on, to fill the data in the field "TempPerMin".

[June7]

What are 1st value and 2nd value? Perhaps http://allenbrowne.com/subquery-01.html#AnotherRecord

[cap.zadi]

Hi

The values given in the field of "Temp" where every next value must be minus from the previous one and divided by the "Duration" field to fill in the "TempPerMin" field.

For example:

Temp	Duration (Hours)	TempPerMin
100	0.1	(100-0)/.10
250	4.0	(250-100)/2.0
400	6.0	(400-250)/6.0

[June7]

Per Allen's tutorial, consider:

SELECT TableRHours.RHID, TableRHours.Descp, TableRHours.Notes, TableRHours1.ID, TableRHours1.Duration, TableRHours1.Temp, Val(DSum("Duration","TableRHours1","RHID=" & [TableRHours1].[RHID] & " AND ID<=" & [ID])) AS RH,
([Temp]-Nz((SELECT TOP 1 Dupe.Temp FROM TableRHours1 AS Dupe WHERE Dupe.RHID=TableRHours1.RHID AND Dupe.ID<TableRHours1.ID ORDER BY RHID, Dupe.ID DESC),0))/Duration AS TempPerMin
FROM TableRHours INNER JOIN TableRHours1 ON TableRHours.RHID = TableRHours1.RHID;

Again, nested query like this, as with domain aggregate functions, can perform very slowly with large dataset.


I have been looking at the graph in your report. Build a grouping on the RHID field. Put a textbox named tbxRHID bound to RHID field in the group header section. Also put the chart in the group header section. Set chart RowSource to: SELECT [RH], [Temp] FROM [QueryRHours] WHERE [RHID] = [tbxRHID] ORDER BY [RH];

[cap.zadi]

Hello June

Thanks for help. its working good.