readability and crash when saving query...

[easyit]

Hi,

I've nested some queries :

SELECT AliasDetails.srt, AliasDetails.TeamNaam, AliasDetails.soort, Sum(AliasDetails.Totaal) AS Totaal, Sum(AliasDetails.[0-5]) AS [0-5], Sum(AliasDetails.[6–10]) AS 6–10, Sum(AliasDetails.[11–20]) AS 11–20, Sum(AliasDetails.[21-40]) AS [21-40], Sum(AliasDetails.[41-60]) AS [41-60], Sum(AliasDetails.[60+]) AS [60+], AliasDetails.Week
FROM (SELECT 1 AS srt, E25T_tbl_rap_tellingen_historie.TeamNaam, t_soort.soort, Sum(E25T_tbl_rap_tellingen_historie.Aantal) AS Totaal, Sum(IIf([aantaldagen]<=5,[E25T_tbl_rap_tellingen_historie]![Aantal])) AS [0-5], Sum(IIf(([aantaldagen]<=10) And ([aantaldagen]>=6),[E25T_tbl_rap_tellingen_historie]![Aantal])) AS 6–10, Sum(IIf(([aantaldagen]<=20) And ([aantaldagen]>=11),[E25T_tbl_rap_tellingen_historie]![Aantal])) AS 11–20, Sum(IIf(([aantaldagen]<=40) And ([aantaldagen]>=21),[E25T_tbl_rap_tellingen_historie]![Aantal])) AS [21-40], Sum(IIf(([aantaldagen]<=60) And ([aantaldagen]>=41),[E25T_tbl_rap_tellingen_historie]![Aantal])) AS [41-60], Sum(IIf(([aantaldagen]>=61),[E25T_tbl_rap_tellingen_historie]![Aantal])) AS [60+], Format([DatumInLijst],"ww",2,3) AS Week
FROM t_soort INNER JOIN E25T_tbl_rap_tellingen_historie ON t_soort.key = E25T_tbl_rap_tellingen_historie.soort
WHERE (((Format([DatumInLijst],"yyyy"))=Year(Date())))
GROUP BY 1, E25T_tbl_rap_tellingen_historie.TeamNaam, t_soort.soort, Format([DatumInLijst],"ww",2,3)
HAVING (((Format([DatumInLijst],"ww",2,3))=Format(Date(),"ww",2,3)-1))
UNION
SELECT 1 AS Srt, [E25T_tbl_items_openstaand].[TeamNaam], [t_soort].[soort], Count([E25T_tbl_items_openstaand].[AfrdocNr]) AS Totaal, Count(IIf([aantaldagen]<=5,[E25T_tbl_items_openstaand]![AfrdocNr])) AS [0-5], Count(IIf(([aantaldagen]<=10) And ([aantaldagen]>=6),[E25T_tbl_items_openstaand]![AfrdocNr])) AS 6–10, Count(IIf(([aantaldagen]<=20) And ([aantaldagen]>=11),[E25T_tbl_items_openstaand]![AfrdocNr])) AS 11–20, Count(IIf(([aantaldagen]<=40) And ([aantaldagen]>=21),[E25T_tbl_items_openstaand]![AfrdocNr])) AS [21-40], Count(IIf(([aantaldagen]<=60) And ([aantaldagen]>=41),[E25T_tbl_items_openstaand]![AfrdocNr])) AS [41-60], Count(IIf(([aantaldagen]>=61),[E25T_tbl_items_openstaand]![AfrdocNr])) AS [60+], Format([DatumInLijst],"ww",2,3) AS Week
FROM t_soort INNER JOIN E25T_tbl_items_openstaand ON [t_soort].[key]=[E25T_tbl_items_openstaand].[soort]
WHERE (((Format([DatumInLijst],"yyyy"))=Year(Date())))
GROUP BY 1, [E25T_tbl_items_openstaand].[TeamNaam], [t_soort].[soort], Format([DatumInLijst],"ww",2,3)
HAVING (((Format([DatumInLijst],"ww",2,3))=Format(Date(),"ww",2,3)-1)) ) AS AliasDetails
GROUP BY AliasDetails.srt, AliasDetails.TeamNaam, AliasDetails.soort, AliasDetails.Week
HAVING (((AliasDetails.TeamNaam) Is Not Null))
UNION
SELECT 1 AS Srt, [E25T_tbl_rap_tellingen_historie]![TeamNaam] & " Totaal" AS TeamNaam, "-" AS soort, Sum(AliasTeamTotaal.Totaal) AS Totaal, Sum(AliasTeamTotaal.[0-5]) AS [0-5], Sum(AliasTeamTotaal.[6–10]) AS 6–10, Sum(AliasTeamTotaal.[11–20]) AS 11–20, Sum(AliasTeamTotaal.[21-40]) AS [21-40], Sum(AliasTeamTotaal.[41-60]) AS [41-60], Sum(AliasTeamTotaal.[60+]) AS [60+], AliasTeamTotaal.Week
FROM (SELECT 1 AS srt, E25T_tbl_rap_tellingen_historie.TeamNaam, t_soort.soort, Sum(E25T_tbl_rap_tellingen_historie.Aantal) AS Totaal, Sum(IIf([aantaldagen]<=5,[E25T_tbl_rap_tellingen_historie]![Aantal])) AS [0-5], Sum(IIf(([aantaldagen]<=10) And ([aantaldagen]>=6),[E25T_tbl_rap_tellingen_historie]![Aantal])) AS 6–10, Sum(IIf(([aantaldagen]<=20) And ([aantaldagen]>=11),[E25T_tbl_rap_tellingen_historie]![Aantal])) AS 11–20, Sum(IIf(([aantaldagen]<=40) And ([aantaldagen]>=21),[E25T_tbl_rap_tellingen_historie]![Aantal])) AS [21-40], Sum(IIf(([aantaldagen]<=60) And ([aantaldagen]>=41),[E25T_tbl_rap_tellingen_historie]![Aantal])) AS [41-60], Sum(IIf(([aantaldagen]>=61),[E25T_tbl_rap_tellingen_historie]![Aantal])) AS [60+], Format([DatumInLijst],"ww",2,3) AS Week
FROM t_soort INNER JOIN E25T_tbl_rap_tellingen_historie ON t_soort.key = E25T_tbl_rap_tellingen_historie.soort
WHERE (((Format([DatumInLijst],"yyyy"))=Year(Date())))
GROUP BY 1, E25T_tbl_rap_tellingen_historie.TeamNaam, t_soort.soort, Format([DatumInLijst],"ww",2,3)
HAVING (((Format([DatumInLijst],"ww",2,3))=Format(Date(),"ww",2,3)-1))
UNION
SELECT 1 AS Srt, [E25T_tbl_items_openstaand].[TeamNaam], [t_soort].[soort], Count([E25T_tbl_items_openstaand].[AfrdocNr]) AS Totaal, Count(IIf([aantaldagen]<=5,[E25T_tbl_items_openstaand]![AfrdocNr])) AS [0-5], Count(IIf(([aantaldagen]<=10) And ([aantaldagen]>=6),[E25T_tbl_items_openstaand]![AfrdocNr])) AS 6–10, Count(IIf(([aantaldagen]<=20) And ([aantaldagen]>=11),[E25T_tbl_items_openstaand]![AfrdocNr])) AS 11–20, Count(IIf(([aantaldagen]<=40) And ([aantaldagen]>=21),[E25T_tbl_items_openstaand]![AfrdocNr])) AS [21-40], Count(IIf(([aantaldagen]<=60) And ([aantaldagen]>=41),[E25T_tbl_items_openstaand]![AfrdocNr])) AS [41-60], Count(IIf(([aantaldagen]>=61),[E25T_tbl_items_openstaand]![AfrdocNr])) AS [60+], Format([DatumInLijst],"ww",2,3) AS Week
FROM t_soort INNER JOIN E25T_tbl_items_openstaand ON [t_soort].[key]=[E25T_tbl_items_openstaand].[soort]
WHERE (((Format([DatumInLijst],"yyyy"))=Year(Date())))
GROUP BY 1, [E25T_tbl_items_openstaand].[TeamNaam], [t_soort].[soort], Format([DatumInLijst],"ww",2,3)
HAVING (((Format([DatumInLijst],"ww",2,3))=Format(Date(),"ww",2,3)-1)) ) AS AliasTeamTotaal
GROUP BY 1, [E25T_tbl_rap_tellingen_historie]![TeamNaam] & " Totaal", "-", AliasTeamTotaal.Week
UNION SELECT 2 AS srt, "Eind Totaal" AS [Eind Totaal], "-" AS soort, Sum(AliasDetails.Totaal) AS Totaal, Sum(AliasDetails.[0-5]) AS [0-5], Sum(AliasDetails.[6–10]) AS 6–10, Sum(AliasDetails.[11–20]) AS 11–20, Sum(AliasDetails.[21-40]) AS [21-40], Sum(AliasDetails.[41-60]) AS [41-60], Sum(AliasDetails.[60+]) AS [60+], AliasDetails.Week
FROM (SELECT 1 AS srt, E25T_tbl_rap_tellingen_historie.TeamNaam, t_soort.soort, Sum(E25T_tbl_rap_tellingen_historie.Aantal) AS Totaal, Sum(IIf([aantaldagen]<=5,[E25T_tbl_rap_tellingen_historie]![Aantal])) AS [0-5], Sum(IIf(([aantaldagen]<=10) And ([aantaldagen]>=6),[E25T_tbl_rap_tellingen_historie]![Aantal])) AS 6–10, Sum(IIf(([aantaldagen]<=20) And ([aantaldagen]>=11),[E25T_tbl_rap_tellingen_historie]![Aantal])) AS 11–20, Sum(IIf(([aantaldagen]<=40) And ([aantaldagen]>=21),[E25T_tbl_rap_tellingen_historie]![Aantal])) AS [21-40], Sum(IIf(([aantaldagen]<=60) And ([aantaldagen]>=41),[E25T_tbl_rap_tellingen_historie]![Aantal])) AS [41-60], Sum(IIf(([aantaldagen]>=61),[E25T_tbl_rap_tellingen_historie]![Aantal])) AS [60+], Format([DatumInLijst],"ww",2,3) AS Week
FROM t_soort INNER JOIN E25T_tbl_rap_tellingen_historie ON t_soort.key = E25T_tbl_rap_tellingen_historie.soort
WHERE (((Format([DatumInLijst],"yyyy"))=Year(Date())))
GROUP BY 1, E25T_tbl_rap_tellingen_historie.TeamNaam, t_soort.soort, Format([DatumInLijst],"ww",2,3)
HAVING (((Format([DatumInLijst],"ww",2,3))=Format(Date(),"ww",2,3)-1))
UNION
SELECT 1 AS Srt, [E25T_tbl_items_openstaand].[TeamNaam], [t_soort].[soort], Count([E25T_tbl_items_openstaand].[AfrdocNr]) AS Totaal, Count(IIf([aantaldagen]<=5,[E25T_tbl_items_openstaand]![AfrdocNr])) AS [0-5], Count(IIf(([aantaldagen]<=10) And ([aantaldagen]>=6),[E25T_tbl_items_openstaand]![AfrdocNr])) AS 6–10, Count(IIf(([aantaldagen]<=20) And ([aantaldagen]>=11),[E25T_tbl_items_openstaand]![AfrdocNr])) AS 11–20, Count(IIf(([aantaldagen]<=40) And ([aantaldagen]>=21),[E25T_tbl_items_openstaand]![AfrdocNr])) AS [21-40], Count(IIf(([aantaldagen]<=60) And ([aantaldagen]>=41),[E25T_tbl_items_openstaand]![AfrdocNr])) AS [41-60], Count(IIf(([aantaldagen]>=61),[E25T_tbl_items_openstaand]![AfrdocNr])) AS [60+], Format([DatumInLijst],"ww",2,3) AS Week
FROM t_soort INNER JOIN E25T_tbl_items_openstaand ON [t_soort].[key]=[E25T_tbl_items_openstaand].[soort]
WHERE (((Format([DatumInLijst],"yyyy"))=Year(Date())))
GROUP BY 1, [E25T_tbl_items_openstaand].[TeamNaam], [t_soort].[soort], Format([DatumInLijst],"ww",2,3)
HAVING (((Format([DatumInLijst],"ww",2,3))=Format(Date(),"ww",2,3)-1)) ) AS AliasDetails
GROUP BY 2, "Eind Totaal", "-", AliasDetails.Week;

Question when I try to put the whole sql into a Alias and do a select * on the alias the query executes fine, but when I hit the savebutton Access crashes/quits instantly.
I thought that it could be due to the number of nested queries, but I believe that I didnt hit the 50 that are allowed. Does anybody have a clue of the reason?

EasyIT

 

[4946]

EasyIT,

This is what worked for me in a similar circumstance with Office XP (Access 2002):
In the top tool bar select "Tools", "Options", then the "General" tab. In the "Name AutoCorrect" section of the "General" tab page, deselect any items with checks in the selection boxes (I think there are three choices...)

Don't remember why it helped, or where I got the info, but hope it helps.

WinN

 

[easyit]

WinN you're the winner!! This is the solution. Normally I always have this option switched off to improve performance, but forgot about it. Thanks!!

EasyIT