System Resources Exceeded when creating Query in code

[szucker1]

I am building a SQL Selecet Statement in code to create query (Set QueryDef = CurrentDb.CreateQueryDef("qryTemp1", strSQL_Select & strSQL_From & strSQL_Where & strSQL_Group)) which has a column for every month in a time period specified by the user. The time period can be up to 10 years (120 columns). If the time period is around 4 years the query is created just fine, but take it to 5 years and I get "System Resources Exceeded". I have another similiar query creation situation where the length of the SQL Selecet Statement string is not quite as long per length of time period and as a result it does not fail until the time period goes beyond around 6 years. It seems like there is a limit too how many characters (it seems to be failing @ a very rough estimate of 125,000 characters) a SQL Select Statement can be. Is that true? Is there some other way to build this query for the user to view and/or export to excel?

Thanks for any ideas.
Scott

[June7]

Sounds like data structure is not normalized.

There is a limit to how many columns a query can retrieve - same limit as number of fields in table - 255.


http://www.databasedev.co.uk/access_specifications.html

http://office.microsoft.com/en-us/ac...010341462.aspx

[szucker1]

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.

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

[June7]

You have 'monthly' columns. Multiple similar named fields indicates non-normalized data structure. Suggest you research and learn about database normalization.
http://forums.aspfree.com/microsoft-...es-208217.html

Attendance database is notoriously tricky to build. Mostly because users want the input form to look like a timesheet with days across the page. This doesn't really fit with relational database principles. And also why many 'timesheet' models use spreadsheet instead of database app. An Excel spreadsheet is used by my org for employee recording of hours and printing sheet for signature and submittal to payroll.

Review
https://www.accessforums.net/forms/h...orm-29070.html
https://www.accessforums.net/access/...nce-20899.html
https://www.accessforums.net/databas...ses-18459.html

[szucker1]

June7,

Once again thank you for the links. I will go through all of them over the next day or so. The monthly class that the students are attending in my case is an online webinar through GoToWebinar by Citrix and the instructor downloads an excel file after each class which is imported into Access to create the database then based on information on each student from the excel file (Interest Rating, Time in Session, & Poll Questions Answered) it is determined whether attendees qualify for credit and tables updated. Then the queries we previously discussed are generated and sent to administraters. Anyway I will review those links you sent me and hopefully they will lead me to a brilliant idea. (:-))

Scott

[June7]

This is not daily attendance recording? If you want to provide sample worksheet might be able to offer ideas.

[szucker1]

June7,

I assume you mean the excel file the instructor downloads after each webinar (webinars happen one per month). I have attached the excel file all the student names and email addresses changed to generic entries. Let me know if it was the Access mdb you wanted; I can upload if you think it will help.

Scott

[June7]

I was just interested in structure of the downloaded spreadsheet to see how it would translate to a relational database. Offhand, I can see need for 3 tables minimum:

Students
USSF_ID (primary key)
RefereeGrade

Courses
WebinarID (primary key)
CourseName
CourseDate

StudentsCoursesAttendance - record entered only if attended, if need to track registered not attended that is another issue
USSF_ID (foreign key)
CourseID (foreign key)
InterestRating
etc


I can't reconcile the spreadsheet with how you describe database as having 'monthly columns'.

The spreadsheet structure is not setup for easy import - that multi-row header complicates. What method do you use for import?

Guess I will need database if you want more critique.

[szucker1]

June7,

I have attached a second excel file (it is just the first file with relevant dates changed from Aug 2012 to March 2012) so you would have two different months of data. I also have attached my database with both this excel file and the previous one already imported, and I have qualified (not using the same parameters so students are not qualified the same in both months) the two imports (sessions).

A Webinar (identified by its 'Webinar_ID') represents one calendar year and has 12 Sessions (one per month, identified by its 'Start') which can have any number of Students (identified by 'Last_Name', 'First_Name', & 'Email'; sorry not 'USSF' which the student enters, is not unique, or may be incorrect) for a given Session. I have five main data tables: table_Attendence, table_Session, table_Student, table_Webinar, and table_Webinar_Registration.

The database is designed to work as follows. It opens up to the main form form_Soccer_Webinar_Duties. This form has four buttons which will generally be used each month from left to right. The first button is 'Import Soccer Webinar' which will import the excel file (the code behind this button shows how I extract data from the excel file), fill the tables, and will open popup form form_Choose_Student when a student is found that is not in the database so they can be added as a new student or takeover an existing student (i.e. they might register as Bob instead of Robert for this webinar). The second button used would be 'Multi-Viewing' which uses form form_Multi-Viewing. Its purpose is to take care of giving credit to multiple students that may attended the webinar by all viewing on the same computer (usually this will be a parent & their children). The third button is 'Qualification' which calls form_Qualification. This is where the instructor decides which attending students for a given session will be qualified to get credit. Finally the fourth button, which started this whole thread, 'Reports' which calls form_Reports and allows the instructor to create/customize his query/report which generally then would be exported to excel and sent to administrators.

