Avatar of andrew gunesberk
andrew gunesberk
 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:

strSQL = "SELECT * FROM tblTimeSheetData WHERE [WorkDate] BETWEEN #" & 
StartDate & "# AND #" & EndDate & "#" _ 
  & "ORDER BY [EmpID], [WorkDate] ;"    

Set rstTime = CurrentDb.OpenRecordset(strSQL)   

Do While Not rstTime.EOF    
    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


Open in new window

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

Avatar of undefined
Last Comment
andrew gunesberk

8/22/2022 - Mon
Dale Fye

What is your intent with this query?

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


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.
andrew gunesberk

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
andrew gunesberk

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.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

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.
andrew gunesberk

It solves the question.