Creating a Report

[Don C]

I have a form with EMPLOYEES NAME, RATE, DATE WORKED, TIME IN, TIME OUT,
HOURS WORKED, and EARNED. I am trying to make a report. I have most
everything working like I want it to. I just have a few questions that I
can’t figure out because I am new to Access.

Question 1: I am trying to sub- total the HOURS WORKED and the EARNED by the
week with an ending date of Sunday. (IE: Monday – Sunday.) How do I
sub-total?

Question 2: In EARNED, when I click on create and then reports, EARNED does
not show up like everything else on the report. I think it is because on my
form EARNED has an expression in it, Hours Worked *Rate. How can I have
EARNED show up in my report?

Question 3: After I build the report, I would like to have it work by
EMPLOYEE so that I can see how much each employee earned. I would also like
to see the report with all 5 employees on it so I can see how much we spend.
Do I do this with a Macro or something?

 

[Ken Sheridan]

Base the report on a query which includes not only the columns from the
table(s) but also the computed fields described below:

1. Add two computed fields to the query, using the following expressions:

WorkYear: Year([Date Worked])
WorkWeek: DatePart("ww",[Date Worked],2)

2. Add a computed field to the query , using the following expression:

Earned: [Hours Worked] * [Rate]

You now need to group the report in design view by means of its internal
sorting and grouping mechanism, firstly by Employees Name, then by WorkYear
and then by WorkWeek. Include a group header for Employees Name and a group
footer for WorkWeek. Put the Employees Name bound control in the group
header, the other bound controls in the detail section, and put unbound text
box controls in the group footer with ControlSource properties of:

=Sum([Hours Worked]
= Sum([Earned])

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

Base the report on a query which includes not only the columns from the
table(s) but also the computed fields described below:

1. Add two computed fields to the query, using the following expressions:

WorkYear: Year([Date Worked])
WorkWeek: DatePart("ww",[Date Worked],2)

2. Add a computed field to the query , using the following expression:

Earned: [Hours Worked] * [Rate]

You now need to group the report in design view by means of its internal
sorting and grouping mechanism, firstly by Employees Name, then by WorkYear
and then by WorkWeek. Include a group header for Employees Name and a group
footer for WorkWeek. Put the Employees Name bound control in the group
header, the other bound controls in the detail section, and put unbound text
box controls in the group footer with ControlSource properties of:

=Sum([Hours Worked]
= Sum([Earned])

Ken Sheridan
Stafford, England

 

[Al Campagna]

Don,
Usually... it's best not to ask 3 questions on one post. No big deal at
all... but, if different responders pipe in on different issues, the flow of
the thread could get a bit messy.
It sounds like you're calling out the form values on your report, for
each field. And... that method only reports on the one employee on the form
at that time. You really need to use a table or query (with all your
employee work data) as the RecordSource for your report.

1. Use...
DatePart("ww", [YourDateField])
to yield the week number. You should Sort and Group, and probably
Sum, on that number.
You should also calculate the Year. Year([YourDateField]) (see
#3)

2. Sounds like Earned is a calculated field on your form, and is not
svaed to the table.
That's the right way to do it.
In your query behind your report, recalculate Earned "on the fly".
Earned : [Hours Worked] * [Rate]
Now Earned can be placed on the report, and summed in any group
footer, or report footer.

3. You should have a query behind your report, (the RecordSource)...
based on the same table as your form.
Your report should be Sorted & Grouped on Year, and WeekNo, and
EmployeeID, and if needed, summed by Year group, by Week group, and by
Employee.

Note: Your table should also have a unique key value, such as an
EmployeeID or EmpID.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."