too complex for me!

[RandyH]

TIA

I have a simple report based on a parameter query. The report shows Account
Numbers and the number of Labor Hours that employee spent on that task
(account). Then I have a formula on the report footer that totals all of the
Labor Hours =sum([LaborHours]). This is working well. My problem is that now
overtime, Vacation, Personal and Holiday hours need to be introduced to the
report. There are unique account numbers for each of the new parameters.
Anybody have any thoughts on how to separate regular hours from overtime,
vacation, personal and holiday on the report footer?
i.e. The report footer should show the total of each of these account hours
on a separate line, like this...

Vacation Hours 0
Personal Days 0
Holidays 0
Regular Hours 80
Overtime Hours 16



Randy

 

[Duane Hookom]

Do you have table/field structures to share as well as sample data and
expected results? Or, do you expect us to make WAGs?

 

[RandyH]

Ok, unfamiliar with "WAGs", but I get the jist I didn't supply enough
information - sorry, here goes...
My report query (Time Sheet Report Query) is pulling data from the 'Time',
'Employees' and 'Accounts' tables. The 'Time' table contains the LaborHours
(text data type), LaborDate and EmployeeID fields with a Record Number
(autonumber key) field. The data from the 'Employees' table contains the
employees first and last name, EmployeeID (key), RegularWage and OTWage
fields. The 'Accounts' table has the AccountNumber (key),
AccountDescription, Departments and EmployeeID fields.
The idea, I guess, is to sum up the LaborHours from each account and show
the totals at the bottom of the report.

Does this help?
Randy

Do you have table/field structures to share as well as sample data and
expected results? Or, do you expect us to make WAGs?

--
Duane Hookom
MS Access MVP

TIA

I have a simple report based on a parameter query. The report shows Account
Numbers and the number of Labor Hours that employee spent on that task
(account). Then I have a formula on the report footer that totals all of the
Labor Hours =sum([LaborHours]). This is working well. My problem is that now
overtime, Vacation, Personal and Holiday hours need to be introduced to the
report. There are unique account numbers for each of the new parameters.
Anybody have any thoughts on how to separate regular hours from overtime,
vacation, personal and holiday on the report footer?
i.e. The report footer should show the total of each of these account hours
on a separate line, like this...

Vacation Hours 0
Personal Days 0
Holidays 0
Regular Hours 80
Overtime Hours 16



Randy

 

[Duane Hookom]

I don't see any mention of "overtime, Vacation, Personal and Holiday hours".
If we don't see it these fields/values and don't see an example of any field
values, I don't know how you expect anyone to tell you how to calculate the
values.

WAG is "Wild A** Guess". It is a fairly common abbreviate used on the
internet.

--
Duane Hookom
MS Access MVP

Ok, unfamiliar with "WAGs", but I get the jist I didn't supply enough
information - sorry, here goes...
My report query (Time Sheet Report Query) is pulling data from the 'Time',
'Employees' and 'Accounts' tables. The 'Time' table contains the LaborHours
(text data type), LaborDate and EmployeeID fields with a Record Number
(autonumber key) field. The data from the 'Employees' table contains the
employees first and last name, EmployeeID (key), RegularWage and OTWage
fields. The 'Accounts' table has the AccountNumber (key),
AccountDescription, Departments and EmployeeID fields.
The idea, I guess, is to sum up the LaborHours from each account and show
the totals at the bottom of the report.

Does this help?
Randy

Do you have table/field structures to share as well as sample data and
expected results? Or, do you expect us to make WAGs?

--
Duane Hookom
MS Access MVP

TIA

I have a simple report based on a parameter query. The report shows Account
Numbers and the number of Labor Hours that employee spent on that task
(account). Then I have a formula on the report footer that totals all

of
the

Labor Hours =sum([LaborHours]). This is working well. My problem is

that
now

overtime, Vacation, Personal and Holiday hours need to be introduced

to
the

report. There are unique account numbers for each of the new parameters.
Anybody have any thoughts on how to separate regular hours from overtime,
vacation, personal and holiday on the report footer?
i.e. The report footer should show the total of each of these account hours
on a separate line, like this...

Vacation Hours 0
Personal Days 0
Holidays 0
Regular Hours 80
Overtime Hours 16



Randy

 

[RandyH]

I think were making this too hard. The numbers are irrelevant, the fields
"overtime, Vacation, Personal and Holiday hours" are just that, the amount
of hours the employee had - most of the time the value is zero but can be
any number from .1 to 1000. I can sum up all hours worked including 'earned'
hours (via ot, vacation, personal or holiday), but do not know how to
separate 'regular' hours from the 'earned' hours. Let's say an employee
worked a 20 hour week (8 hours on Monday, 8 hours on Tuesday and 4 hours on
Wednesday), used 4 vacation hours (on Wednesday), 12 holiday hours (on 8 on
Thursday and 4 on Friday) and 4 personal hours (on Friday). Now, I have no
problem adding up all of the hours to get 40 for that employee, but, what I
want the report to show at the bottom is...

Regular hours 20
Vacation hours 4
Holiday hours 12
Personal hours 4

I don't know if this can be leveraged but, each 'earned' field has a unique
account number (account numbers are: vacation # 3260, holiday #3265 and
personal #3280) whereas the regular hours do not.

I don't see any mention of "overtime, Vacation, Personal and Holiday hours".
If we don't see it these fields/values and don't see an example of any field
values, I don't know how you expect anyone to tell you how to calculate the
values.

WAG is "Wild A** Guess". It is a fairly common abbreviate used on the
internet.

--
Duane Hookom
MS Access MVP

Ok, unfamiliar with "WAGs", but I get the jist I didn't supply enough
information - sorry, here goes...
My report query (Time Sheet Report Query) is pulling data from the 'Time',
'Employees' and 'Accounts' tables. The 'Time' table contains the LaborHours
(text data type), LaborDate and EmployeeID fields with a Record Number
(autonumber key) field. The data from the 'Employees' table contains the
employees first and last name, EmployeeID (key), RegularWage and OTWage
fields. The 'Accounts' table has the AccountNumber (key),
AccountDescription, Departments and EmployeeID fields.
The idea, I guess, is to sum up the LaborHours from each account and show
the totals at the bottom of the report.

Does this help?
Randy

all

of

the
Labor Hours =sum([LaborHours]). This is working well. My problem is that
now
overtime, Vacation, Personal and Holiday hours need to be introduced to
the
report. There are unique account numbers for each of the new parameters.
Anybody have any thoughts on how to separate regular hours from overtime,
vacation, personal and holiday on the report footer?
i.e. The report footer should show the total of each of these account
hours
on a separate line, like this...

Vacation Hours 0
Personal Days 0
Holidays 0
Regular Hours 80
Overtime Hours 16



Randy

 

[Duane Hookom]

Randy,
Looking back at your previous messages, how do you think we were supposed to
know that the Account Number had anything to do with vacation, holiday,...?
You can use expressions in your report footer text boxes like
=Sum(Abs(AccountNumber=3260) * Val([LabourHours]))

BTW: Consider finding and using a naming convention. "Time" is a function
name and should not be used to name objects such as forms, fields,
tables,...
You stated LabourHours is text. Is there a reason why this is not numeric?
The above expression assumes AccountNumber is a numeric value.

I am still not sure this solution is complete since you haven't provided any
sample records from your query.

--
Duane Hookom
MS Access MVP

I think were making this too hard. The numbers are irrelevant, the fields
"overtime, Vacation, Personal and Holiday hours" are just that, the amount
of hours the employee had - most of the time the value is zero but can be
any number from .1 to 1000. I can sum up all hours worked including 'earned'
hours (via ot, vacation, personal or holiday), but do not know how to
separate 'regular' hours from the 'earned' hours. Let's say an employee
worked a 20 hour week (8 hours on Monday, 8 hours on Tuesday and 4 hours on
Wednesday), used 4 vacation hours (on Wednesday), 12 holiday hours (on 8 on
Thursday and 4 on Friday) and 4 personal hours (on Friday). Now, I have no
problem adding up all of the hours to get 40 for that employee, but, what I
want the report to show at the bottom is...

Regular hours 20
Vacation hours 4
Holiday hours 12
Personal hours 4

I don't know if this can be leveraged but, each 'earned' field has a unique
account number (account numbers are: vacation # 3260, holiday #3265 and
personal #3280) whereas the regular hours do not.

I don't see any mention of "overtime, Vacation, Personal and Holiday hours".
If we don't see it these fields/values and don't see an example of any field
values, I don't know how you expect anyone to tell you how to calculate the
values.

WAG is "Wild A** Guess". It is a fairly common abbreviate used on the
internet.

--
Duane Hookom
MS Access MVP

Ok, unfamiliar with "WAGs", but I get the jist I didn't supply enough
information - sorry, here goes...
My report query (Time Sheet Report Query) is pulling data from the 'Time',
'Employees' and 'Accounts' tables. The 'Time' table contains the LaborHours
(text data type), LaborDate and EmployeeID fields with a Record Number
(autonumber key) field. The data from the 'Employees' table contains the
employees first and last name, EmployeeID (key), RegularWage and OTWage
fields. The 'Accounts' table has the AccountNumber (key),
AccountDescription, Departments and EmployeeID fields.
The idea, I guess, is to sum up the LaborHours from each account and show
the totals at the bottom of the report.

Does this help?
Randy

Do you have table/field structures to share as well as sample data and
expected results? Or, do you expect us to make WAGs?

--
Duane Hookom
MS Access MVP


TIA

I have a simple report based on a parameter query. The report shows
Account
Numbers and the number of Labor Hours that employee spent on that task
(account). Then I have a formula on the report footer that totals

all

of
the
Labor Hours =sum([LaborHours]). This is working well. My problem

is
that

now
overtime, Vacation, Personal and Holiday hours need to be

introduced
to

the
report. There are unique account numbers for each of the new parameters.
Anybody have any thoughts on how to separate regular hours from
overtime,
vacation, personal and holiday on the report footer?
i.e. The report footer should show the total of each of these account
hours
on a separate line, like this...

Vacation Hours 0
Personal Days 0
Holidays 0
Regular Hours 80
Overtime Hours 16



Randy

 

[RandyH]

Duane, I had LaborHours as text because we were using a hyphen between
account numbers (i.e. 32-60), I have since taken the hyphen out and made the
field a number. When I call the report, it asks for input for the field
LaborHours, it should just totalize what's there. Any thoughts?

Randy,
Looking back at your previous messages, how do you think we were supposed to
know that the Account Number had anything to do with vacation, holiday,...?
You can use expressions in your report footer text boxes like
=Sum(Abs(AccountNumber=3260) * Val([LabourHours]))

BTW: Consider finding and using a naming convention. "Time" is a function
name and should not be used to name objects such as forms, fields,
tables,...
You stated LabourHours is text. Is there a reason why this is not numeric?
The above expression assumes AccountNumber is a numeric value.

I am still not sure this solution is complete since you haven't provided any
sample records from your query.

--
Duane Hookom
MS Access MVP

I think were making this too hard. The numbers are irrelevant, the fields
"overtime, Vacation, Personal and Holiday hours" are just that, the amount
of hours the employee had - most of the time the value is zero but can be
any number from .1 to 1000. I can sum up all hours worked including 'earned'
hours (via ot, vacation, personal or holiday), but do not know how to
separate 'regular' hours from the 'earned' hours. Let's say an employee
worked a 20 hour week (8 hours on Monday, 8 hours on Tuesday and 4 hours on
Wednesday), used 4 vacation hours (on Wednesday), 12 holiday hours (on 8 on
Thursday and 4 on Friday) and 4 personal hours (on Friday). Now, I have no
problem adding up all of the hours to get 40 for that employee, but,

what

I
want the report to show at the bottom is...

Regular hours 20
Vacation hours 4
Holiday hours 12
Personal hours 4

I don't know if this can be leveraged but, each 'earned' field has a unique
account number (account numbers are: vacation # 3260, holiday #3265 and
personal #3280) whereas the regular hours do not.

I don't see any mention of "overtime, Vacation, Personal and Holiday hours".
If we don't see it these fields/values and don't see an example of any field
values, I don't know how you expect anyone to tell you how to

calculate
the

values.

WAG is "Wild A** Guess". It is a fairly common abbreviate used on the
internet.

--
Duane Hookom
MS Access MVP


Ok, unfamiliar with "WAGs", but I get the jist I didn't supply enough
information - sorry, here goes...
My report query (Time Sheet Report Query) is pulling data from the 'Time',
'Employees' and 'Accounts' tables. The 'Time' table contains the
LaborHours
(text data type), LaborDate and EmployeeID fields with a Record Number
(autonumber key) field. The data from the 'Employees' table contains the
employees first and last name, EmployeeID (key), RegularWage and OTWage
fields. The 'Accounts' table has the AccountNumber (key),
AccountDescription, Departments and EmployeeID fields.
The idea, I guess, is to sum up the LaborHours from each account and show
the totals at the bottom of the report.

Does this help?
Randy

Do you have table/field structures to share as well as sample data and
expected results? Or, do you expect us to make WAGs?

--
Duane Hookom
MS Access MVP


TIA

I have a simple report based on a parameter query. The report shows
Account
Numbers and the number of Labor Hours that employee spent on

that
task

(account). Then I have a formula on the report footer that

totals
all

of
the
Labor Hours =sum([LaborHours]). This is working well. My problem is
that
now
overtime, Vacation, Personal and Holiday hours need to be introduced
to
the
report. There are unique account numbers for each of the new
parameters.
Anybody have any thoughts on how to separate regular hours from
overtime,
vacation, personal and holiday on the report footer?
i.e. The report footer should show the total of each of these account
hours
on a separate line, like this...

Vacation Hours 0
Personal Days 0
Holidays 0
Regular Hours 80
Overtime Hours 16



Randy

 

[Duane Hookom]

What kind of value are you storing that you had previously stored 32-60 and
can now store an actual number. Apparently you do not have a field in your
report's record source named "LaborHours".

--
Duane Hookom
MS Access MVP

Duane, I had LaborHours as text because we were using a hyphen between
account numbers (i.e. 32-60), I have since taken the hyphen out and made the
field a number. When I call the report, it asks for input for the field
LaborHours, it should just totalize what's there. Any thoughts?

Randy,
Looking back at your previous messages, how do you think we were

supposed

to
know that the Account Number had anything to do with vacation, holiday,...?
You can use expressions in your report footer text boxes like
=Sum(Abs(AccountNumber=3260) * Val([LabourHours]))

BTW: Consider finding and using a naming convention. "Time" is a function
name and should not be used to name objects such as forms, fields,
tables,...
You stated LabourHours is text. Is there a reason why this is not numeric?
The above expression assumes AccountNumber is a numeric value.

I am still not sure this solution is complete since you haven't provided any
sample records from your query.

--
Duane Hookom
MS Access MVP

I think were making this too hard. The numbers are irrelevant, the fields
"overtime, Vacation, Personal and Holiday hours" are just that, the amount
of hours the employee had - most of the time the value is zero but can be
any number from .1 to 1000. I can sum up all hours worked including 'earned'
hours (via ot, vacation, personal or holiday), but do not know how to
separate 'regular' hours from the 'earned' hours. Let's say an employee
worked a 20 hour week (8 hours on Monday, 8 hours on Tuesday and 4

hours
on

Wednesday), used 4 vacation hours (on Wednesday), 12 holiday hours (on

8
on

Thursday and 4 on Friday) and 4 personal hours (on Friday). Now, I

have

no

problem adding up all of the hours to get 40 for that employee, but,

what

I
want the report to show at the bottom is...

Regular hours 20
Vacation hours 4
Holiday hours 12
Personal hours 4

I don't know if this can be leveraged but, each 'earned' field has a unique
account number (account numbers are: vacation # 3260, holiday #3265 and
personal #3280) whereas the regular hours do not.


I don't see any mention of "overtime, Vacation, Personal and Holiday
hours".
If we don't see it these fields/values and don't see an example of any
field
values, I don't know how you expect anyone to tell you how to calculate
the
values.

WAG is "Wild A** Guess". It is a fairly common abbreviate used on the
internet.

--
Duane Hookom
MS Access MVP


Ok, unfamiliar with "WAGs", but I get the jist I didn't supply
enough
information - sorry, here goes...
My report query (Time Sheet Report Query) is pulling data from the
'Time',
'Employees' and 'Accounts' tables. The 'Time' table contains the
LaborHours
(text data type), LaborDate and EmployeeID fields with a Record Number
(autonumber key) field. The data from the 'Employees' table

contains
the

employees first and last name, EmployeeID (key), RegularWage and OTWage
fields. The 'Accounts' table has the AccountNumber (key),
AccountDescription, Departments and EmployeeID fields.
The idea, I guess, is to sum up the LaborHours from each account and
show
the totals at the bottom of the report.

Does this help?
Randy

Do you have table/field structures to share as well as sample

data
and

expected results? Or, do you expect us to make WAGs?

--
Duane Hookom
MS Access MVP


TIA

I have a simple report based on a parameter query. The report shows
Account
Numbers and the number of Labor Hours that employee spent on that
task
(account). Then I have a formula on the report footer that totals
all
of
the
Labor Hours =sum([LaborHours]). This is working well. My

problem
is

that
now
overtime, Vacation, Personal and Holiday hours need to be introduced
to
the
report. There are unique account numbers for each of the new
parameters.
Anybody have any thoughts on how to separate regular hours from
overtime,
vacation, personal and holiday on the report footer?
i.e. The report footer should show the total of each of these
account
hours
on a separate line, like this...

Vacation Hours 0
Personal Days 0
Holidays 0
Regular Hours 80
Overtime Hours 16



Randy

 

[RandyH]

Sorry, I just copied your formula, you spelled labor differently than I.
Once I changed it, it worked fine. Thanks!
The database is new, so I could easily change my LaborHours field from text
to number (we just decided to not use the hyphen).

Randy

What kind of value are you storing that you had previously stored 32-60 and
can now store an actual number. Apparently you do not have a field in your
report's record source named "LaborHours".

--
Duane Hookom
MS Access MVP

Duane, I had LaborHours as text because we were using a hyphen between
account numbers (i.e. 32-60), I have since taken the hyphen out and made the
field a number. When I call the report, it asks for input for the field
LaborHours, it should just totalize what's there. Any thoughts?

Randy,
Looking back at your previous messages, how do you think we were

supposed

to
know that the Account Number had anything to do with vacation, holiday,...?
You can use expressions in your report footer text boxes like
=Sum(Abs(AccountNumber=3260) * Val([LabourHours]))

BTW: Consider finding and using a naming convention. "Time" is a function
name and should not be used to name objects such as forms, fields,
tables,...
You stated LabourHours is text. Is there a reason why this is not numeric?
The above expression assumes AccountNumber is a numeric value.

I am still not sure this solution is complete since you haven't

provided
any

sample records from your query.

--
Duane Hookom
MS Access MVP


I think were making this too hard. The numbers are irrelevant, the fields
"overtime, Vacation, Personal and Holiday hours" are just that, the amount
of hours the employee had - most of the time the value is zero but

can
be

any number from .1 to 1000. I can sum up all hours worked including
'earned'
hours (via ot, vacation, personal or holiday), but do not know how to
separate 'regular' hours from the 'earned' hours. Let's say an employee
worked a 20 hour week (8 hours on Monday, 8 hours on Tuesday and 4 hours
on
Wednesday), used 4 vacation hours (on Wednesday), 12 holiday hours

(on

8

on
Thursday and 4 on Friday) and 4 personal hours (on Friday). Now, I

have

no
problem adding up all of the hours to get 40 for that employee, but, what
I
want the report to show at the bottom is...

Regular hours 20
Vacation hours 4
Holiday hours 12
Personal hours 4

I don't know if this can be leveraged but, each 'earned' field has a
unique
account number (account numbers are: vacation # 3260, holiday #3265 and
personal #3280) whereas the regular hours do not.


I don't see any mention of "overtime, Vacation, Personal and Holiday
hours".
If we don't see it these fields/values and don't see an example of any
field
values, I don't know how you expect anyone to tell you how to calculate
the
values.

WAG is "Wild A** Guess". It is a fairly common abbreviate used on the
internet.

--
Duane Hookom
MS Access MVP


Ok, unfamiliar with "WAGs", but I get the jist I didn't supply
enough
information - sorry, here goes...
My report query (Time Sheet Report Query) is pulling data from the
'Time',
'Employees' and 'Accounts' tables. The 'Time' table contains the
LaborHours
(text data type), LaborDate and EmployeeID fields with a Record Number
(autonumber key) field. The data from the 'Employees' table contains
the
employees first and last name, EmployeeID (key), RegularWage and
OTWage
fields. The 'Accounts' table has the AccountNumber (key),
AccountDescription, Departments and EmployeeID fields.
The idea, I guess, is to sum up the LaborHours from each account and
show
the totals at the bottom of the report.

Does this help?
Randy

Do you have table/field structures to share as well as sample data
and
expected results? Or, do you expect us to make WAGs?

--
Duane Hookom
MS Access MVP


TIA

I have a simple report based on a parameter query. The report
shows
Account
Numbers and the number of Labor Hours that employee spent on that
task
(account). Then I have a formula on the report footer that totals
all
of
the
Labor Hours =sum([LaborHours]). This is working well. My problem
is
that
now
overtime, Vacation, Personal and Holiday hours need to be
introduced
to
the
report. There are unique account numbers for each of the new
parameters.
Anybody have any thoughts on how to separate regular hours from
overtime,
vacation, personal and holiday on the report footer?
i.e. The report footer should show the total of each of these
account
hours
on a separate line, like this...

Vacation Hours 0
Personal Days 0
Holidays 0
Regular Hours 80
Overtime Hours 16



Randy