multiple errors on query on slow-loading form

**Cottonshirt** · 12-23-2021, 02:55 AM

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

**June7** · 12-23-2021, 03:04 AM

Would have to analyze data and examine queries in design. Suggest providing db for analysis.

Should alias name be "winner" instead of "winter"?

**Minty** · 12-23-2021, 03:19 AM

The fact that you are basing these queries on other queries (e.g. qry_natfinalist_9) and then having to union the results may well indicate that they are not designed in the best way.
What is the sql for qry_natfinalist_9 ?

**Cottonshirt** · 12-23-2021, 05:27 AM

June7 said: Should alias name be "winner" instead of "winter"?

no. the analysis divides athletes between those born in summer vs winter

Minty said: What is the sql for qry_natfinalist_9 ?

qry_natfinalist_9 is the first UNION query given above, which, for convenience, I repeat here:

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;

many thanks,

Cottonshirt

**CJ_London** · 12-23-2021, 07:36 AM

1. ones that mess with the memory in a way access cannot recover from
2. query too complex error usually means a problem with the data - for example a null where a number is expected or a division by zero, so I would be looking at these sorts of fields [sums]-[wins])/([wins]+[sums])
3. not clear what query relates to what, you've provided sql, but not given it names. I assumed you have a chain of queries starting with qry_natfinalist_qtr_02 and numbered up to qry_natfinalist_9 but suspect that is not the case

other comments:

naming fields as just a number is not a good idea
as minty says, use of union queries implies non normalised tables - and is probably one of the reasons for poor performance since the use of indexing will be negated.

**Cottonshirt** · 12-23-2021, 10:28 AM

Ajax said I assumed you have a chain of queries starting with qry_natfinalist_qtr_02 and numbered up to qry_natfinalist_9 but suspect that is not the case

it is actually two chains of queries. qry_natfinalist_0 to qry_natfinalist_8 calculates one set of figures with appropriate sub-totals that are brought together in the UNION query qry_natfinalist_9. then qry_natfinalist_qtr_01 to qry_natfinalist_qtr_04 calculates another set of figures with appropriate sub-totals that are brought together in the UNION query qry_natfinalist_qtr_05. then the big query that caused all the problems brings data from the two UNION queries together into a nice looking format that I can load into my document creation software where I write the paper that is the point of all this analysis.

Ajax said query too complex error usually means a problem with the data - for example a null where a number is expected or a division by zero...

this turns out to be very good advice. thank you. it turns out that in some countries, folks on databases just don't get born in every quarter of the year, and if there is nothing to add up, there isn't a number. spooky, but true.

I had to insert another query into the chain to make sure that data going into the UNION query was neither Null nor zero, and the whole problem disappeared.

thank you very much indeed.

Ajax said as minty says, use of union queries implies non normalised tables ...

I find this very difficult to believe, and I find UNION queries a very useful addition to the toolbox. I mainly use them for calculating sub-totals. in this current example, I have one query that counts the number of folks born in various parts of the year, per event. and then I have a second query count the total number of folk born in that part of the year, per country, and the UNION query allows me to list the sub-totals and totals together, for dozens of countries all at once. this is very useful and I'm afraid I cannot see how any of this implies that my data is not normalised.

it might be that there is some other way to achieve the same result, and if that is the case I am more than willing to learn something new, but I have not found any other way of doing it.

tables don't come out very well in this forum, but here's a short snippet of the sort of thing I mean, but you have to imagine this going on for 350 rows with data for five dozen countries.

code event winter summer qtr-1 qtr-2 qtr-3 qtr-4
HUN 400m 41 (46.59%) 47 (53.41%) 18 23 26 21
HUN 800m 48 (49.48%) 49 (50.52%) 22 26 27 22
HUN 1500m 67 (52.76%) 60 (47.24%) 36 31 25 35
HUN 5000m 72 (52.94%) 64 (47.06%) 38 34 26 38
HUN 10,000m 63 (52.94%) 56 (47.06%) 33 30 27 29
HUN total 291 (51.32%) 276 (48.68%) 147 144 131 145

again, many thanks for your help in solving my problem, greatly appreciated.

best wishes,

Cottonshirt

