Max UNION ALL in single query

[Chrafangau]

I'm having a small problem where Access returns a "Query too complex" error when I try to run a Union query of the form:

SELECT CourseNumber, CourseTitle, [Date], TotalScore, "Two (2) zero visibility dives?" AS [Question], CountZeroVisibility AS [NegativeResponses]
FROM qryScore
UNION ALL
SELECT CourseNumber, CourseTitle, [Date], TotalScore, "One (1) night dive?" AS [Question], CountNightDive AS [NegativeResponses]
FROM qryScore
UNION ALL
...etc

with more than 13 unions. I've managed to work around it, but it's messy and if anyone knows why this is occurring, where the limit is or has a better method, I would be interested in hearing it.

 

[namliam]

1) Dont use "Date" as a column name in a table, its a reserved word in access and can/will cause all kinds of unwanted behaviours later on.

2) I dont see the point for your query's it seems like you have multiple columns you want to count... why not simply do it all in one query... Without knowing details for your query and/or table.... I am guessing something along the lines of:

SELECT CourseNumber, CourseTitle, [Date], TotalScore, 
Iif([CountZeroVisibility], "Two (2) zero visibility dives?",
Iif([NegativeResponses], "One (1) night dive?" , 
Iif(.....
))) AS [Question], ....
FROM qryScore

Thought there is probably a better way to do this in your qryScore

 

[Chrafangau]

I have learned about the date issue in the course of the project, but given the very limited scope of the database, left it in there.

The purpose of the query is to take a table that looks like this:
CourseNumber, CourseTitle, Date, TotalScore, Question1Score, Question2Score, ... , Question103Score
01, Part 1, 04/2013, 0.98, 2, 0, ..., 4
02, Part 2, 04/2013, 0.96, 1, 2, ..., 0

And make it look like:
CourseNumber, CourseTitle, Date, TotalScore, Question, Negative Responses
01, Part 1, 04/2013, 0.98, "Literal question text", Question1Score
01, Part 1, 04/2013, 0.98, "Literal question text", Question2Score
...
01, Part 1, 04/2013, 0.98, "Literal question text", Question103Score
02, Part 2, 04/2013, 0.96, "Literal question text", Question1Score

And so on. It's a mess, and the first 4 columns are repeated many, many times, but it works well for the grouping in the report. Including the literal question text as part of the union query was a happy upside to doing it this way, and formatting them is pretty easy with a CSV file full of questions and some regex.

Basically, if I could get the computer to run the full union query I would be happy.

 

[namliam]

with such denormalized data you are basicaly screwed.... If I am reading you properly, you seem to be importing this from a csv, if so during import I would normalize this data into 2 tables instead of having only the one.

First table would only be: CourseNumber, CourseTitle, Date
Perhaps, maybe include the totalscore as well

Second table : CourseNumber, QuestionNumber, Score

Each table would be filled from a staging table that contains your denormalized CSV data, from which you run a bit of code to execute the 103 different Append queries to your "final" normalized tables.

Alternatively you might consider doing a Cartesian join with a dummy table containing the numbers 1 thru 103 and the literal texts you want... This will duplicate your records 103 times (replicating your 103 unnion all queries) which then needs some IIF'ing or some function to add the proper scores to the different questions.

I will strongly suggest normalizing your data though

 

[Chrafangau]

I would agree with you on the basically screwed front, except that it all already works. I just had to break the union query into ten little bits and append them onto a dummy table one at a time.

The data, at this stage in the process has already been through 2 other queries that filter out the desired results by school and a date range, change the text data (yes, no, strongly disagree, that sort of thing) into weighted scores grouped by CourseNumber (which is not a unique field in the original data. Also CourseTitle and Date are the result of a First() check for said groupings, just done for display purposes on the report.) And the data in acquired by directly exporting from another program (remark office omr) into a dummy table in the frontend database, which is then appended onto the backend. Normalising this database would be a nightmare, and I'm not sure how is would help that much.

Normally I would ignore the mess and say 'Bugger it, it works and I know how it works', but I'm leaving in two weeks and I would like a solution that is a touch less difficult for whoever follows me to edit.

 

[namliam]

Well if normalizing the data isnt really an option (anymore) ... that leaves the alternative to go with... Though Normalizing data is something that is always a good thing, certainly when you start thinking about maintainablity and is something that should be on your top 2 things to do when you start a project like this... (IMHO)

For the alternative I definatly wouldnt work with all them union queries but rather use a Cartesian product....
Create a table, lets save it as tblQuestions with columns QID | Question
values:
1 Two (2) zero visibility dives?
2 One (1) night dive?

now make a cartesian query like so, called qryQuestions:

SELECT qryScore.ID
     , qryScore.course
     , qryScore.Question1
     , qryScore.Question2
     , qryScore.Question3
     , tblQuestions.QID
     , tblQuestions.Question
     , IIf([QID]=1,[Question1]
      ,IIf([Qid]=2,[Question2]
      ,IIf([Qid]=3,[Question3]
      ,IIF([Qid]=..., ....
          ,"Error")))) AS NegValue1
     , Choose([QID],[Question1]
                   ,[Question2]
                   ,[Question3]
                   , ...) AS NegValue2
