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
8 1833
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]
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
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 -
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];
-
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 - 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;
-
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 - 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 -
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;
-
Awesome Job I added the Rate in one spot and it works perfectly. Great Job. Can I give you Kudos points on this form? - 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;
Awesome Job I added the Rate in one spot and it works perfectly. Great Job. Can I give you Kudos points on this form? - 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
NeoPa 32,556
Expert Mod 16PB
Happy to help...Thanks for the kudos :)
J
We notice these things :)
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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 ...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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
|
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...
| |