**Welshgasman** · 12-23-2021, 11:31 AM

tables don't come out very well in this forum,

They do if you post within tags and do not edit afterwards, so a;ways post them last

Code:

code     event     winter         summer         qtr-1     qtr-2     qtr-3     qtr-4
HUN     400m     41 (46.59%)     47 (53.41%)     18     23     26     21
HUN     800m     48 (49.48%)     49 (50.52%)     22     26     27     22
HUN     1500m     67 (52.76%)     60 (47.24%)     36     31     25     35
HUN     5000m     72 (52.94%)     64 (47.06%)     38     34     26     38
HUN     10,000m 63 (52.94%)     56 (47.06%)     33     30     27     29
HUN     total     291 (51.32%)     276 (48.68%)     147     144     131     145

Well the site still cocked them up on Post

, but at least you get a better idea?

**CJ_London** · 12-23-2021, 11:52 AM

I did say implies and they do have their uses on occasion, but usually to do with combining data from different tables (for example a list of customers and suppliers). I can see why you might want subtotals, although these are easily handled in reports. Would need to see example raw data and an example of the final outcome required to comment further. But would think

I have one query that counts the number of folks born in various parts of the year, per event. and then I have a second query count the total number of folk born in that part of the year, per country

could be done with one aggregate query if you want the results in one row and your data was something like this

tblRunners
RunnerPK
RunnerName
RunnerDOB
RunnerCountry

tblEvents
EventPK
EventName
EventDate

tblRunnerEvents
RunnerEventPK
RunnerFK
EventFK

