The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.
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:
My code returns this:
I want it to return this:
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!
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
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.
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.
I've solved it! It did need a nested loop. Instead of one query to bring the total records (5) I did two queries (2 then 3 and 2) within the nested loops as seen below and that did the trick. Thank you.
Set rstTemp = CurrentDb.OpenRecordset("tblTimeSheetDataTemp")
Set rstTime = CurrentDb.OpenRecordset("tblTimeSheetData")
'this next query finds two distinct records John and Helen's EmpID
strSqla = "SELECT DISTINCT tblTimeSheetData.EmpID FROM tblTimeSheetData WHERE [WorkDate] BETWEEN #" & StartDate & "# AND #" & EndDate & "#"
Set rstCts = CurrentDb.OpenRecordset(strSqla, dbOpenDynaset)
Do While Not rstCts.EOF
lngEmpID = rstCts![EmpID]
rstTemp.AddNew
'This next query finds the number of records with John's (or Helen's) EmpIDs between Start and End dates
strSql = "SELECT * FROM tblTimeSheetData WHERE [EmpID] = " & lngEmpID & " AND [WorkDate] BETWEEN #" & StartDate & "# AND #" & EndDate & "#"
Set rstTime = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
Do While Not rstTime.EOF
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
rstTime.MoveNext
Loop
rstTemp.Update
rstCts.MoveNext
Loop
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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