Total week working hours

[grogmi1]

Good morning,

I received some help on here yesterday which enabled me to find out the total hours worked by one of our employees for a day. I am now looking to see if there is any way I can add these daily total hours together to find out the weekly total?

I have a query which has MON Total, Tue Total, Wed Total, Thur Total, Fri Total, Sat Total, Sun Total (Each days total hours for each employee) I need to add these all together to get a week total hours worked but I'm unsure on how to do it.

Thanks in advance

 

[pr2-eugin]

Are these Mon Total, Tue Total etc. Columns or Rows? IF they are columns (you need Normalization), you just create a New column by adding them, like

SELECT MONTotal, TueTotal, WedTotal, ThurTotal, FriTotal, SatTotal, SunTotal,
(MONTotal+ TueTotal+ WedTotal+ ThurTotal+ FriTotal+ SatTotal+ SunTotal) As WeekTotal
FROM yourTable;

If they are Rows then create a TOTALS Query. http://fontstuff.com/access/acctut04.htm

 

[grogmi1]

Thanks for your response.

The Totals are all columns with the total column at the end of my query. I have tried entering your above solution but it returns 'The Syntax of the Subquery in this expression is incorrect'. I'm not the greatest at using Access !!

 

[pr2-eugin]

Can you please show the Query you have used?

 

[grogmi1]

SELECT MONTotal, TueTotal, WedTotal, ThurTotal, FriTotal, SatTotal, SunTotal,
(MONTotal+ TueTotal+ WedTotal+ ThurTotal+ FriTotal+ SatTotal+ SunTotal) As WeekTotal
FROM [SHIFT PATTERNS Query];

 

[pr2-eugin]

Show the "SHIFT PATTERNS Query" Query.

 

[grogmi1]

Not sure if this is how you want to view it but here goes

SELECT [SHIFT PATTERNS].ID, [SHIFT PATTERNS].SHIFT, [SHIFT PATTERNS].[MON START], [SHIFT PATTERNS].[MON FINISH], CDate(([MON FINISH]+IIf([MON START]>[MON FINISH],1,0))-[MON START]) AS [MON TOTAL], [SHIFT PATTERNS].[TUE START], [SHIFT PATTERNS].[TUE FINISH], CDate(([TUE FINISH]+IIf([TUE START]>[TUE FINISH],1,0))-[TUE START]) AS [TUE TOTAL], [SHIFT PATTERNS].[WED START], [SHIFT PATTERNS].[WED FINISH], CDate(([WED FINISH]+IIf([WED START]>[WED FINISH],1,0))-[WED START]) AS [WED TOTAL], [SHIFT PATTERNS].[THUR START], [SHIFT PATTERNS].[THUR FINISH], CDate(([THUR FINISH]+IIf([THUR START]>[THUR FINISH],1,0))-[THUR START]) AS [THUR TOTAL], [SHIFT PATTERNS].[FRI START], [SHIFT PATTERNS].[FRI FINISH], CDate(([FRI FINISH]+IIf([FRI START]>[FRI FINISH],1,0))-[FRI START]) AS [FRI TOTAL], [SHIFT PATTERNS].[SAT START], [SHIFT PATTERNS].[SAT FINISH], CDate(([SAT FINISH]+IIf([SAT START]>[SAT FINISH],1,0))-[SAT START]) AS [SAT TOTAL], [SHIFT PATTERNS].[SUN START], [SHIFT PATTERNS].[SUN FINISH], CDate(([SUN FINISH]+IIf([SUN START]>[SUN FINISH],1,0))-[SUN START]) AS [SUN TOTAL], [SHIFT PATTERNS].[TOTAL HOURS]
FROM [SHIFT PATTERNS];

 

[pr2-eugin]

Try

SELECT [MON TOTAL], [TUE TOTAL], [WED TOTAL], [THUR TOTAL], [FRI TOTAL], [SAT TOTAL], [SUN TOTAL],
([MON TOTAL] + [TUE TOTAL] + [WED TOTAL] + [THUR TOTAL] + [FRI TOTAL] + [SAT TOTAL] + [SUN TOTAL]) As WeekTotal
FROM [SHIFT PATTERNS Query];

You need a proper Naming convention and Normalization to help you through next time.

 

[grogmi1]

Thanks,

The result is the same 'Syntax of the subquery in this exception is incorrect check the subquerys syntax and enclose the sub query in parentheses'

 

[pr2-eugin]

You can use the Query wizard, just insert the Query you have previously, and then insert all the columns, then add these columns. Or upload a Stripped DB.

 

[namliam]

You need a proper Naming convention and Normalization to help you through next time.

Sounds familiar

Strongly suggest you read this:
http://www.access-programmers.co.uk/forums/showthread.php?t=225837&highlight=Name+convention

And see if you can implement something along those lines

 

[grogmi1]

I'm not sure how to upload it onto here. I'll try using the query wizard to see if I can get a total. Thanks for your help