I am sure this is more than you ever wanted to know, but what the heck. You have already been more than helpful so feel free to say you've had enough at any time or even retroactively.

Scott

[June7]

Well, that gives me a completely different impression! There isn't a table with 'monthly columns'. The tables appear to be nicely normalized. However, I find the compound keys somewhat troubling. Assumes students never change name or email. Too bad the USSF field isn't unique because of the students without a USSF ID. Could maybe give them a non-member ID? But then you don't want to rely on it because student might enter it wrong. What if they misspell names? When they login to Webinar they don't have to provide a valid username/password? Seems this is where USSF ID would be useful.

The code for importing the spreadsheet is essentially what I expected would have to be.

Why using unbound forms?

You are building sql strictly within VBA to open a recordset and create a query object for export to Excel. If you want the spreadsheet structure to be the 'monthly columns' then don't think you can do this by setting up recordset/query in that structure, at least not with all monthly columns, not for all months for all years. As you discovered the SQL is too long. Suggest will have to reconfigure code that doesn't create a query object but instead write data direct to Excel sheet in multiple passes. Pass 1 will open recordset with the non-month columns. Then as many subsequent passes as are needed for each month within the selected range. This might run rather slow.

Consider:

Query1
SELECT table_Student.Last_Name, table_Student.First_Name, table_Student.Email, table_Student.USSF, Count(table_Attendence.Start) AS Sessions, Format([Start],"yyyymm") AS YrMo, Count(table_Attendence.Webinar_ID) AS CountAttend, "Yes" AS Attendance
FROM table_Attendence INNER JOIN table_Student ON (table_Attendence.Email = table_Student.Email) AND (table_Attendence.First_Name = table_Student.First_Name) AND (table_Attendence.Last_Name = table_Student.Last_Name)
WHERE (((table_Attendence.Start) Between #3/1/2012# And #8/31/2012#) AND ((table_Attendence.Qualification)=True))
GROUP BY table_Student.Last_Name, table_Student.First_Name, table_Student.Email, table_Student.USSF, Format([Start],"yyyymm"), "Yes";

Query2
TRANSFORM First(Query1.Attendance) AS FirstOfAttendance
SELECT Query1.Last_Name, Query1.First_Name, Query1.Email, Query1.USSF, Query1.Sessions, Query1.Sessions
FROM Query1
GROUP BY Query1.Last_Name, Query1.First_Name, Query1.Email, Query1.USSF, Query1.Sessions, Query1.Sessions
PIVOT Query1.YrMo;

Query2 will not create columns for months that do not have data. Also, still subject to 255 column limit.

[szucker1]

June7,

GoToWebinar (Citrix) uses email addresses to uniquely identify attendees but I cannot because of the multi-viewing aspect I mentioned in my prior post where a parent logs into the webinar session and watches with let say 2 children. Citrix sees this as one attendee (email), but I have to create 3 attendees and that is where my form_Multi-Viewing comes in. Students register (enter names and email addresses) per webinar (generally once a year). If a student changes their name or email that is where my form_Choose_Student comes in and allows the instructor to do some investigating and decide whether they have a new student or an existing one who has just changed (misspelled) their name or email.

You have good idea that will improve my current design significantly I think. What if as I import the excel file I use an autonumber field in table_Student called 'Student_ID' to uniquely identify the students in the database. The instructor can still use the trio of last, first, & email visually on the forms and reports, but behind the scenes I can use Student_ID instead of the 'trio' which when building my query SQL statement I think will reduce the number of characters roughly 40%. This would mean that 'All' report (where the instructor wants to see one of three identifiers (blank for didn't attend, 'No' for atteneded but didn't qualify, 'Yes' for attended and qualified) per student per session (month) could go from about 4.5 years to 7.5 years. I think you are also correct that if I want to give the instructor the ability to look over larger time spans then I will have to export from code directly to excel.

You asked me about using bound forms but I'm not sure what advantage I get with that. Being unbound allows me to make the instuctor have to be a little more proactive to change (or screw up) data.

I was looking at the two queries you suggested but I will have to do some more research because I don't have any experience with 'TRANSFORM' or 'PIVOT' so when I understand what they do within queries maybe I can use them to further reduce the character length of my query SQL statements, as well as, be more efficient.

Again I would like to thank you for your thoughts and suggestions as they are helping me understand Access better and expand my thinking for solving these, as well as, other issues.

Scott

[June7]

That's what autonumber as primary key is intended for.

Query2 is a CROSSTAB. Access has a wizard for that, at least it does from A2003 up.

[ssanfu]

Access 2000 also has the Crosstab wizard.