I have a form that takes a long time to open and I was investigating possible causes for that. the form displays no data, it just has a few labels and buttons that run queries. the queries themselves ran correctly when clicked in the navigation pane, and via the button on the form, but I suspected one (or more) of the queries was the cause of the problem.
I used a tool called Dependency Checker to analyse what was going on and when I asked it to look at qry_relative_age_00 the tool said that it could not find the macro that was the button instruction to run the query. this is odd, because the button itself worked and the query ran correctly (if a little slowly).
in fact, Dependency Checker itself returned an error and asked me to send an error report to the maintainer.
looking at the query I noted that I had used a non-standard character in an alias so I changed this from [δ-sw] to [d-sw] and saved the query and from that moment on ACCESS went berserk.
when I clicked on the query I got three different error messages (not all at once, each time I clicked I got a different message) I got the following:
1. The number of columns in the two selected tables or queries of a Union query do not match.
2. Query is too complex.
3. Microsoft ACCESS has encountered a problem and is closing.
the main query, the one I am investigating, is not itself a UNION query but it is the end of a long chain of queries and there are two UNION queries in that chain. so I investigated both of them and the number of columns do match. they both run on their own without any problem, and they both feed other queries that also run without any problems.
after spending the best part of a day trying to fiddle with this to get it to work I decided the best thing to do was to delete the query and come back to it. so I deleted it.
this is what it said:
Code:
SELECT qry_natfinalist_9.code, qry_natfinalist_9.event, [wins] & " (" & Format(([wins]/([wins]+[sums]))*100,"Fixed") & "%)" AS win, [sums] & " (" & Format(([sums]/([wins]+[sums]))*100,"Fixed") & "%)" AS [sum], qry_natfinalist_qtr_05.q1n,
qry_natfinalist_qtr_05.q2n, qry_natfinalist_qtr_05.q3n, qry_natfinalist_qtr_05.q4n, Format((Abs([sums]-[wins])/([wins]+[sums]))*100,"Fixed") AS [d-sw], [qry_natfinalist_9].[event] & "\>" & [win] & "\>" & [sum] & "\>" & [d-sw] & "\\" AS [table-1], [qry_natfinalist_9].[event] & "\>" & [q1n] & "\>" & [q2n] & "\>" & [q3n] & "\>" & [q4n] & "\\" AS [table-2], tbl_event.dist
FROM (qry_natfinalist_9 LEFT JOIN tbl_event ON qry_natfinalist_9.event = tbl_event.event) LEFT JOIN qry_natfinalist_qtr_05 ON (qry_natfinalist_9.event = qry_natfinalist_qtr_05.event) AND (qry_natfinalist_9.code = qry_natfinalist_qtr_05.code)
WHERE (((qry_natfinalist_9.code)<>"total"))
ORDER BY qry_natfinalist_9.code, tbl_event.dist;
and here is the first UNION query:
Code:
SELECT qry_natfinalist_6.event, qry_natfinalist_6.code, qry_natfinalist_6.sums, qry_natfinalist_6.wins, qry_natfinalist_6.summ, qry_natfinalist_6.winm, qry_natfinalist_6.solo, qry_natfinalist_6.multi, qry_natfinalist_6.inx
FROM qry_natfinalist_6
UNION ALL
SELECT qry_natfinalist_7.event, qry_natfinalist_7.code, qry_natfinalist_7.sums, qry_natfinalist_7.wins, qry_natfinalist_7.summ, qry_natfinalist_7.winm, qry_natfinalist_7.solo, qry_natfinalist_7.multi, qry_natfinalist_7.inx
FROM qry_natfinalist_7
UNION ALL
SELECT qry_natfinalist_8.event, qry_natfinalist_8.code, qry_natfinalist_8.sums, qry_natfinalist_8.wins, qry_natfinalist_8.summ, qry_natfinalist_8.winm, qry_natfinalist_8.solo, qry_natfinalist_8.multi, qry_natfinalist_8.inx
FROM qry_natfinalist_8;
and the second one:
Code:
SELECT qry_natfinalist_qtr_02.code, qry_natfinalist_qtr_02.event, qry_natfinalist_qtr_02.[1], qry_natfinalist_qtr_02.[2], qry_natfinalist_qtr_02.[3], qry_natfinalist_qtr_02.[4]
FROM qry_natfinalist_qtr_02
UNION ALL
SELECT qry_natfinalist_qtr_03.code, qry_natfinalist_qtr_03.event, qry_natfinalist_qtr_03.[1], qry_natfinalist_qtr_03.[2], qry_natfinalist_qtr_03.[3], qry_natfinalist_qtr_03.[4]
FROM qry_natfinalist_qtr_03;
you can easily confirm that they have the same number of columns, of the same data type.
having deleted the original I waited two days, during which time I did a compact and repair and took a backup each day. this is my usual routine, always do a compact and repair before taking a backup, and I usually do this three or four times a week.
after two days I tried to re-write the query but to make it less complex, perhaps do it in more than one step, so I wrote the following:
Code:
SELECT qry_natfinalist_9.code, qry_natfinalist_9.event, [wins] & " (" & Format(([wins]/([wins]+[sums]))*100,"Fixed") & "%)" AS winter, [sums] & " (" & Format(([sums]/([wins]+[sums]))*100,"Fixed") & "%)" AS [summer], Format((Abs([sums]-[wins])/([wins]+[sums]))*100,"Fixed") AS [d-sw]
FROM qry_natfinalist_9;
when I first wrote this it ran and displayed what looked to be correct data, but when I tried to save it ACCESS went wack-wack oops! on me again and closed the program. after opening up ACCESS (again) I found that it had saved the query, but when I click on it I get the "Query is too complex" error message again.
I think it is obvious that the query itself is not the problem, the query not running is a symptom of whatever is really going on so I am not expecting anyone to analyse this query and say "you missed a comma out of your Format function," or anything along those lines. the sort of answers I am looking or are:
1. what sorts of errors cause ACCESS to just close.
2. how "complex" (whatever that means) does a query have to be for you to get a "Query is too complex." error?
3. why would ACCESS think a UNION query does not have the correct number of columns, when they clearly do?
many thanks,
Cottonshirt