473,326 Members | 2,127 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

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 1833
JConsulting
603 Expert 512MB
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
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
JConsulting
603 Expert 512MB
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
  1. 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
  2. From Yourtable  INNER JOIN YourTable ON yourtable.ID = employees.ID
  3. Group By [Name], [Date];
  4.  
May 8 '07 #4
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
  1. 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
  2. FROM Employees INNER JOIN [Time Sheets] ON Employees.ID = [Time Sheets].Employee
  3. GROUP BY [Time Sheets].Employee, [Time Sheets].[Date Of Hours], Employees.Rate;
  4.  
May 8 '07 #5
JConsulting
603 Expert 512MB
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
  1. 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
  2. FROM Employees INNER JOIN [Time Sheets] ON Employees.ID = [Time Sheets].Employee
  3. GROUP BY [Time Sheets].Employee, [Time Sheets].[Date Of Hours], Employees.Rate;
  4.  

you were missing the overtime calc

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. [Time Sheets].Employee, 
  3. [Time Sheets].[Date Of Hours], 
  4. Sum([Time Sheets].[Hours (Hund)]) AS TotalHours,
  5. Employees.Rate,iif(TotalHours <=8, TotalHours *  Employees.Rate, ((TotalHours - 8) * 1.5) + (Employees.Rate * 8 ) as Dollars
  6. FROM Employees INNER JOIN [Time Sheets] ON Employees.ID = [Time Sheets].Employee
  7. GROUP BY [Time Sheets].Employee, [Time Sheets].[Date Of Hours], Employees.Rate;
  8.  
May 8 '07 #6
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
  1. SELECT 
  2. [Time Sheets].Employee, 
  3. [Time Sheets].[Date Of Hours], 
  4. Sum([Time Sheets].[Hours (Hund)]) AS TotalHours,
  5. Employees.Rate,iif(TotalHours <=8, TotalHours *  Employees.Rate, ((TotalHours - 8) * 1.5) * (Employees.Rate)+ (Employees.Rate * 8 ) )as Dollars
  6. FROM Employees INNER JOIN [Time Sheets] ON Employees.ID = [Time Sheets].Employee
  7. GROUP BY [Time Sheets].Employee, [Time Sheets].[Date Of Hours], Employees.Rate;
May 8 '07 #7
JConsulting
603 Expert 512MB
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
  1. SELECT 
  2. [Time Sheets].Employee, 
  3. [Time Sheets].[Date Of Hours], 
  4. Sum([Time Sheets].[Hours (Hund)]) AS TotalHours,
  5. Employees.Rate,iif(TotalHours <=8, TotalHours *  Employees.Rate, ((TotalHours - 8) * 1.5) * (Employees.Rate)+ (Employees.Rate * 8 ) )as Dollars
  6. FROM Employees INNER JOIN [Time Sheets] ON Employees.ID = [Time Sheets].Employee
  7. GROUP BY [Time Sheets].Employee, [Time Sheets].[Date Of Hours], Employees.Rate;
Happy to help...Thanks for the kudos :)
J
May 8 '07 #8
NeoPa
32,556 Expert Mod 16PB
Happy to help...Thanks for the kudos :)
J
We notice these things :)
May 11 '07 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: lou | last post by:
When sending a date value to a web service, does .NET know what time zone that date is sent from? The reason I ask is because I have client software that sends data to our web service and when a...
7
by: Edward Mitchell | last post by:
I have a number of DateTimePicker controls, some set to dates, some set to a format of Time. The controls are all embedded in dialogs. I created the controls by dragging the DateTime picker from...
2
by: john | last post by:
From an Excel-import I have an access table with a datetime field of wich both date and time are entered. I've set the field's notation properties to Short Date. When I view the table in tableview...
1
by: jennyp29 | last post by:
I am trying to design a Time Sheet in Excel. With a Start Time, an End Time, and a time standown (Lunch Break) But cannot seem to get the formula correct. I realise this is probably one of the...
0
by: zenonm15 | last post by:
I need assistance in creating a formula for "ND" (Night Differential) and "OT" (Overtime). ND is between 5:15 a.m. & 6:00 a.m. and 18:00 & 21:00. OT is any anything after 8 hours. DAY DATE ...
10
jrtox
by: jrtox | last post by:
Hello guys, I made a simple Computerized Time Sheet in our Office and its not 100% working because i used the system clock. the user can edit/change the system time and date if he/she knows that...
6
by: gozdeata | last post by:
hey there, i m trying to get only time information of a date type variable. i got a column in type of char, it keeps time info in hh:mm format - 5 characters when i tried to convert it to date...
0
by: Aandrew | last post by:
I need to be able to work out the UTC time given the local system time for any date in the year. I live in the UK and at the moment the time is GMT+1:00, however when we return to DST it will be...
0
Jerry Maiapu
by: Jerry Maiapu | last post by:
Can someone give me a sample time sheet databse. I am doing a project using ms access to create a database to records clock in and clock out times for emplyees and that includes over time as well...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.