VBA Access Timesheet - Vertical to Horizontal view
I am basically trying to convert vertical data to horizontal (like an excel table) an am almost there. I have to use a loop but the result is not what I want and not sure how to go about it! Here's the code so far which is working but not how I want:
strSQL = "SELECT * FROM tblTimeSheetData WHERE [WorkDate] BETWEEN #" & StartDate & "# AND #" & EndDate & "#" _ & "ORDER BY [EmpID], [WorkDate] ;" Set rstTime = CurrentDb.OpenRecordset(strSQL) Do While Not rstTime.EOF rstTemp.AddNew lngEmpID = rstTime![EmpID] rstTemp![EmpID] = lngEmpID intWeekday = Weekday(rstTime![WorkDate]) Select Case intWeekday Case 2 rstTemp![MondayWorkDate] = rstTime![WorkDate] rstTemp![MondayWorkHours] = rstTime![WorkHours] Case 3 rstTemp![TuesdayWorkDate] = rstTime![WorkDate] rstTemp![TuesdayWorkHours] = rstTime![WorkHours] Case 4 rstTemp![WednesdayWorkDate] = rstTime![WorkDate] rstTemp![WednesdayWorkHours] = rstTime![WorkHours] Case 5 rstTemp![ThursdayWorkDate] = rstTime![WorkDate] rstTemp![ThursdayWorkHours] = rstTime![WorkHours] Case 6 rstTemp![FridayWorkDate] = rstTime![WorkDate] rstTemp![FridayWorkHours] = rstTime![WorkHours] End Select rstTemp.Update rstTime.MoveNext Loop
You should be creating a cross-tab query with the Name as the RowHEader, the Date as the column header, and the hours as a value.
With a WHERE clause that limits the dates to a specific date span.
The down side of this is that it will not be updateable. However, what I have done is display this in a form, and then use the double click event of the control to open a popup which determins the name, and date based on the row and column being clicked. Then the user can edit hours, and when that popup form is closed, I requery my form