432,369 Members | 966 Online + Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,369 IT Pros & Developers. It's quick & easy.

# Daily Totals ( Like a Time Sheet) Sumerized by Date.

 P: 9 Greetings, I am working on a project and need help with a querie. Project: A Calculated Time Sheet for Job costing Combining same values and sub totals Ie. Name | Date | Hours (hund) | Job | Phase | Elivation | Brad 1/1/07 3.5 1 1 1 Brad 1/1/07 2.0 3 1 2 Brad 1/2/07 8.0 1 2 1 I would like to returne this Name | Date | Hours (hund) | Brad 1/1/07 5.5 Brad 1/2/07 8.0 Thanks for your help in advance. _ Brad May 7 '07 #1
8 Replies

 Expert 100+ P: 603 Greetings, I am working on a project and need help with a querie. Project: A Calculated Time Sheet for Job costing Combining same values and sub totals Ie. Name | Date | Hours (hund) | Job | Phase | Elivation | Brad 1/1/07 3.5 1 1 1 Brad 1/1/07 2.0 3 1 2 Brad 1/2/07 8.0 1 2 1 I would like to returne this Name | Date | Hours (hund) | Brad 1/1/07 5.5 Brad 1/2/07 8.0 Thanks for your help in advance. _ Brad well, let' see. You want to select Brad and the day and total his hours Select [Name], [Date], sum([Hours] as TotalHours From Yourtable Group By [Name], [Date] May 7 '07 #2

 P: 9 Awesome Job. So simple but then again that why your guys are the experts. Now Part 2 if you can help is a little more math centric. I have a total Name | Date | Total | Brad 1/1/07 11 I want to do a calculation so here it is in english however I would like the SQL if total is less than or equil to 8 Multiply by Rate (Rate is in a Table called Employees and has a relation) and show in a field called Dollars. ELSE Total subtract 8 and multiply remainder by (Rate * 1.5) + (8 * Rate) and place into field called Dollars. Ie. (Rate = 10.0) Name | Date | Total | Dollars | Brad 1/1/07 11 \$125 Math Figure OverTimeHours 11-8=3 Figure Dollars 3*10 =30 Figure Dollars with OverTime Calc 30*1.5=45 \$45.00 in Overtime Figure RegularDollars 8*10=80 \$80.00 in Regular Figure Total 45+80=125 \$125 in Total Dollars Thanks again for your expertice. Brad May 8 '07 #3

 Expert 100+ P: 603 Awesome Job. So simple but then again that why your guys are the experts. Now Part 2 if you can help is a little more math centric. I have a total Name | Date | Total | Brad 1/1/07 11 I want to do a calculation so here it is in english however I would like the SQL if total is less than or equil to 8 Multiply by Rate (Rate is in a Table called Employees and has a relation) and show in a field called Dollars. ELSE Total subtract 8 and multiply remainder by (Rate * 1.5) + (8 * Rate) and place into field called Dollars. Ie. (Rate = 10.0) Name | Date | Total | Dollars | Brad 1/1/07 11 \$125 Math Figure OverTimeHours 11-8=3 Figure Dollars 3*10 =30 Figure Dollars with OverTime Calc 30*1.5=45 \$45.00 in Overtime Figure RegularDollars 8*10=80 \$80.00 in Regular Figure Total 45+80=125 \$125 in Total Dollars Thanks again for your expertice. Brad Word problems...hated them in school...never thought I'd be interpreting them for a living. I might have a partnthesis or two out of order...but this is to help you learn right? J Expand|Select|Wrap|Line Numbers Select [Name], [Date], sum([Hours] as TotalHours, iif(sum([Hours])<=8,sum([Hours])*[Employees].[Rate],sum([Hours])-8)*([employees].[rate]*1.5 + ([employees].[rate] * 8)) as Dollars From Yourtable  INNER JOIN YourTable ON yourtable.ID = employees.ID Group By [Name], [Date];   May 8 '07 #4

 P: 9 Ok we are almost there however there is somethign wrong with the Math. Here is My Control 11 hours rate is 10.00 Total should be 125. With the code I am getting 240. Can you please help me troubleshoot this code. Thanks Again! Here is my Code Expand|Select|Wrap|Line Numbers SELECT [Time Sheets].Employee, [Time Sheets].[Date Of Hours], Sum([Time Sheets].[Hours (Hund)]) AS TotalHours,Employees.Rate,iif(TotalHours <=8, TotalHours *  Employees.Rate, TotalHours -8) * (Employees.Rate * 8 ) as Dollars FROM Employees INNER JOIN [Time Sheets] ON Employees.ID = [Time Sheets].Employee GROUP BY [Time Sheets].Employee, [Time Sheets].[Date Of Hours], Employees.Rate;   May 8 '07 #5

 Expert 100+ P: 603 Ok we are almost there however there is somethign wrong with the Math. Here is My Control 11 hours rate is 10.00 Total should be 125. With the code I am getting 240. Can you please help me troubleshoot this code. Thanks Again! Here is my Code Expand|Select|Wrap|Line Numbers SELECT [Time Sheets].Employee, [Time Sheets].[Date Of Hours], Sum([Time Sheets].[Hours (Hund)]) AS TotalHours,Employees.Rate,iif(TotalHours <=8, TotalHours *  Employees.Rate, TotalHours -8) * (Employees.Rate * 8 ) as Dollars FROM Employees INNER JOIN [Time Sheets] ON Employees.ID = [Time Sheets].Employee GROUP BY [Time Sheets].Employee, [Time Sheets].[Date Of Hours], Employees.Rate;   you were missing the overtime calc Expand|Select|Wrap|Line Numbers SELECT  [Time Sheets].Employee,  [Time Sheets].[Date Of Hours],  Sum([Time Sheets].[Hours (Hund)]) AS TotalHours, Employees.Rate,iif(TotalHours <=8, TotalHours *  Employees.Rate, ((TotalHours - 8) * 1.5) + (Employees.Rate * 8 ) as Dollars FROM Employees INNER JOIN [Time Sheets] ON Employees.ID = [Time Sheets].Employee GROUP BY [Time Sheets].Employee, [Time Sheets].[Date Of Hours], Employees.Rate;   May 8 '07 #6

 P: 9 Awesome Job I added the Rate in one spot and it works perfectly. Great Job. Can I give you Kudos points on this form? Expand|Select|Wrap|Line Numbers SELECT  [Time Sheets].Employee,  [Time Sheets].[Date Of Hours],  Sum([Time Sheets].[Hours (Hund)]) AS TotalHours, Employees.Rate,iif(TotalHours <=8, TotalHours *  Employees.Rate, ((TotalHours - 8) * 1.5) * (Employees.Rate)+ (Employees.Rate * 8 ) )as Dollars FROM Employees INNER JOIN [Time Sheets] ON Employees.ID = [Time Sheets].Employee GROUP BY [Time Sheets].Employee, [Time Sheets].[Date Of Hours], Employees.Rate; May 8 '07 #7

 Expert 100+ P: 603 Awesome Job I added the Rate in one spot and it works perfectly. Great Job. Can I give you Kudos points on this form? Expand|Select|Wrap|Line Numbers SELECT  [Time Sheets].Employee,  [Time Sheets].[Date Of Hours],  Sum([Time Sheets].[Hours (Hund)]) AS TotalHours, Employees.Rate,iif(TotalHours <=8, TotalHours *  Employees.Rate, ((TotalHours - 8) * 1.5) * (Employees.Rate)+ (Employees.Rate * 8 ) )as Dollars FROM Employees INNER JOIN [Time Sheets] ON Employees.ID = [Time Sheets].Employee GROUP BY [Time Sheets].Employee, [Time Sheets].[Date Of Hours], Employees.Rate; Happy to help...Thanks for the kudos :) J May 8 '07 #8

 Expert Mod 15k+ P: 31,475 Happy to help...Thanks for the kudos :) J We notice these things :) May 11 '07 #9 