how can I pick the days apart again

A

Annelie

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
 
D

Duane Hookom

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.
 
A

Annelie

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


Duane Hookom said:
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


Annelie said:
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
 
D

Duane Hookom

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


Annelie said:
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


Duane Hookom said:
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


Annelie said:
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
 
A

Annelie

how do I start an SQL query?

Duane Hookom said:
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


Annelie said:
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


Duane Hookom said:
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
 
A

Annelie

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

Annelie said:
how do I start an SQL query?

Duane Hookom said:
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


Annelie said:
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


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
 
D

Duane Hookom

You need to create a new query and then go to the SQL view. Then you have to
type in the SQL like I typed in below. There is no drag and drop or other
gui stuff. You last line should end with ";"
--
Duane Hookom
MS Access MVP


Annelie said:
how do I start an SQL query?

Duane Hookom said:
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


Annelie said:
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


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
 
D

Duane Hookom

Please provide all the relevant field names as well as the table names.

--
Duane Hookom
MS Access MVP


Annelie said:
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

Annelie said:
how do I start an SQL query?

Duane Hookom said:
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


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
 
A

Annelie

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



Duane Hookom said:
Please provide all the relevant field names as well as the table names.

--
Duane Hookom
MS Access MVP


Annelie said:
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

Annelie said:
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


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
 
D

Duane Hookom

What is the primary or unique field(s) in the table? For instance 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


Annelie said:
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



Duane Hookom said:
Please provide all the relevant field names as well as the table names.

--
Duane Hookom
MS Access MVP


Annelie said:
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


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
 
A

Annelie

An Employee can have several records per day, there is for example the
driver, who has hours for several jobs each workday. I need the hours by
employee by job by day, along with the GLAcct which indicates where he
worked, it effects the workers comp code. This is a construction company,
worker's comp gets more expensive about every 2 floors. Of course I also
need rate of pay to be able to calculate the gross pay along with the cost
to the employer.
So there nothing really unique, I need all records, except zero values
Can you please tell me how I can add all the fields to the SQL by example. I
tried a bunch of ways and it does not work.
Thank you,
Annelie

Duane Hookom said:
What is the primary or unique field(s) in the table? For instance 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


Annelie said:
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



Duane Hookom said:
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


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
 
D

Duane Hookom

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


Annelie said:
An Employee can have several records per day, there is for example the
driver, who has hours for several jobs each workday. I need the hours by
employee by job by day, along with the GLAcct which indicates where he
worked, it effects the workers comp code. This is a construction company,
worker's comp gets more expensive about every 2 floors. Of course I also
need rate of pay to be able to calculate the gross pay along with the cost
to the employer.
So there nothing really unique, I need all records, except zero values
Can you please tell me how I can add all the fields to the SQL by example. I
tried a bunch of ways and it does not work.
Thank you,
Annelie

Duane Hookom said:
What is the primary or unique field(s) in the table? For instance 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


Annelie said:
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


Is there anyway that you can normalize your table
structure
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
 
A

Annelie

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


Annelie said:
An Employee can have several records per day, there is for example the
driver, who has hours for several jobs each workday. I need the hours by
employee by job by day, along with the GLAcct which indicates where he
worked, it effects the workers comp code. This is a construction company,
worker's comp gets more expensive about every 2 floors. Of course I also
need rate of pay to be able to calculate the gross pay along with the cost
to the employer.
So there nothing really unique, I need all records, except zero values
Can you please tell me how I can add all the fields to the SQL by
example.
I
tried a bunch of ways and it does not work.
Thank you,
Annelie

Duane Hookom said:
What is the primary or unique field(s) in the table? For instance 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


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
 
D

Duane Hookom

I'm pleased you had some good results. Union queries are a bit intimidating
since you have to understand the sql rather than just drag and drop.

Good luck,
--
Duane Hookom
MS Access MVP


Annelie said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top