I got it to work, thanks so much, I even added more fields.
Here are the first 2 days of the week of my union query and it has all the
info I need to work with. I think everyone in this newsgroup who looks for
help tries to minimize what is really needed or doesn't realize what
information must be given to give a correct response, like me.
I need to do a certified payroll report for one of the big jobs for wages by
month by employee, by workers compensation code, listing regular hours,
overtime hours, total hours, regular rate of pay, regular hourly wages,
overtime hours wages, total wages.
SELECT employeename, subofjob, earningclass, glacct, jobdescription,
regrate, otrate,monreghrs As HrsWrkd, [WeDate]- 6 As WorkDate , "Reg" as
TimeType
FROM TblPayrollAllData
WHERE monreghrs>0
UNION
SELECT employeename, subofjob, earningclass, glacct, jobdescription,
regrate, otrate,monothrs, [WeDate]- 6, "OT"
FROM TblPayrollAllData
WHERE monothrs >0
UNION
SELECT employeename, subofjob, earningclass, glacct, jobdescription,
regrate, otrate,tuereghrs, [WeDate]- 5, "Reg"
FROM TblPayrollAllData
WHERE tuereghrs>0
UNION
SELECT employeename, subofjob, earningclass, glacct, jobdescription,
regrate, otrate,tueothrs, [WeDate]- 5, "OT"
FROM TblPayrollAllData
WHERE tueothrs >0
Thank you, you are the best, as always,
Regards,
Annelie
You have helped me so many times already
Duane Hookom said:
Your original request was "I need to create a report of hours worked per
month." Just add the SSNo field into each section of the UNION query. This
will allow you to calculate the number of hours per month by employee.
--
Duane Hookom
MS Access MVP
example.
can
an
employee have more than one record per week? You could either add
all
the
fields or just the relevant fields. If you just add the unique
fields,
you
can join the union query to tblPayrollAllData to get the other values.
--
Duane Hookom
MS Access MVP
The table is "TblPayrollAllData"
Relevent fields are:
EmployeeName, SSNo,
JobNo,GlAcct,Earningclass,JobDescription,RegRate,OtRate
Here is what I have so far, and it seems to be working, but they need
the
relationship to the rest of the data:
SELECT monreghrs As HrsWrkd, [WeDate]- 6 As WorkDate , "Reg" as
TimeType
FROM TblPayrollAllData
WHERE monreghrs>=0
UNION
SELECT monothrs, [WeDate]- 6, "OT"
FROM TblPayrollAllData
WHERE monothrs >=0
UNION
SELECT tuereghrs, [WeDate]- 5, "Reg"
FROM TblPayrollAllData
WHERE tuereghrs>=0
UNION
SELECT tueothrs, [WeDate]- 5, "OT"
FROM TblPayrollAllData
WHERE tueothrs >=0
UNION
SELECT wedreghrs, [WeDate]- 4, "Reg"
FROM TblPayrollAllData
WHERE wedreghrs>=0
UNION
SELECT wedothrs, [WeDate]- 4, "OT"
FROM TblPayrollAllData
WHERE wedothrs >=0
UNION
SELECT THUreghrs, [WeDate]- 3, "Reg"
FROM TblPayrollAllData
WHERE THUreghrs>=0
UNION
SELECT THUothrs, [WeDate]- 3, "OT"
FROM TblPayrollAllData
WHERE THUothrs >=0
UNION
SELECT frireghrs, [WeDate]- 2, "Reg"
FROM TblPayrollAllData
WHERE frireghrs>=0
UNION
SELECT friothrs, [WeDate]- 2, "OT"
FROM TblPayrollAllData
WHERE friothrs >=0
UNION
SELECT Satreghrs, [WeDate]- 1, "Reg"
FROM TblPayrollAllData
WHERE Satreghrs>=0
UNION
SELECT Satothrs, [WeDate]- 1, "OT"
FROM TblPayrollAllData
WHERE Satothrs >=0
UNION
SELECT Sunreghrs, [WeDate], "Reg"
FROM TblPayrollAllData
WHERE Sunreghrs>=0
UNION SELECT tueothrs, [WeDate], "OT"
FROM TblPayrollAllData
WHERE Sunothrs >=0;
Thank you,
Annelie
Please provide all the relevant field names as well as the table
names.
--
Duane Hookom
MS Access MVP
I found that finally in the help. Thank you for your help.
Needless to say, this is my first union query. I am able to
get
the
hours
and dates as I need them. Now I need to add the Employee Name, Job
number
und other pertinent info. With what statement do I add that.
It
all
comes
from the same table.
Annelie
how do I start an SQL query?
You can then normalize your data with a union query
SELECT monreghrs As HrsWrkd, [WeekEnding]- 5 As WrkDate , "Reg"
as
TimeType
FROM tblA
WHERE monreghrs>0
UNION
SELECT monothrs, [WeekEnding]- 5, "OT"
FROM tblA
WHERE monothrs >0
UNION
SELECT tuereghrs, [WeekEnding]- 4, "Reg"
FROM tblA
WHERE tuereghrs>0
UNION
SELECT tueothrs, [WeekEnding]- 4, "OT"
FROM tblA
WHERE tueothrs >0
UNION
...etc...
You can then query all the dates in the union query for a
particular
month.
--
Duane Hookom
MS Access MVP
Unfortunately, this is the way is right now. I guess, if I
could
turn
time
back, I would save the data in a different way. However,
I
was
worried
about
size, since the original data comes over from Quickbooks and
each
weeks
data
has somewhere between 600 and 900 rows. My year to date table
(after
10
month) has 4600 records, having each day worked in there per
job
per
day,
could easily reach 10 times that much.
I have not worked with access long enough to see if that would
really
slow
things down. Please tell me.
Annelie
message
Is there anyway that you can normalize your table structure
so
that
you
don't have fields for each day of the week. A more
normalized
structure
would simplify pulling hours worked per month since each
day's
hours
would
be contained in its own record.
--
Duane Hookom
MS Access MVP
My table shows hrs worked per day, Mon, Tue, Wed,
etc
with
a
WeekEnding
date
for the week.
Now I need to get back the days worked, because I
need
to
create
a
report
of
hours worked per month.
I added to my query:\
WorkDate: IIf([monreghrs] Or
[monothrs]>=0,[wedate]-6,IIf([tuereghrs]
Or
[tueothrs]>=0,[wedate]-5,IIf([wedreghrs] Or
[wedothrs]>=0,[wedate]-4,IIf([thureghrs] Or
[thuothrs]>=0,[wedate]-3,IIf([frireghrs] Or
[friothrs]>=0,[wedate]-2,IIf([satreghrs] Or
[satothrs]>=0,[wedate]-1,IIf([sunreghrs] Or
[sunothrs]>=0,[wedate])))))))
I was hoping it would give me an extra line for each day,
but
I
only
get
the
date of the first day encountered. How can I extract the
exact
date?
Annelie
