Time Sheet Report 1

[mhoque]

Please forgive my "not so bright" question. I am a new user of ACCESS. My DB is designed to track project hours. I would like to create a report to show

Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8
Proj# ProjName Hrs(mon)Hrs(T) Hrs(W) Hrs(Th) Hrs(F) Hrs(Total)

I tried to use Crosstab query but get error message: "Too many column heading...."
How may I accomplish this goal? Please help. Thanks.

 

[razchip]

If these column headings are actually fields in a table, just assembly a query and add the project hours up.

Thanks for the help.
Greg

 

[GingerR]

You'll have to tell us your table structure, and giving us some sample data along with what the output should look like (with sample data) would help us help you better.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[mhoque]

Thanks GingerR. Here are some details.
Table1:Employee
Fields: EmployeeID, LastName, FirstName

Table2rojects
FieldsrojectID, JobNumber,ProjectName, ProjectClass,

Table3:Work Codes (To separate regular hours vs. OT, Sick, Vac hours etc)
Fields: WorkCodeID, WorkCode, WorkCodeDescr

Table3:EmployeeHours
Fields:RecordID, EmployeeID, DateWorked, ProjectID,Hours, WorkCodeID

I need to generate time sheet that will look like this:

Project Date1 Date2 Date3 Date4 Date5 Total
Mon Tue Wed Thu Fri
Proj A 5 2 3 2 1 13
Proj B 1 .5 1 1 1.5 5
-----------------------------------------------
Regular Hours 38
Sick Hours 2
==================================================

Did I answer your question? Please keep in mind I am just a beginner and don't know much.

Right now, my DB works good when staff enters their hours and I print weekly, monthly reports for project hours. I need help with the time sheet part.

Thanks much.

 

[GingerR]

Is DateWorked an actual date and you want it to be changed to a Weekday (Mon, Tues, etc?)

What's the query you wrote so far?

It looks like this certain report is just reporting for one week? If so, will you be selecting or entering a date range some how? If so, how?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[mhoque]

Is DateWorked an actual date and you want it to be changed to a Weekday (Mon, Tues, etc?)

Yes, DateWorked is an actual date. No I don’t want it to be changed to a weekday, rather I would like both date and day to be displayed.

What's the query you wrote so far?

I attempted a crosstab query but with no success. The answer is no query so far. Must I use a query to generate this report?

It looks like this certain report is just reporting for one week? If so, will you be selecting or entering a date range some how? If so, how?

Yes, the report is for a given week in other words, a Weekly Time Sheet. I have a form called frmReport which prompts for the following:
StartDate and a EndDate and Employee Name

Thanks.

 

[GingerR]

Ok, there will be a lot of parts to this, but hang in there.
First, we'll set up the form, where the user will enter the week starting date, pick an employee, and hit a button. Then we'll create the report.

1) On the form frmReport:

a) Put a text box called txtStartDate. Make the format be m/d/yy.

b) Put a combo box called cboEmployee, with the row source being

SELECT Employee.EmployeeID, [LastName] & ", " & [FirstName] AS EmpName FROM Employee;

column count = 2
column widths = 0,1
bound column = 1

c) Put a button on the form, named btnViewEmpReport. In the On Click property, choose [Event Procedure] and click the build button (the little button with the three dots on it to the right). Paste this code in between the SUB and END SUB lines:

    DoCmd.OpenReport "WeeklyTimeSheet", acViewPreview, , "EmployeeID = " & Me.cboEmployee

This will open your report for the selected employee only.

Close and save the form.

2) Now let's make the report. Start a new report, and in design mode make the RecordSource be this:

TRANSFORM Sum(EmployeeHours.Hours) AS SumOfHours SELECT Employee.EmployeeID, EmployeeHours.ProjectID, Projects.ProjectName FROM (Employee INNER JOIN EmployeeHours ON Employee.EmployeeID=EmployeeHours.EmployeeID) INNER JOIN Projects ON EmployeeHours.ProjectID=Projects.ProjectID GROUP BY Employee.EmployeeID, EmployeeHours.ProjectID, Projects.ProjectName ORDER BY EmployeeHours.ProjectID PIVOT Format(Weekday([DateWorked]),"ddd") In ("Mon","Tue","Wed","Thu","Fri") WITH OWNERACCESS OPTION;

