asked on
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:
My select query returns exactly what I want:
![TNgef1.jpg]()
My code returns this:
![Wfxe8.jpg]()
I want it to return this:
![vlmt2.jpg]()
Thank you so much for your help!
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
My select query returns exactly what I want:
My code returns this:
I want it to return this:
Thank you so much for your help!
Microsoft AccessVBA
Last Comment
andrew gunesberk
As Dale said, the crosstab is a simple, no code solution but there are a couple of things to consider. If you want your columns to sort correctly, you have to format the date to accomodate that. The only format that absolutely works is yyyy-mm-dd. That will handle weeks that cross months or years. If all you want is 7 weekdays, then, you need to ensure your criteria selects only one week. The best way to do that is to have the user enter either the week starting or week ending date and you add or subtract 7 to find the other end date. The crosstab has an option to group by day but you might need to add a column headings property to get the days to sort in the order you want. So, if your week starts on Sunday, then
Sun, Mon, Tue, Wed, Thu, Fri, Sat
will do it. Othwerwise, adjust to suit your schedule.
Sun, Mon, Tue, Wed, Thu, Fri, Sat
will do it. Othwerwise, adjust to suit your schedule.
ASKER
Dale, Pat; thanks for the feedback. Yes i had created a crosstab query and it does return (not in VBA!) what i need however I could not place that information in that format onto the timesheet subform! That's where the problem is. The query in the code above returns the information I am looking for as well but in vertical form and I have to place that onto the subform in horizontal as shown in the photos. Pat as you can see in the code I have no issues with the dates etc.; when the user picks the weekending date in the timesheet form, it automatically creates the fields with dates from Mon to Fri. However at that point it has to go check to see the database if there are any records already in the table for those dates: this is the part of code I'm trying to accomplish. I have the other part working, where if there are no records for that week, the user can enter all the info and can save to the table converting from horizontal to vertical. So another possibility could be, to assign the first EmpID to a variable in the query results up above then DCount to see how many times it appears (in this example: John 3 times) and somehow use that in a nested loop. Thanks again.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Limiting yourself to 5 weekdays is shortsighted. What will you do if someone needs to work on a Saturday?
Please close the question if you have your answer.
Please close the question if you have your answer.
ASKER
It solves the question.
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
Dale