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

Open in new window


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!
andrew gunesberkAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Dale FyeOwner, Developing Solutions LLCCommented:
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

Dale
PatHartmanCommented:
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 gunesberkAuthor Commented:
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.
Big Business Goals? Which KPIs Will Help You

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.

andrew gunesberkAuthor Commented:
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

Open in new window

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
PatHartmanCommented:
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 gunesberkAuthor Commented:
It solves the question.
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.