April 29, 2009 at 5:36 am
Very new to database configuration - so please bare with me.
I have a table (JKTime) that contains workers hours:
PayrollWeek (Date\Time), Mechanic (Text), Job-ID (Text), Time (Number), TimeType (Text). (note: TimeType can be Reg, OT, Dbl)
I'm looking to have One Query gather the following info for all 40 workers in one shot....
Below are 3 queries I have that get the info I need, which is to total up all the hours in a given PayrollWeek for the noted Mechanic name based on TimeType.
Q1:
SELECT Sum(JKTime.Time) AS RegularHours
FROM JKTime
WHERE (((JKTime.Mechanic)='Craig Bisesti' AND JKTime.TimeType='REG' AND JKTime.PayrollWeek=[Please Enter Pay Week]))
GROUP BY JKTime.Mechanic;
Q2:
SELECT Sum(JKTime.Time) AS OTHours
FROM JKTime
WHERE (((JKTime.Mechanic)='Craig Bisesti' AND JKTime.TimeType='OT' AND JKTime.PayrollWeek=[Please Enter Pay Week]))
GROUP BY JKTime.Mechanic;
Q3:
SELECT Sum(JKTime.Time) AS DBLHours
FROM JKTime
WHERE (((JKTime.Mechanic)='Craig Bisesti' AND JKTime.TimeType='DBL' AND JKTime.PayrollWeek=[Please Enter Pay Week]))
GROUP BY JKTime.Mechanic;
April 29, 2009 at 10:03 am
I've come up with a solution. There is probably a better, more simple one, but Ithinkt his will work. I changed your queries so they would select all employees:
Q1:
SELECT Sum(JKTime.Time) AS RegularHours, JKTime.Mechanic
FROM JKTime
WHERE (((JKTime.TimeType)='REG') AND ((JKTime.PayrollWeek)=[Please Enter Pay Week]))
GROUP BY JKTime.Mechanic;
Q2:
SELECT Sum(JKTime.Time) AS OTHours, JKTime.Mechanic
FROM JKTime
WHERE (((JKTime.TimeType)='OT') AND ((JKTime.PayrollWeek)=[Please Enter Pay Week]))
GROUP BY JKTime.Mechanic;
Q3:
SELECT Sum(JKTime.Time) AS DBLHours, JKTime.Mechanic
FROM JKTime
WHERE (((JKTime.TimeType)='DBL') AND ((JKTime.PayrollWeek)=[Please Enter Pay Week]))
GROUP BY JKTime.Mechanic;
Then I added a fourth query based on your 3 queries:
Q4:
SELECT Query1.Mechanic, Query1.RegularHours, Query2.OTHours, Query3.DBLHours
FROM (Query1 LEFT JOIN Query2 ON Query1.Mechanic = Query2.Mechanic) LEFT JOIN Query3 ON Query1.Mechanic = Query3.Mechanic;
This query assumes that every employee will have regular hours if they have OT or Dbl hours; there at least has to be an entry to the table showing 0 regular hours if there is OT or Dbl.
Play around with the Joins. Hope this helps.
April 29, 2009 at 10:21 am
Thanks very much - I will play with it.
April 29, 2009 at 10:59 am
I think this is what you are looking for:
declare @PayWeek int;
set @PayWeek = 1; -- just a value for illustration purposes
select
jkt.Mechanic,
sum(case when jkt.TimeType = 'REG' then jkt.Time else 0 end) as RegularHours,
sum(case when jkt.TimeType = 'OT' then jkt.Time else 0 end) as OTHours,
sum(case when jkt.TimeType = 'DBL' then jkt.Time else 0 end) as DBLHours
from
JKTime jkt
where
jkt.PayrollWeek = @PayWeek
group by
jkt.Mechanic;
April 29, 2009 at 11:49 am
I would love getting this to work - but I get several error messages within MS Access 2k3 when I try it.
1st - it doesn't recongnize Declare as a valid sql statement.
2nd - it doesn't like when either.
Do you have modifications so that it would work with access 2k3?
April 29, 2009 at 11:55 am
Sorry, I didn't realize this was Access when I posted the query. Don't know enough about Access to help at this point. Let me do some research and see what I can find.
April 29, 2009 at 12:19 pm
Try something like this:
SELECT DISTINCTROW JKTime.PayrollWeek, JKTime.Mechanic, JKTime.JobID, JKTime.TimeType, Sum(JKTime.Time) AS [Sum Of Time]
FROM JKTime
GROUP BY JKTime.PayrollWeek, JKTime.Mechanic, JKTime.JobID, JKTime.TimeType
HAVING (((JKTime.PayrollWeek)=#1/1/2009#));
You will need to change the date for PayrollWeek
April 29, 2009 at 12:58 pm
That did it - thanks so much for your help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply