too complex for me!

  • Thread starter Thread starter RandyH
  • Start date Start date
R

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
 
Do you have table/field structures to share as well as sample data and
expected results? Or, do you expect us to make WAGs?
 
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

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


RandyH said:
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
Click to expand...
Click to expand...
 
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


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

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


RandyH said:
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
Click to expand...
of
the
Labor Hours =sum([LaborHours]). This is working well. My problem is
Click to expand...
that
now
overtime, Vacation, Personal and Holiday hours need to be introduced
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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.


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


RandyH said:
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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


RandyH said:
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.


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


RandyH said:
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
Click to expand...
all
of
the
Labor Hours =sum([LaborHours]). This is working well. My problem
Click to expand...
is
that
now
overtime, Vacation, Personal and Holiday hours need to be
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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?


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


RandyH said:
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,
Click to expand...
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.


Duane Hookom said:
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
Click to expand...
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
Click to expand...
that
task
(account). Then I have a formula on the report footer that
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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


RandyH said:
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?


Duane Hookom said:
Randy,
Looking back at your previous messages, how do you think we were
Click to expand...
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


RandyH said:
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
Click to expand...
hours
on
Wednesday), used 4 vacation hours (on Wednesday), 12 holiday hours (on
Click to expand...
8
on
Thursday and 4 on Friday) and 4 personal hours (on Friday). Now, I
Click to expand...
Click to expand...
have
no
problem adding up all of the hours to get 40 for that employee, but,
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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
Duane Hookom said:
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


RandyH said:
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?


Duane Hookom said:
Randy,
Looking back at your previous messages, how do you think we were
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
(on
8
on
Thursday and 4 on Friday) and 4 personal hours (on Friday). Now, I
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top