query would be something like this (don't know how you are determining winter/summer - looks like it is summing Q1 & Q2 for winter, or whether the requirements are for events this year, last year, whatever

Code:

SELECT
RunnerCountry,
EventName, 
count(runnercountry) as runnerspercountry,
abs(sum(datepart("q",runnerDOB) in (1,2))) as runnersperWinter,
"(" & format(abs(sum(datepart("q",runnerDOB) in (1,2)))/count(runnercountry),"percent") & ")" as WinterPC,
abs(sum(datepart("q",runnerDOB) in (3,4))) as runnersperSummer,
"(" & format(abs(sum(datepart("q",runnerDOB) in (3,4)))/count(runnercountry),"percent") & ")" as SummerPC,
abs(sum(datepart("q",runnerDOB)=1)) as runnersperQ1,
abs(sum(datepart("q",runnerDOB)=2)) as runnersperQ2,
abs(sum(datepart("q",runnerDOB)=3)) as runnersperQ3,
abs(sum(datepart("q",runnerDOB)=4)) as runnersperQ4
FROM
(tblRunnerEvents INNER JOIN tblRunners ON tblRunnerEvents.RunnerFK=tblRunners.RunnerPK) INNER JOIN tblEvents ON tblRunnerEvents.EventFK=tblEvents.EventPK
GROUP BY 
RunnerCountry,
EventName

**Cottonshirt** · 12-23-2021, 01:41 PM

Ajax said: I can see why you might want subtotals, although these are easily handled in reports.

I can't get the output from a report into my document writing software. reports are fine if you want to view it on screen, or print it on paper at your location, but for digital output for moving into other software you just can't beat a query in tablesheet view.

your tables are not quite right.

tbl_athlete
athleteID
athletename
dateofbirth

country is not a property of athlete, it is a property of race.

tbl_event
eventID
eventname
eventdistance

this lists race distances, 400m, 800m, and so on...

tbl_race
raceID
athleteID
eventID
countrycode
racedate

this lists the actual races. Bob Smith of Scotland ran 5000m at Berlin on 5 Oct 1986 and so forth...

winter is defined as the first six months of the school year in each country. so winter in Australia is not winter in Poland, is not winter in Sweden, and so on. for this reason the DatePart() function is of no use to me. to keep track of this I have a table of countries, where a field [school] is the number of the month in which the school year starts in that country.

tbl_code
codeID
countrycode
countryname
school

then another table:

tbl_quarter
school
birthmonth
quarter

keeps track of which quarter each month falls in depending on when the school year starts in your country. if your school year starts in May then November is in Q3, and so on...

so, from the country code you get the start of the school year, from tbl_athlete you get month of birth, and from tbl_quarter you get the quarter in which he was born. Q1 and Q2 are winter, Q3 and Q4 are summer.

this gives you the following output:

[countrycode], [event], [quarter], from which you determine summer/winter which we will call [season]

then you SELECT [countrycode], [event], [quarter], [season]

and however you group them you can only get a total for a combination of [countrycode] and [event], which is what you're getting in your sample query. so you are getting a combination of country and event, and your output will look like this:

POL 400m 87
POL 800m 65
POL 1500m 124
POL 5000m 63
POL 10,000m 82

but you don't have a total for Poland overall, because you can't SUM just the country when GROUPing by [event].

if you also want a total for each country, you have to SELECT [countrycode], [quarter], [season] and sum them, replacing [event] with the word "total", in a separate query.

then when you join these two together in the UNION query you get the table I posted earlier, in which the "total" row is the total for each country, not the total for the whole query.

thanks for engaging,

Cottonshirt

**June7** · 12-23-2021, 02:05 PM

Use Advanced editor to build a data table:

code	event	winter	summer	qtr-1	qtr-2	qtr-3	qtr-4
HUN	400m	41	47	18	23	26	21
HUN	800m	48	49	22	26	27	22
HUN	1500m	67	60	36	31	25	35
HUN	5000m	72	64	38	34	26	38
HUN	10,000m	63	56	33	30	27	29
HUN	Total	291	276	147	144	131	145

Or simply copy/paste records from Access table:

TeamID	TeamName	Division	PlayLevel	Manager
1	abc	BB	Junior	a
2	def	SB	Junior	b
3	ghi	BB	Major	c
4	jkl	SB	Major	d
5	mno	BB	Minor	e
6	pqr	SB	Minor	f
7	stu	BB	MinorA	g
8	vwx	SB	MinorA	h

Then switch to Advanced editor if you want to modify table.

**CJ_London** · 12-23-2021, 07:06 PM

but you don't have a total for Poland overall, because you can't SUM just the country when GROUPing by [event].

if you can't do summing in whatever your reporting software is I agree you would need a union query based on the same query - but only one, you seem to have many

Assuming the query I provided was called qry1

Code:

SELECT  * FROM qry1
UNION
SELECTRunnerCountry,
"Total", 
sum(runnerspercountry),
sum(runnersperWinter),
"(" & format(sum(runnersperWinter)/sum(runnerspercountry),"percent") & ")",
sum(runnersperSummer),
"(" & format(sum(runnersperSummer)/sum(runnerspercountry),"percent") & ")"
sum(runnersperQ1),
sum(runnersperQ2),
sum(runnersperQ3),
sum(runnersperQ4)
FROM
qry1
GROUP BY 
RunnerCountry, "Total"

with regards winter/summer/different countries, I leave you to work out, I'm simply trying to demonstrate that from what you have provided , you don't need so many queries.

Good luck with your project, but don't think I can suggest anything else to your comment I am more than willing to learn something new

**Cottonshirt** · 12-24-2021, 11:46 PM

Ajax said: I'm simply trying to demonstrate that from what you have provided, you don't need so many queries.

when I first read your post I thought you were still trying to convince me that I should not be using UNION queries, so I saw your proposed query in that light, and it just didn't seem to make your point all that well. if you had not been kind enough to post your follow up comment I would probably not have revisited it, but I did, and I did learn something.

I had not realised that you could use In() in that way, or that critieria in general were allowed within an individual aggregate function. previously, if I wanted to know how many of a thing there were, I would first isolate the thing, then count them. this frequently requires the use of a crosstab query to get a column of vertical data into category columns to be counted.

your very helpful tip now means that, as long as the categories are a priori identifiable, the crosstab approach is no longer necessary. this has the potential to do away with loads of unneccessary queries and has already been applied.

the two chains of queries that were the subject of this thread involved 17 queries with two crosstabs and two UNIONs. using this new approach I have reduced that to 7 queries with no crosstabs and one UNION.

this has also considerably simplified the elimination of nulls and zeroes and will no doubt lead to further benefits as I explore its potential in the coming months.

this has definitely been my most valuable Christmas present for several years and I don't know how to thank you enough,

best wishes,

Cottonshirt