FROM qryScore, tblQuestions;

NegValue1 and NegValue2 are two different ways of achieving the same solution.
The advantage is that anyone can go into the tblQuestions and add, change, delete questions if they want... If you "want perfection", you will need some code to build the cartesian query to reduce the "labor" involved of adding and removing columns manually....

I felt like a challange this morning

Sub makeQuery()
    Dim qry As Object
    Dim sql As String, sql1 As String, sql2 As String
    Dim rs As DAO.Recordset
    Set qry = CurrentDb.QueryDefs("qryQuestions")
    Set rs = CurrentDb.OpenRecordset("tblQuestions")
    sql = "SELECT qryScore.ID, qryScore.course, qryScore.Question1, qryScore.Question2, qryScore.Question3, tblQuestions.QID, tblQuestions.Question, "
    sql2 = "IIf([QID]=1,[Question1],IIf([Qid]=2,[Question2],IIf([Qid]=3,[Question3],""Error""))) AS NegValue1, Choose([QID],[Question1],[Question2],[Question3]) AS NegValue2 FROM qryScore, tblQuestions"
    sql1 = ""
    sql2 = ""
    sql3 = "Choose([QID],"
    Do While Not rs.EOF
        sql1 = sql1 & "Iif([QID]=" & rs!Qid & ",[Question" & rs!Qid & "],"
        sql2 = sql2 & ")"
        sql3 = sql3 & "[Question" & rs!Qid & "],"
        rs.MoveNext
    Loop
    sql1 = sql1 & """Error""" & sql2 & " as NegValue1,"
    sql3 = sql3 & """End"" ) as NegValue2"
    ' Debug.Print sql & sql1 & sql3 & " FROM qryScore, tblQuestions;"
    qry.sql = sql & sql1 & sql3 & " FROM qryScore, tblQuestions;"
    rs.Close
    Set qry = Nothing
    Set rs = Nothing
End Sub

that should build your query for you, only "challange" left is what happens if you get question 110 and 12013 in the number of columns in your table.

 

[Chrafangau]

Thanks for the response, I'll look forward to trying this out come Monday.

 

[Chrafangau]

Out of curiousity...what happens at 110 and 12013?

 

[Chrafangau]

Sadly, both methods presented above have given me 'Expression too complex' results when used for more than ~10 fields. Access just does not seem to want to handle these queries.

 

[namliam]

You could use my code and re-write it a little to create your union queries for you or.... still go back to "proper" normalized design which should be the best solution IMHO.

Using my code to run the needed append queries so you can utilize the true power of database.

Failing that you can also re-write the query to "append" each single query (inside your unions) to your dummy table. Building the Append queries again from the tblQuestions so that it is easy for any user to append / change data accoordingly.

 

[Chrafangau]

I have used your code as the basis for writing a module that generates my union queries, and it works really well, so thanks heaps for setting the groundwork for that. As for normalisation....I'm honestly not sure how to normalise this mess. The only thing I could take out might be the date, course title and school, everything else is unique to every record. I'll think on it some more. I have encountered an odd problem with qry.sql however. For some reason, when I try to use it to set the code for a different query (I'm rewriting much of my database with this code, thanks) it fails to act, despite not giving any errors and all of the variables filled correctly (stepped through and checked).
Is there a character limit for this function that you know of? Or some other limitation?

 

[namliam]

Well if you are running multiple append queries no need to actually write the SQL to actual queries.

You can use either
1)
Docmd.setwarnings false
docmd.runsql "Append query"
docmd.setwarnings true
2)
currentdb.execute "append query"

Running queries this way is much more "best practice" instead of using actual query objects inside the database... Code after is code, while objects in the database are not "really" part of that code... Since you are already writing the SQL it should be easy to addapt to do it this way.
-- Just for kicks, I am curious to see what your final code will look like --

As for saving the query, in order for the updated SQL to be commited to the actual query object you may have to close/set = nothing the Object, not sure about how to get it "saved" though.

As for normalizing the data, I am primarily refering to the different questions and their answers. A structure of ID-Course-Q1-Q2-Q3-etc is not normalized and will cause these issues you are experiencing now.

ID-Q1     -Q2      -Q3    -Q4       -Q5
 1-Good   -Great   -Better-Fantastic-Horrible
 2-Average-mediocre- etc...

Instead it should be ID-QNumber-Answer
1-1-Good
1-2-Great
1-3-Better
1-4-Fantastic
1-5-Horrible
2-1-Average
2-2-mediocre
etc...

This second structure is basicaly what you are (trying to) achieve with your Union/append queries

 

[Chrafangau]

I'll see if I can implement that suggestion re. the appends, it makes sense. And you're right about the normalisation, though I dont think there's any way to do that with my current method of exporting other than manually, so given my time constraints I think I'll have to let it slide for now. I'll try the close/set=nothing method when I get in tomorrow, and if that fails I'll double check I dont have any error handling suppressing the message.
Thanks again for all the advice.

 

[Chrafangau]

Never mind, the issue with writing to the queries was between the chair and the keyboard. Just a small SQL syntax problem that wasn't coming up due to error suppressing.