Need help getting values from a transform query 1

[DougP]

I need to get the Day(s) of the Week out and store in a separate table. The DayOfWeek field is the pivot field so it is not returned in the recordset.
here is my code so far.

    ' get project number totals by day
    Dim Conn2 As ADODB.Connection
    Dim Rs1, Rs2 As ADODB.Recordset
    Dim SQLCode, SQLCode2 As String
    Set Conn2 = CurrentProject.Connection
    Set Rs1 = New ADODB.Recordset
    Set Rs2 = New ADODB.Recordset
    
    ' clear from before
    SQLCode2 = "Delete From QBTotals;"
    Rs2.Open SQLCode2, Conn2, adOpenStatic, adLockOptimistic
    
    SQLCode2 = "Select * from QBTotals;"
    SQLCode = "TRANSFORM Sum([TimeSheet Details].Hours) AS SumOfHours " & _
               "SELECT [TimeSheet Details].[Employee Name], [TimeSheet Details].[Project Number], [TimeSheet Details].Service, " & _
               "[TimeSheet Details].Number, [TimeSheet Details].Description, [TimeSheet Details].TypeOfHours " & _
               "FROM [TimeSheet Details] " & _
               "WHERE ((([TimeSheet Details].WeekEndDate) = #" & Me![cboChooseWeekEndDate] & "#)) " & _
               "GROUP BY [TimeSheet Details].[Employee Name], [TimeSheet Details].[Project Number], [TimeSheet Details].Service, " & _
               "[TimeSheet Details].Number, [TimeSheet Details].Description, [TimeSheet Details].TypeOfHours, [TimeSheet Details].WeekEndDate " & _
               "PIVOT [TimeSheet Details].DayOfWeek;"

    Rs1.Open SQLCode, Conn2, adOpenStatic, adLockOptimistic
    Rs2.Open SQLCode2, Conn2, adOpenStatic, adLockOptimistic
    
    TotRecs = Rs1.RecordCount
    
    For a = 1 To TotRecs
        Rs2.AddNew
            Rs2![Employee Name] = Rs1![Employee Name]
            Rs2![Project Number] = Rs1![Project Number]
            Rs2![Service] = Rs1![Service]
            Rs2![Number] = Rs1![Number]
            Rs2![TypeOfHours] = Rs1![TypeOfHours]
            Rs2![Description] = Rs1![Description]
    
        Select Case Rs1!DayOfWeek
            Case "Mon"
                Rs2!Mon = Rs1!SumOfHours
            Case "Tues"
                Rs2!Tues = Rs1!SumOfHours
            Case "Wed"
                Rs2!Wed = Rs1!SumOfHours
            Case "Thur"
                Rs2!Thur = Rs1!SumOfHours
            Case "Fri"
                Rs2!Fri = Rs1!SumOfHours
            Case "Sat"
                Rs2!Sat = Rs1!SumOfHours
            Case "Sun"
                Rs2!Sun = Rs1!SumOfHours
            Case Else
                MsgBox "Bad Day"
        End Select
        
        Rs2.Update
        Rs1.MoveNext
    Next

    Set Rs1 = Nothing
    Set Conn2 = Nothing

Any ideas?

TIA


DougP

 

[SoCalAccessPro]

Sure.

Open the query definition using the Qdef object in DAO. Loop through the the feild names until you get past the "fixed" field names (row headings and column headings).

For example, if you have 5 "fixed" fields:

Set qdef = yourquery
i = qdef.fields.count
For y = i-5 to 1
debug.pring qdef.fields(i).name
Next i








Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[DougP]

Thank you

I did that and here is my new code.
It replaces all the Select case stuff

            Rs2!Mon = Rs1!Mon
            Rs2!Tues = Rs1!Tues
            Rs2!Wed = Rs1!Wed
            Rs2!Thur = Rs1!Thur
            Rs2!Fri = Rs1!Fri
            Rs2!Sat = Rs1!Sat
            Rs2!Sun = Rs1!Sun\

Then if someone did not work a particular Day or Days the field did not exist so I had to add a Error trap.

...        
Select Case Err.Number
        Case 3265
            ' field is not in recordset
            ' this occured is someone did not work a particular day
            Resume Next
        Case Else
            MsgBox "Error # " & Err.Number & "  " & Err.Description, vbInformation, "In sub "
            
    End Select

DougP

 

[SoCalAccessPro]

Have you considered normizing the table ?

Instead of having field names named Mon-Fri, have a day ID instead field. This is more consistent with proper database design.

That would eliminate the need for that particular error trap.


Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[SoCalAccessPro]

Edit:

Normalizing

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[dhookom]

You can assure the column creation of the crosstab by adding to your code:

 "PIVOT [TimeSheet Details].DayOfWeek " & _
 "IN ('Sun','Mon','Tue','Wed','Thu','Fri','Sat');"

Duane
Hook'D on Access
MS Access MVP

 

[DougP]

Well dhookom, That’s Great!
Plus I am not getting zeros in every row/record if a Day is not in the record set eliminating the need for further code in the report.

Thanks!!!!


DougP