If you then look at this query in design view, and run it, you'll see that it shows every employee, every project, for MON, TUE, WED, THU, FRI. But don't worry, it'll open for only the employee you select on the frmReport.

View the field list, and bring the controls for ProjectName and Mon, Tue, Wed, Thu, Fri down into the report. Arrange them in the detail section. Remove the labels and put them in the header (select them, then cut and paste). Also in the header, in order to put something like "Weekly Time Sheet for Tom Smith for Week Starting 9/12/05", make a text box and put this:

="Time Card for " & Forms!frmReport!cboEmployee.column(1) & " for Week Starting " & Forms!frmReport!txtStartDate

. See how it references the frmReport? It will take the start date and emp name right off of that form.

Now, to put the dates above each MON, TUE, WED, THU, FRI label in the header, put one text box above MON and in it put

 =Forms!frmReport!txtStartDate

This puts the start date above Monday.

For the rest, use the Dateadd function, so like above TUE, put a text box and in it put

=DateAdd("d",1,Forms!frmReport!txtStartDate)

. This will add 1 to the Start Date. Do the same for the other days, adding 2, then 3, then 4.

For the TOTALS column, make another text box to the far right. In the control source, put this:

=nz([Mon],0)+nz([Tue],0)+nz([Wed],0)+nz([Thu],0)+nz([FRI],0)

The NZ function makes a value be ZERO (or whatever you put after the comma) in case it's NULL. If things are null, they can't add up. So if you just put MON+TUE+WED+THU+FRI, and one of them had NULL for hours, it wouldn't add up. ok?


Ok--now for the sums at the bottom. We'll put them in the EmployeeID footer. To do this, go VIEW + Sorting and Grouping. Pick field EmployeeID, and down below set Group Footer to YES. Back in the design of the report, put a text box in the EmployeeID footer. In the control Source, put this:

=DSum("Hours","EmployeeHours","EmployeeID = " & Forms!frmReport!cboEmployee & " and DateWorked between #" & Forms!frmReport!txtStartDate & "# and #" & DateAdd("d",5,Forms!frmReport!txtStartDate) & "# and WorkCodeID = 2")

DSum, DLookup, etc are functions that can get data from a table that is not the direct source of your report. So this one that I wrote, sums up the hours in the table EmployeeHours for the selected Employee for the dates between your entered Start Date and 5 days after that, for WorkCodeID 2. Since you didn't note what data types your fields are, I assumed that WorkCodeID was a number. In my data, WorkCodeID 2 is OT. So I'd make the label say "OT:", and put that Dsum code into the corresponsing text box. You can add others for Reg Time, Sick Leave, etc, and just change the WorkCodeID = X portion to reflect the ID Code you need.

When you're done, close the report (Save it as "WeeklyTimeSheet"). Open the form, enter a MONDAY date, select an employee, and hit the button.

Ok, so that's a lot!! Try it out and let us know how far you go.


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[mhoque]

Hi:

It was a great help, thank you. I changed a few things, e.g., using Week Ending Date instead of Week Starting date. This necessitated changes in formulas in “MON”, “TUE”, etc. which I did successfully.

However, I have some other problems:
1) For a given week (eg. Week ending 9/03/05 for employee #1), the report displayed all the hours he worked in a year although, the labels showed correctly 8/29 as Mon 8/30 as Tue etc. However, at the bottom the weekly total was shown correctly, e.g., Regular 32, Sick 8. etc. How to solve this problem?
2) How can I get the project names display in alpha order on the time sheet report?
3) In addition to the ProjectName I need to have the Job Number (Field name: JobNumber (Data Type Text) display in the Time Sheet report..
4) How can I have the dates displayed as “d/mmm” instead of d/mm/yyyy for MON, TUE etc.
5) I also need Total for both vertical and horizontal numbers. Got the one you helped to create, how to do the horizontal one?

Here are some details on the Tables, Fields (not all fields are listed) and its properties:

Table: Employee
EmployeeID (Auto)
LastName (Text)
FirstName (Text)

Table: Projects
ProjectId (Auto)
JobNumber (Text)
ProjectName (Text)

Table: Work Codes (I made a mistake in naming this one and forgot to delete the space)
WorkCodeID (Auto)
WorkCode (Text)
WorkCodeDescr (Text)

Table: EmployeeHour
RecordID (Auto)
EmployeeID (Number)
DateWorked (Date/Time)
Hours (Number)
ProjectID (Number)
WorkCodeID (Number)

Many thanks in advance. I sincerely appreciate.

 

[mhoque]

Please ignore question No.2. I was able to solve it myself. Thanks.

 

[GingerR]

I'm off for the next six hours, but quickly:

1) the labels/dates on the report have nothing to do with the data displayed. YOu have to make the query only show the data from the selected date range. Make sure your query says something like "Between X and Y"

2) ignored

3) Job Number--just add it to your query and to the report. Have you tried and failed? if so post your failure and what you want instead.

4) use the FORMAT property of the text boxes. Look it up in HELP if you need to.

5) I noted how to make your TOTALS column in my last post. Is that what you mean? Do you mean Mon+Tue+Wed+THU+FRI? If so please check my previous post.


Back in a while...

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[mhoque]

1) the labels/dates on the report have nothing to do with the data displayed. YOu have to make the query only show the data from the selected date range. Make sure your query says something like "Between X and Y"

According your previous post, I created only one text box to enter a start date. Where do you want me to enter this date range, in the query itself? How? When I added the DateWorked field to the query and included a “Between StartDate and EndDate” in criteria, it didn’t work.

2) Sorry I meant to ignore question #3. Even though in the query I see that the Project Name to be sorted in ascending order, it’s not coming out as such in the report.

3)Yes, I succeeded.

4) I am familiar with FORMATting a Text Box. But when I clicked on the dropped down list, there was no option for Month/Day rather General Date, Long Date, Medium Date and Short Date. None of these apply to my need.

5) No, I meant, All MON hours at the bottom of MON column, all TUE hours at the bottom of TUE column etc.


Take your time. Thanks

 

[GingerR]

Hi there--good job plugging away at this. I'm impressed!!

1) I'm home, and the db I made to help you is at work, so I'll just have to talk all of this instead of test it. Yes the BETWEEN goes in the query. I just looked and see that I didn't do it for you above, sorry. In the Recordsource of the report, click on the BUILD button (the little button to the right of the RecordSource), and there's your query grid. You'd put the DateWorked into the query grid. Underneath it, in the criteria part, put something like

Between Forms!frmReport!txtEndDate and dateadd("d",5,Forms!frmReport!txtEndDate)

Then when you run the report, you probably get an error that it doesn't recognize Forms!frmReport!txtEndDate? If so, in the design of the query, right-click up in the grey part where the tables are sitting, and pick PARAMETERS. On the left, put (or copy from below and paste here) [Forms]![frmReport]![txtEndDate] and to the right of it, choose Date and close the Parameters pop-up screen. This makes it so that the value from the frmReport will pass to this query.

For it to run properly, you have to have the form open and a date entered into the date text box.

Just a note: To test something like this if you can't get it to work is to put in some real dates that you know have data, like

Between #1/1/05# and #1/7/05#

See if that works. Then continue troubleshooting until you find the problem.

If this still doesn't work, post any error message you get.


2) try using the SORTING and GROUPING: In the Report's design mode, pick the black square in the upper-left corner and right-click and pick Sorting and Grouping. On the left pick ProjectName, on the right leave it at Ascending. See if that works.

4) I assume you're talking about the text boxes you made that have dates in them, right? Not the labels that just say "MON", "TUES", etc? I just want to make sure. Ok--try just typing in the Format what you want, ignore the pull-down menu.

5) In the EmployeeID footer, make a text box and put it under your Monday values. In the control source, put =Sum([Mon]). This is assuming that your text box in the Detail section is called MON.

Keep plugging away!!

g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[mhoque]

1) No this one didn’t work. But I found a different way.

I created a Macro to open the Report called “WeeklyTimeSheet”, added a Where condition that reads: [EmployeeID]=[Forms]![frmTimeSheet]![cboEmployee] And [DateWorked] Between [Forms]![frmTimeSheet]![txtStartDate] And [Forms]![frmTimeSheet]![txtEndDate]
Note: I changed the form name from frmReport to frmTimeSheet
I added a button on the frmTimeSheet and tied this macro to it.

On the report “WeeklyTimeSheet”, I created two Text Boxes on the top right corner called Start: & End: and included the txtStartDate and txtEndDate as its record sources.

Walllaaaaa it worked.


2) Yes it worked.

4) It worked. What are you a magician? I should call you Ginger Houdini.

5) This worked perfect.

Thanks again.


New Problems:
1. Instead of writing each project once, it repeats as many times as it occurs. For example, if an employee was on vacation on Sep 2 and Sep 3, and worked on ProjectA 2 hours on 9/2 and 2 hours on 9/3: it shows data like this:
9/2 9/3
Vacation 8
Vacation 8
ProjectA 2
ProjectA 2

But I would like it to display as this
9/2 9/3
Vacation 8 8
ProjectA 2 2

How do I accomplish this?


2. How to get a Grand Total at the bottom right corner?

3. I would like to have all data perfectly aligned with Mondays, Tuesdays….. and have a line divide each column and row. I have been dragging those labels and text boxes one at a time. Is there a more efficient way of formatting? Or creating a Table with borders and have the data fall to its right place?

4. I am going to follow your earlier instructions to capture Vacation, Holiday for each days (Mondays, Tues etc) instead of just one summary total for these hours. If I run into problem, shall let you know. I must be brave and try first.

Many Thanks.

 

[GingerR]

1) Usually it's because you have something grouped wrong. Post you're report's Record Source.

2) I'd use DSum() (like the formulas for Sick, Holiday, etc). Put it in the Page Footer.

3) sorry, keep on dragging. You can use Align on the toolbar to help--Pick 5 text boxes (use Shift key to pick more than one at a time), then FORMAT+ALIGN+ Bottom or Left or whatever.

4) Ok.

Good job. Keep on truckin'.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[mhoque]

I have exactly what you had in the record source of the report. I added "JobNumber" and Sat & Sun and DateWorked.

TRANSFORM Sum(EmployeeHour.Hours) AS SumOfHours SELECT Employee.EmployeeID, EmployeeHour.ProjectID, Projects.JobNumber, Projects.ProjectName, EmployeeHour.DateWorked FROM Projects INNER JOIN (Employee INNER JOIN EmployeeHour ON Employee.EmployeeID = EmployeeHour.EmployeeID) ON Projects.ProjectID = EmployeeHour.ProjectID GROUP BY Employee.EmployeeID, EmployeeHour.ProjectID, Projects.JobNumber, Projects.ProjectName, EmployeeHour.DateWorked ORDER BY EmployeeHour.ProjectID, Projects.JobNumber PIVOT Format(Weekday([DateWorked]),"ddd") In ("Mon","Tue","Wed","Thu","Fri","Sat","Sun") WITH OWNERACCESS OPTION;

Thanks.

 

[GingerR]

Your SQL is not the same as mine: you have included the DateWorked field, which is grouping the records in a way you do not want. Open the query and run it, and see how the DateWorked field makes new rows because you are GROUPing on it. Take it out and see the difference.

The reason for the column headings ("Mon", "TUE", etc) is to have the query/recordsource not take into account any dates because they will always be changing and a report will not let you use variable column headings (unless you do a lot of crazy programming). You are just displaying the dates as column headings on the report by using formulas in text boxes above the columns, but all we want from the query is MON, TUE, etc.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[mhoque]

