If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Creating a Report
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? -- Don C |
#2
|
|||
|
|||
Creating a Report
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 "Don C" wrote: 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? -- Don C |
#3
|
|||
|
|||
Creating a Report
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 "Don C" wrote: 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? -- Don C |
#4
|
|||
|
|||
Creating a Report
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." "Don C" wrote in message ... 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? -- Don C |
Thread Tools | |
Display Modes | |
|
|