Running total by date (1 Viewer)

[Jonny45wakey]

Hi All

I'm hoping someone with a better brain than mine can help me out please?

I have a query called qryESH which has the following fields:-

[Job_No] - Job Number
[OpStart] - Operations start DTG
[OpStop] - Operations stop DTG
[SumOfActHrs] - Total Hrs between [OpStart] & [OpStop]
[TransDate] - Date of Transaction

What i would like is a query which sums the total [SumOfActHrs] field against all [TransDate] which are on same day.

For example:-

In [TransDate] on 20/04/2023 the query shows one row with total [SumOfActHrs] for each job, within the [TransDate] field i would then set query criteria to [Enter Date] to return something like below.

Here all [SumOfActHrs] have been totalled up for each [Job-No] by the [TransDate] value entered in [Enter Date] criteria of [TransDate] field.

Hope this makes sense?

Any help much appreciated

Thanks

Jonny

 

[ebs17]

SELECT 
   Job_No, 
   TransDate, 
   SUM(SumOfActHrs) AS XSum 
FROM 
   tblData AS T 
GROUP BY 
   Job_No, 
   TransDate

 

[June7]

Do you really want "running total"? In Access, running total query requires subquery. Alternatively, build a report using Sorting & Grouping features and use RunningSum property of textbox.

Or do you just want aggregation by Job_No and TransDate?

 

[Jonny45wakey]

Do you really want "running total"? In Access, running total query requires subquery. Alternatively, build a report using Sorting & Grouping features and use RunningSum property of textbox.

Or do you just want aggregation by Job_No and TransDate?

Exactly June7, aggregation of SumOfActHrs by Job_No and TransDate

Thanks

Jonny

 

[GaP42]

Then as Ebs suggested, based on your query
SELECT tblJobs.Job_No, tblJobs.TransDt, Sum(DateDiff("h",[opstartdt],[openddt])) AS Hr
FROM tblJobs
GROUP BY tblJobs.Job_No, tblJobs.TransDt
ORDER BY tblJobs.TransDt;