Aggregating row totals

[bwlytkr]

Does anyone know if there is a formula I can use to aggregate the hours in different rows and have it populate the total when running the query in Access? I have multiple rows with the same job # and I need to the total of those hours to populate for the job in each of those rows when I run the query since the table it is matched to only has the job# one time. Below is an example:

Query Result Job # Total Hours Table 1 Job # Table 2 Job # Hours
254 9 254 254 2
254 2
254 5
I'm not sure if this can even be done but, I thought I would ask anyway. Thanks in advance for your help. John

 

[JonXL]

Put this in the SQL view of your query.

SELECT [Job #], SUM([Hours]) AS [Total Hours]
FROM [Table 1]
GROUP BY [Job #]

 

[bwlytkr]

Thanks So much!! I'll give it a try and let you know

 

[bwlytkr]

Would I put this in just like you have it? Each line on separate lines below the box with the check in it. Not to familiar with putting formulas in access obviously. I tried doing it this way and it didn't work. It told me I had to put opening and closing ( ) for each line.

 

[alansidman]

Open your query, in the upper left corner, select SQL. Paste the SQL statement. Change the selection from SQL to DataSheet.

 

[bwlytkr]

thanks again for the help

 

[bwlytkr]

Sorry to keep bothering you with this but, do I just paste it to the end of the existing query or paste next to the statement representing the column I want to aggregate. This is my existing query and this is the statement representing my column, [PWO COMPLETIONS REPORT].[Jam Demand Time]. Thanks


SELECT RAW_COMPLETIONS.District, RAW_COMPLETIONS.subDistrict, RAW_COMPLETIONS.Code, RAW_COMPLETIONS.[Coverage Area], RAW_COMPLETIONS.[Member Name], RAW_COMPLETIONS.[Ticket Number], RAW_COMPLETIONS.[Ticket Type], RAW_COMPLETIONS.[Step Job Type], RAW_COMPLETIONS.[Step Order], RAW_COMPLETIONS.[Step Job Identifier], IIf(Left([RAW_COMPLETIONS]![Step Job Identifier],3)="PWO","PWO",IIf(Right([RAW_COMPLETIONS]![Step Job Identifier],1)="n" Or Right([RAW_COMPLETIONS]![Step Job Identifier],1)="b" Or Right([RAW_COMPLETIONS]![Step Job Identifier],1)="a" Or Right([RAW_COMPLETIONS]![Step Job Identifier],1)="c","EWO","FOK")) AS JOB_TYPE, RAW_COMPLETIONS.[Step Reference], RAW_COMPLETIONS.[Step TaskID], RAW_COMPLETIONS.ServingArea, RAW_COMPLETIONS.[OSPCM Job Status], RAW_COMPLETIONS.[OSPCM Step Status], RAW_COMPLETIONS.[OSPCM Step Estimated Hrs], [PWO COMPLETIONS REPORT].[Jam Demand Time], RAW_COMPLETIONS.[OSPCM Step Count], RAW_COMPLETIONS.[Ticket House Number], RAW_COMPLETIONS.[Ticket Street Name], RAW_COMPLETIONS.[Created On Dt], RAW_COMPLETIONS.[Created By Member], RAW_COMPLETIONS.[Pole Owner], RAW_COMPLETIONS.ticketRemarks, RAW_COMPLETIONS.stepRemarks, [ticketRemarks] & " // " & [stepremarks] AS allRemarks, RAW_COMPLETIONS.[Step Member Code], RAW_COMPLETIONS.[Next To Go Member], RAW_COMPLETIONS.[Work Requested Dt], RAW_COMPLETIONS.[Next To Go Start Dt], RAW_COMPLETIONS.[Completed Dt], Format([Completed Dt],"mmmm") AS [Completed Month], RAW_COMPLETIONS.[NTG Interval], IIf([NTG Interval]<=30,"<30 Days",IIf([NTG Interval]>30 And [NTG Interval]<=60,"31-60 Days",IIf([NTG Interval]>60 And [NTG Interval]<=240,"61-240 Days",IIf([NTG Interval]>240,">240 Days")))) AS [NTG AGE BUCKET], RAW_COMPLETIONS.Interval, RAW_COMPLETIONS.[Priority Code], RAW_COMPLETIONS.Status, RAW_COMPLETIONS.County, RAW_COMPLETIONS.Place, RAW_COMPLETIONS.[Updated On Dt], RAW_COMPLETIONS.StepLatitude, RAW_COMPLETIONS.StepLongitude, RAW_COMPLETIONS.StepLocation, RAW_COMPLETIONS.ticketLatitude, RAW_COMPLETIONS.ticketLongitude, RAW_COMPLETIONS.ContactName, RAW_COMPLETIONS.ContactPhone, WLS07.[WC Name], WLS07.[Construction Director], WLS07.[Const Area Manager], WLS07.[Const Placing Network Manager]
FROM (RAW_COMPLETIONS LEFT JOIN WLS07 ON RAW_COMPLETIONS.ServingArea = WLS07.CLLI) LEFT JOIN [PWO COMPLETIONS REPORT] ON RAW_COMPLETIONS.[Step Job Identifier] = [PWO COMPLETIONS REPORT].[Job Nm];

 

[alansidman]

You will probably want this to be a separate query. Replace the existing SQL statement and then save as a new query.

 

[bwlytkr]

ok I'll give it a try

 

[bwlytkr]

Well I got the new query to work by itself by removing the old query, which is great by the way but, still unsure how to make it work in conjunction with the existing query so I can view it on the entire report. Just not sure how to add it to the existing query to make it all work together. any thoughts??