Idle curiosity, why does it take so long for design view to open for a crosstab?

[Johnny C]

What it says on the tin. I've got a very simple crosstab query, 1 table, no criteria, 5 row headings, 1 column heading, 1 data... the database is 900Mb, not big, and regularly compacted. But it takes 3 mins to open design view, it runs quicker than it takes to open design view.

Why is that?

 

[JHB]

Have you tried to copy the SQL-string to a new query?

 

[Johnny C]

Yes, it makes no difference. The SQL is

TRANSFORM Count([T-TR-Performance].EVIDENCEID) AS CountOfEVIDENCEID
SELECT
     [T-TR-Performance].[T-TRMODULESCHEMEEVENT_STATUS], 
     [T-TR-Performance].Unfunded, 
     [T-TR-Performance].AREA, 
     [T-TR-Performance].SCHEME, 
     [T-TR-Performance].TRAINEEID
FROM [T-TR-Performance]
GROUP BY 
     [T-TR-Performance].[T-TRMODULESCHEMEEVENT_STATUS], 
     [T-TR-Performance].Unfunded, 
     [T-TR-Performance].AREA, 
     [T-TR-Performance].SCHEME, 
     [T-TR-Performance].TRAINEEID
PIVOT 
     (Year([T-TR-Performance]![EVENTDATE])-Year([T-TR-Performance]![StartMonth]))*12+(Month([T-TR-Performance]![EVENTDATE])-Month([T-TR-Performance]![StartMonth]))+1;

Just a numpty crosstab

 

[JHB]

Do you have some sample data, we could try your query on, (in an Excel sheet or database)?

 

[gemma-the-husky]

just check your default printer is a local printer, and not a network printer. rendering stuff for printers can cause speed issues of this sort.

 

[Johnny C]

There's too much data alas, 5m rows or thereabouts. Hence it's not in an Excel s/s. The query works fine, runs in about a minute. Just take 2-3 mins to open or close in design mode.

It could be the printer, it's a big network colour printer that takes an age to print to but I've never printed from Access.

I just wondered if prior to opening in design mode or closing if it was pre-scanning the table to verify that the current query design made sense with the data structure in case the data structure had been modified. I've changed structures before and been unable to open the query except in SQL mode because the query no longer made sense.