I can see the difference in grouping. And I understand what you explained about Mon Tue etc. But I am not getting hours restricted between a specific Mon and Fri.


As I mentioned before it pulls all the hours from time unknown and does not restrict hours between the dates. I believe the Button called btnEmpViewReport needs to be modified to include the DateWorked restriction. Currently the Event Procedure is shown to be: DoCmd.OpenReport "WeeklyTimeSheet", acViewPreview, , "EmployeeID = " & Me.cboEmployee

How do I do this then? Please help.

Here is what we have in the record source of the Report, I added the JobNumber and Sat & Sun

TRANSFORM Sum(EmployeeHour.Hours) AS SumOfHours SELECT Employee.EmployeeID, EmployeeHour.ProjectID, Projects.JobNumber, Projects.ProjectName FROM Projects INNER JOIN (Employee INNER JOIN EmployeeHour ON Employee.EmployeeID = EmployeeHour.EmployeeID) ON Projects.ProjectID = EmployeeHour.ProjectID GROUP BY Employee.EmployeeID, EmployeeHour.ProjectID, Projects.JobNumber, Projects.ProjectName ORDER BY EmployeeHour.ProjectID PIVOT Format(Weekday([DateWorked]),"ddd") In ("Mon","Tue","Wed","Thu","Fri", "Sat","Sun") WITH OWNERACCESS OPTION;

 

[GingerR]

Bring down the DateWorked field. Change the thing in the TOTAL row to be WHERE instead of GROUP BY. In the criteria, put this (tweak as necessary if you change form name, etc)

Between [Forms]![frmReport]![txtStartDate] And DateAdd("d",7,[Forms]![frmReport]![txtStartDate])

Then take this bit out of your macro:
And [DateWorked] Between [Forms]![frmTimeSheet]![txtStartDate] And [Forms]![frmTimeSheet]![txtEndDate]

you don't need that any more.

Also, why do you bother putting both a Start Date and an End date on the form? seems unnecessary as it will always be a week? Seems like you only need one or the other. Easier for the user.

Anyhow, try that and see how it goes.

g


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[mhoque]

It worked! It worked! Thank you so much. I had to tweak a few things here and there. For Example, instead of a Start Date, I chose Week Ending date. This required several modifications, but everything is working great with one exception.

1. I couldn't get the Grand Total to display correctly.
2. I would like to add the employee's title to display below his name.
3. Since I used Week Ending Date as an input, how can I make this entry restricted to Saturdays only? For example, if someone enters a date that is not a Saturday, he will be prompted to an error message. Is it possible to do so?

Many thanks for all your help. I sincerely appreciate.

 

[GingerR]

Good job!

1) please post your forumla. How is it reporting wrong? It should be something like

=DSum("Hours","EmployeeHour","EmployeeID = " & Forms!frmReport!cboEmployee & " and DateWorked between #" & Forms!frmReport!txtStartDate & "# and #" & DateAdd("d",5,Forms!frmReport!txtStartDate) & "#")

OR--instead of DSUM(), if the total of your sub-sums (Sick, Holiday, etc) = the grand total, just name each of those text boxes (txtSick, txtHoliday, etc) and add them together (control source = NZ(txtSick,0) + nz(txtHoliday,0) + etc).

2) I don't see that info contained in any of your tables. If it's in the Employee table, try bringing it down into the query that is the report's recordsource (Row Heading), or try dlookup().

3) Ok--so you already know that the function Weekday() finds the day of the week of a date. Look that function up in HELP. You could add some code to your button, something like this (untested). You can try vbSaturday, or 7 I think (check HELP and/or other posts):

'Verify that entered date is a Saturday;
'If it is, continue
'If it isn't, exit this subroutine

if weekday(me.txtEndDate) <> 7 then
    msgbox "End date should be a Saturday!",vbokOnly,"Invalid Date"
    me.txtEndDate.setfocus
    exit sub
end if

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html