I'm using a transform Pivot SQL from within Access VBA to create the data I need to build a report in Excel. This report will be used by the user. the data comes from committees held across a year, and the various attendees. I don't want large gaps with all the dates being across the transform, so I run it for each committee, create/write the details to the Excel output file, and then run it again for the next committee. I could do it in a query and produce a report in native Access for each committee, but this would entail the user having to put it together and this, I know, won't be done. (There are 36 committees, with up to 50 memberships in each committee).
But when I create the transform Pivot query, it works perfectly, but how to I access the headings that have the actual meeting dates in them, within the VBA?
If I run as just an SQL the Result has headings
ABR, ID, MemberName, TotalAtt, TotalMeet,yyyy/mm/dd, yyyy/mm/dd of all the meetings repeating.
It's THOSE dates that I want to get hold of to write as headings when populating my excel.
sql2 = "TRANSFORM Sum(MeetingAttendance.Attend) AS SumOfAttend " & _
" SELECT MeetingAttendance.ABR, MeetingAttendance.MemCat, [Surname] & """ & "," & """ & [Title] & """ & " " & """ & [Initials] AS MemName, Sum(([Attend]*-1)) AS TotalAtt, Count(MeetingAttendance.MeetingDate) AS TotalMeet " & _
" FROM CommitteeMemberNames INNER JOIN MeetingAttendance ON CommitteeMemberNames.ID = MeetingAttendance.ID " & _
" WHERE MeetingAttendance.ABR = """ & pdb!ABR & """" & _
" GROUP BY MeetingAttendance.ABR, MeetingAttendance.Memcat,MeetingAttendance.sortcode, MeetingAttendance.ID, [Surname] & """ & "," & """ & [Title] & """ & " " & """ & [Initials] " & _
" PIVOT MeetingAttendance.MeetingDate ;"
'MsgBox "now were about to go to the 2nd do while"
Set Atd = CurrentDb.OpenRecordset(sql2)