Assistance needed in summing up the total and group by a field (1 Viewer)

[Voyager]

Hi Team,
I needed assistance in fixing the query in the attachment. I needed the grandtotal column to show the aggregate of time spent on that task.
e.g. first two rows in the grandtotal field should show 5:00 ( Total hrs. spent on the same task) and last two to show 2:00 hrs. I have done some mistake in my query but could not figure out what it should be?

 

[plog]

Fix your tables, use the appropriate data type for your fields. You can't SUM text. You also shouldn't store calculated values (which I think both Total and GrandTotal are).


Furlther, you are using reserved words as field names. Don't use any words on this list as names:

https://support.office.com/en-us/ar...-symbols-e33eb3a9-8baa-4335-9f57-da237c63eabe

 

[mike60smart]

Hi

As plog has mentioned you need to fix the table structure, field names and field format.

In the attached example:-

1. the names of the tables have been changed
2. Referential Integrity has been set between the two related tables
3. the format of fieldnames JobStart & JobEnd have now been set as ShortTime and also with an Input Mask of ShortTime
4. The Form that opens is a Main Form based on tblJobs
5. with a Subform based on tblJobDetails which is the Many side of the relationship.
6. You can now select a Job and see all of the related Job Details.
7. The calculation for the Time Taken for the Job is carried out in the Record Source of the Subform as follows:-

SELECT tblJobDetails.JobDetailsID, tblJobDetails.JobID, tblJobDetails.JobStart, tblJobDetails.JobEnd, DateDiff("n",[JobStart],[JobEnd])\60 & "." & Format(DateDiff("n",[JobStart],[JobEnd]) Mod 60,"00") AS Expr1 FROM tblJobDetails;

View attachment Test2.zip

 

[Voyager]

Thank you Plog,
mike60smart you have done a splendid Job your solution will reduce plenty of my burden.