June7,
Thank you for the reply and the links. I see from the links that a query SQL Statement is limited to about 64,000 which I am most likely runnig up against because each of those monthly columns is a calculated expression and the query is using Group By so the calculated expression is basically listed twice (once in the Select and once in the Group By). I am not sure what you mean by data structure not normalizeed but maybe you can explain further. What the generated query is doing is looking at an attendence table (where a student is listed each time they attend a monthly class and for each class they attend whether they qualified for credit) and for each student there is a row and for each monthly column it is blank if they did not attend, 'Yes' if they did attend and qualified for credit, or 'No' if they attended but did not qualify for credit. I have listed the monthly calculated expression for the Select Statement below (it is repeated in code for each month by using a month for-next loop (themonth) inside a year for-next loop (lngyear). If you have any ideas how I could simplify it would be greatly appreciated.
Code:
strSQL_Select = strSQL_Select & ", IIf(IsNull(DLookUp(""[Qualification]""," & _
"""table_Attendence"",""Month([Start]) = " & themonth & " " & _
"And Year([Start]) = " & lngyear & " " & _
"And [Last_Name] = """""" & [table_Attendence]![Last_Name] & """""" " & _
"And [First_Name] = """""" & [table_Attendence]![First_Name] & """""" " & _
"And [Email] = """""" & [table_Attendence]![Email] & """""""")),""""," & _
"IIf(DLookUp(""[Qualification]"",""table_Attendence""," & _
"""Month([Start]) = " & themonth & " And Year([Start]) = " & lngyear & " " & _
"And [Last_Name] = """""" & [table_Attendence]![Last_Name] & """""" " & _
"And [First_Name] = """""" & [table_Attendence]![First_Name] & """""" " & _
"And [Email] = """""" & [table_Attendence]![Email] & """""""")=True,""Yes"",""No"")) " & _
"AS " & Format(themonth & "/1", "mmm") & lngyear
Thank you again for those links and any other suggestion,
Scott