I wrote a large query that contains multiple subqueries and joins. I can execute the query and see my results, however when I try to save it, I get the: "Microsoft Access has encountered an error . . . and has to close." When I reopen, an empty query with the saved name is all that remains. This is getting quite frustrating, so any help is appreciated.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
On saving a Query, there is an Access error and program closes
- Thread starter charle525
- Start date
I get this a lot too and it is indeed very annoying. I don't know a good way around it but I usually end up copying and pasting SQL queries into notepad or something similar when they reach a certain size/ complexity so as not to lose anything important!
Have a look at:
for a more precise description of this problem from Microsoft...
Have a look at:
for a more precise description of this problem from Microsoft...
charle525
Try saving nested queries separately. I had a guy, who learned from the school or "hard knocks" designed a database with what I considered to be a poor design. He got through it with pure brute force. (See appended query example) But the code was gratly simplfied when the queries were saved separately.
query_1 select * from tblA where tblA.StartDate between [parameters] and tblA.EmployeeID = query_2
query_2 select tblB.EmployeeID, ... from tblB where tblB.status = "Active"
In the SQL statement, Access will create an inner join query1 where query1.employeeID = query2.employeeID.
In addition to adding tables to a query design, you can also add queries (sub queries). This saves space and improves readability.
Also, I had a similar problem with reports - PC crashing. This turned out to be a hardware issue where I needed more memory on my PC.
Richard.
p.s. Here is the type of query this guy built -- over 6000 characters. The amazing thing is that it works,, and he used about 10 such queries, but I would not recommend this thing to any friend of mine. (The guy who did this learned his lesson, and is a very much improved Access user.)
SELECT [Time Cards 2].[Week Ending], HREMP.ED_SURNAME, HREMP.ED_FNAME, [Time Cards].Operator, [Time Cards].[Absence Sunday], [Time Cards].[1 ABS Code], [Time Cards].[1 Occurance], [Time Cards].[Absence Monday], [Time Cards].[2 ABS Code], [Time Cards].[2 Occurance], [Time Cards].[Absence Tuesday], [Time Cards].[3 ABS Code], [Time Cards].[3 Occurance], [Time Cards].[Absence Wednesday], [Time Cards].[4 ABS Code], [Time Cards].[4 Occurance], [Time Cards].[Absence Thursday], [Time Cards].[5 ABS Code], [Time Cards].[5 Occurance], [Time Cards].[Absence Friday], [Time Cards].[6 ABS Code], [Time Cards].[6 Occurance], [Time Cards].[Absence Saturday], [Time Cards].[7 Abs Code], [Time Cards].[7 Occurance], IIf([1 ABS Code]="Sick",[Absence Sunday],0)+IIf([2 ABS Code]="Sick",[Absence Monday],0)+IIf([3 ABS Code]="Sick",[Absence Tuesday],0)+IIf([4 ABS Code]="Sick",[Absence Wednesday],0)+IIf([5 ABS Code]="Sick",[Absence Thursday],0)+IIf([6 ABS Code]="Sick",[Absence Friday],0)+IIf([7 ABS Code]="Sick",[Absence Saturday],0) AS [Hours Sick], IIf([1 Occurance] Is Null,0,[1 Occurance])+IIf([2 Occurance] Is Null,0,[2 Occurance])+IIf([3 Occurance] Is Null,0,[3 Occurance])+IIf([4 Occurance] Is Null,0,[4 Occurance])+IIf([5 Occurance] Is Null,0,[5 Occurance])+IIf([6 Occurance] Is Null,0,[6 Occurance])+IIf([7 Occurance] Is Null,0,[7 Occurance]) AS Occurances, IIf([1 ABS Code]="Sick",IIf([Absence Sunday]<12,1,0),0)+IIf([2 ABS Code]="Sick",IIf([Absence Monday]<12,1,0),0)+IIf([3 ABS Code]="Sick",IIf([Absence Tuesday]<12,1,0),0)+IIf([4 ABS Code]="Sick",IIf([Absence Wednesday]<12,1,0),0)+IIf([5 ABS Code]="Sick",IIf([Absence Thursday]<12,1,0),0)+IIf([6 ABS Code]="Sick",IIf([Absence Friday]<12,1,0),0)+IIf([7 ABS Code]="Sick",IIf([Absence Saturday]<12,1,0),0) AS [Number Part Sick Days], IIf([1 ABS Code]="Sick",IIf([Absence Sunday]<12,[Absence Sunday],0),0)+IIf([2 ABS Code]="Sick",IIf([Absence Monday]<12,[Absence Monday],0),0)+IIf([3 ABS Code]="Sick",IIf([Absence Tuesday]<12,[Absence Tuesday],0),0)+IIf([4 ABS Code]="Sick",IIf([Absence Wednesday]<12,[Absence Wednesday],0),0)+IIf([5 ABS Code]="Sick",IIf([Absence Thursday]<12,[Absence Thursday],0),0)+IIf([6 ABS Code]="Sick",IIf([Absence Friday]<12,[Absence Friday],0),0)+IIf([7 ABS Code]="Sick",IIf([Absence Saturday]<12,[Absence Saturday],0),0) AS [Hrs Part Sick Days], IIf([1 ABS Code]="Vac",[Absence Sunday],0)+IIf([2 ABS Code]="Vac",[Absence Monday],0)+IIf([3 ABS Code]="Vac",[Absence Tuesday],0)+IIf([4 ABS Code]="Vac",[Absence Wednesday],0)+IIf([5 ABS Code]="Vac",[Absence Thursday],0)+IIf([6 ABS Code]="Vac",[Absence Friday],0)+IIf([7 ABS Code]="Vac",[Absence Saturday],0) AS [Hours Vacation], IIf([1 ABS Code]="28",[Absence Sunday],0)+IIf([2 ABS Code]="28",[Absence Monday],0)+IIf([3 ABS Code]="28",[Absence Tuesday],0)+IIf([4 ABS Code]="28",[Absence Wednesday],0)+IIf([5 ABS Code]="28",[Absence Thursday],0)+IIf([6 ABS Code]="28",[Absence Friday],0)+IIf([7 ABS Code]="28",[Absence Saturday],0) AS [Floater Hours]
FROM HREMP INNER JOIN ([Time Cards 2] INNER JOIN [Time Cards] ON [Time Cards 2].[Record Number] = [Time Cards].Counter) ON HREMP.ED_EMPNBR = [Time Cards].Operator
GROUP BY [Time Cards 2].[Week Ending], HREMP.ED_SURNAME, HREMP.ED_FNAME, [Time Cards].Operator, [Time Cards].[Absence Sunday], [Time Cards].[1 ABS Code], [Time Cards].[1 Occurance], [Time Cards].[Absence Monday], [Time Cards].[2 ABS Code], [Time Cards].[2 Occurance], [Time Cards].[Absence Tuesday], [Time Cards].[3 ABS Code], [Time Cards].[3 Occurance], [Time Cards].[Absence Wednesday], [Time Cards].[4 ABS Code], [Time Cards].[4 Occurance], [Time Cards].[Absence Thursday], [Time Cards].[5 ABS Code], [Time Cards].[5 Occurance], [Time Cards].[Absence Friday], [Time Cards].[6 ABS Code], [Time Cards].[6 Occurance], [Time Cards].[Absence Saturday], [Time Cards].[7 Abs Code], [Time Cards].[7 Occurance], IIf([1 ABS Code]="Sick",[Absence Sunday],0)+IIf([2 ABS Code]="Sick",[Absence Monday],0)+IIf([3 ABS Code]="Sick",[Absence Tuesday],0)+IIf([4 ABS Code]="Sick",[Absence Wednesday],0)+IIf([5 ABS Code]="Sick",[Absence Thursday],0)+IIf([6 ABS Code]="Sick",[Absence Friday],0)+IIf([7 ABS Code]="Sick",[Absence Saturday],0), IIf([1 Occurance] Is Null,0,[1 Occurance])+IIf([2 Occurance] Is Null,0,[2 Occurance])+IIf([3 Occurance] Is Null,0,[3 Occurance])+IIf([4 Occurance] Is Null,0,[4 Occurance])+IIf([5 Occurance] Is Null,0,[5 Occurance])+IIf([6 Occurance] Is Null,0,[6 Occurance])+IIf([7 Occurance] Is Null,0,[7 Occurance]), IIf([1 ABS Code]="Sick",IIf([Absence Sunday]<12,1,0),0)+IIf([2 ABS Code]="Sick",IIf([Absence Monday]<12,1,0),0)+IIf([3 ABS Code]="Sick",IIf([Absence Tuesday]<12,1,0),0)+IIf([4 ABS Code]="Sick",IIf([Absence Wednesday]<12,1,0),0)+IIf([5 ABS Code]="Sick",IIf([Absence Thursday]<12,1,0),0)+IIf([6 ABS Code]="Sick",IIf([Absence Friday]<12,1,0),0)+IIf([7 ABS Code]="Sick",IIf([Absence Saturday]<12,1,0),0), IIf([1 ABS Code]="Sick",IIf([Absence Sunday]<12,[Absence Sunday],0),0)+IIf([2 ABS Code]="Sick",IIf([Absence Monday]<12,[Absence Monday],0),0)+IIf([3 ABS Code]="Sick",IIf([Absence Tuesday]<12,[Absence Tuesday],0),0)+IIf([4 ABS Code]="Sick",IIf([Absence Wednesday]<12,[Absence Wednesday],0),0)+IIf([5 ABS Code]="Sick",IIf([Absence Thursday]<12,[Absence Thursday],0),0)+IIf([6 ABS Code]="Sick",IIf([Absence Friday]<12,[Absence Friday],0),0)+IIf([7 ABS Code]="Sick",IIf([Absence Saturday]<12,[Absence Saturday],0),0), IIf([1 ABS Code]="Vac",[Absence Sunday],0)+IIf([2 ABS Code]="Vac",[Absence Monday],0)+IIf([3 ABS Code]="Vac",[Absence Tuesday],0)+IIf([4 ABS Code]="Vac",[Absence Wednesday],0)+IIf([5 ABS Code]="Vac",[Absence Thursday],0)+IIf([6 ABS Code]="Vac",[Absence Friday],0)+IIf([7 ABS Code]="Vac",[Absence Saturday],0), IIf([1 ABS Code]="28",[Absence Sunday],0)+IIf([2 ABS Code]="28",[Absence Monday],0)+IIf([3 ABS Code]="28",[Absence Tuesday],0)+IIf([4 ABS Code]="28",[Absence Wednesday],0)+IIf([5 ABS Code]="28",[Absence Thursday],0)+IIf([6 ABS Code]="28",[Absence Friday],0)+IIf([7 ABS Code]="28",[Absence Saturday],0)
HAVING ((([Time Cards].Operator)=[Forms]![Absent Dialog Box]![Field8]))
ORDER BY [Time Cards].Operator;
Try saving nested queries separately. I had a guy, who learned from the school or "hard knocks" designed a database with what I considered to be a poor design. He got through it with pure brute force. (See appended query example) But the code was gratly simplfied when the queries were saved separately.
query_1 select * from tblA where tblA.StartDate between [parameters] and tblA.EmployeeID = query_2
query_2 select tblB.EmployeeID, ... from tblB where tblB.status = "Active"
In the SQL statement, Access will create an inner join query1 where query1.employeeID = query2.employeeID.
In addition to adding tables to a query design, you can also add queries (sub queries). This saves space and improves readability.
Also, I had a similar problem with reports - PC crashing. This turned out to be a hardware issue where I needed more memory on my PC.
Richard.
p.s. Here is the type of query this guy built -- over 6000 characters. The amazing thing is that it works,, and he used about 10 such queries, but I would not recommend this thing to any friend of mine. (The guy who did this learned his lesson, and is a very much improved Access user.)
SELECT [Time Cards 2].[Week Ending], HREMP.ED_SURNAME, HREMP.ED_FNAME, [Time Cards].Operator, [Time Cards].[Absence Sunday], [Time Cards].[1 ABS Code], [Time Cards].[1 Occurance], [Time Cards].[Absence Monday], [Time Cards].[2 ABS Code], [Time Cards].[2 Occurance], [Time Cards].[Absence Tuesday], [Time Cards].[3 ABS Code], [Time Cards].[3 Occurance], [Time Cards].[Absence Wednesday], [Time Cards].[4 ABS Code], [Time Cards].[4 Occurance], [Time Cards].[Absence Thursday], [Time Cards].[5 ABS Code], [Time Cards].[5 Occurance], [Time Cards].[Absence Friday], [Time Cards].[6 ABS Code], [Time Cards].[6 Occurance], [Time Cards].[Absence Saturday], [Time Cards].[7 Abs Code], [Time Cards].[7 Occurance], IIf([1 ABS Code]="Sick",[Absence Sunday],0)+IIf([2 ABS Code]="Sick",[Absence Monday],0)+IIf([3 ABS Code]="Sick",[Absence Tuesday],0)+IIf([4 ABS Code]="Sick",[Absence Wednesday],0)+IIf([5 ABS Code]="Sick",[Absence Thursday],0)+IIf([6 ABS Code]="Sick",[Absence Friday],0)+IIf([7 ABS Code]="Sick",[Absence Saturday],0) AS [Hours Sick], IIf([1 Occurance] Is Null,0,[1 Occurance])+IIf([2 Occurance] Is Null,0,[2 Occurance])+IIf([3 Occurance] Is Null,0,[3 Occurance])+IIf([4 Occurance] Is Null,0,[4 Occurance])+IIf([5 Occurance] Is Null,0,[5 Occurance])+IIf([6 Occurance] Is Null,0,[6 Occurance])+IIf([7 Occurance] Is Null,0,[7 Occurance]) AS Occurances, IIf([1 ABS Code]="Sick",IIf([Absence Sunday]<12,1,0),0)+IIf([2 ABS Code]="Sick",IIf([Absence Monday]<12,1,0),0)+IIf([3 ABS Code]="Sick",IIf([Absence Tuesday]<12,1,0),0)+IIf([4 ABS Code]="Sick",IIf([Absence Wednesday]<12,1,0),0)+IIf([5 ABS Code]="Sick",IIf([Absence Thursday]<12,1,0),0)+IIf([6 ABS Code]="Sick",IIf([Absence Friday]<12,1,0),0)+IIf([7 ABS Code]="Sick",IIf([Absence Saturday]<12,1,0),0) AS [Number Part Sick Days], IIf([1 ABS Code]="Sick",IIf([Absence Sunday]<12,[Absence Sunday],0),0)+IIf([2 ABS Code]="Sick",IIf([Absence Monday]<12,[Absence Monday],0),0)+IIf([3 ABS Code]="Sick",IIf([Absence Tuesday]<12,[Absence Tuesday],0),0)+IIf([4 ABS Code]="Sick",IIf([Absence Wednesday]<12,[Absence Wednesday],0),0)+IIf([5 ABS Code]="Sick",IIf([Absence Thursday]<12,[Absence Thursday],0),0)+IIf([6 ABS Code]="Sick",IIf([Absence Friday]<12,[Absence Friday],0),0)+IIf([7 ABS Code]="Sick",IIf([Absence Saturday]<12,[Absence Saturday],0),0) AS [Hrs Part Sick Days], IIf([1 ABS Code]="Vac",[Absence Sunday],0)+IIf([2 ABS Code]="Vac",[Absence Monday],0)+IIf([3 ABS Code]="Vac",[Absence Tuesday],0)+IIf([4 ABS Code]="Vac",[Absence Wednesday],0)+IIf([5 ABS Code]="Vac",[Absence Thursday],0)+IIf([6 ABS Code]="Vac",[Absence Friday],0)+IIf([7 ABS Code]="Vac",[Absence Saturday],0) AS [Hours Vacation], IIf([1 ABS Code]="28",[Absence Sunday],0)+IIf([2 ABS Code]="28",[Absence Monday],0)+IIf([3 ABS Code]="28",[Absence Tuesday],0)+IIf([4 ABS Code]="28",[Absence Wednesday],0)+IIf([5 ABS Code]="28",[Absence Thursday],0)+IIf([6 ABS Code]="28",[Absence Friday],0)+IIf([7 ABS Code]="28",[Absence Saturday],0) AS [Floater Hours]
FROM HREMP INNER JOIN ([Time Cards 2] INNER JOIN [Time Cards] ON [Time Cards 2].[Record Number] = [Time Cards].Counter) ON HREMP.ED_EMPNBR = [Time Cards].Operator
GROUP BY [Time Cards 2].[Week Ending], HREMP.ED_SURNAME, HREMP.ED_FNAME, [Time Cards].Operator, [Time Cards].[Absence Sunday], [Time Cards].[1 ABS Code], [Time Cards].[1 Occurance], [Time Cards].[Absence Monday], [Time Cards].[2 ABS Code], [Time Cards].[2 Occurance], [Time Cards].[Absence Tuesday], [Time Cards].[3 ABS Code], [Time Cards].[3 Occurance], [Time Cards].[Absence Wednesday], [Time Cards].[4 ABS Code], [Time Cards].[4 Occurance], [Time Cards].[Absence Thursday], [Time Cards].[5 ABS Code], [Time Cards].[5 Occurance], [Time Cards].[Absence Friday], [Time Cards].[6 ABS Code], [Time Cards].[6 Occurance], [Time Cards].[Absence Saturday], [Time Cards].[7 Abs Code], [Time Cards].[7 Occurance], IIf([1 ABS Code]="Sick",[Absence Sunday],0)+IIf([2 ABS Code]="Sick",[Absence Monday],0)+IIf([3 ABS Code]="Sick",[Absence Tuesday],0)+IIf([4 ABS Code]="Sick",[Absence Wednesday],0)+IIf([5 ABS Code]="Sick",[Absence Thursday],0)+IIf([6 ABS Code]="Sick",[Absence Friday],0)+IIf([7 ABS Code]="Sick",[Absence Saturday],0), IIf([1 Occurance] Is Null,0,[1 Occurance])+IIf([2 Occurance] Is Null,0,[2 Occurance])+IIf([3 Occurance] Is Null,0,[3 Occurance])+IIf([4 Occurance] Is Null,0,[4 Occurance])+IIf([5 Occurance] Is Null,0,[5 Occurance])+IIf([6 Occurance] Is Null,0,[6 Occurance])+IIf([7 Occurance] Is Null,0,[7 Occurance]), IIf([1 ABS Code]="Sick",IIf([Absence Sunday]<12,1,0),0)+IIf([2 ABS Code]="Sick",IIf([Absence Monday]<12,1,0),0)+IIf([3 ABS Code]="Sick",IIf([Absence Tuesday]<12,1,0),0)+IIf([4 ABS Code]="Sick",IIf([Absence Wednesday]<12,1,0),0)+IIf([5 ABS Code]="Sick",IIf([Absence Thursday]<12,1,0),0)+IIf([6 ABS Code]="Sick",IIf([Absence Friday]<12,1,0),0)+IIf([7 ABS Code]="Sick",IIf([Absence Saturday]<12,1,0),0), IIf([1 ABS Code]="Sick",IIf([Absence Sunday]<12,[Absence Sunday],0),0)+IIf([2 ABS Code]="Sick",IIf([Absence Monday]<12,[Absence Monday],0),0)+IIf([3 ABS Code]="Sick",IIf([Absence Tuesday]<12,[Absence Tuesday],0),0)+IIf([4 ABS Code]="Sick",IIf([Absence Wednesday]<12,[Absence Wednesday],0),0)+IIf([5 ABS Code]="Sick",IIf([Absence Thursday]<12,[Absence Thursday],0),0)+IIf([6 ABS Code]="Sick",IIf([Absence Friday]<12,[Absence Friday],0),0)+IIf([7 ABS Code]="Sick",IIf([Absence Saturday]<12,[Absence Saturday],0),0), IIf([1 ABS Code]="Vac",[Absence Sunday],0)+IIf([2 ABS Code]="Vac",[Absence Monday],0)+IIf([3 ABS Code]="Vac",[Absence Tuesday],0)+IIf([4 ABS Code]="Vac",[Absence Wednesday],0)+IIf([5 ABS Code]="Vac",[Absence Thursday],0)+IIf([6 ABS Code]="Vac",[Absence Friday],0)+IIf([7 ABS Code]="Vac",[Absence Saturday],0), IIf([1 ABS Code]="28",[Absence Sunday],0)+IIf([2 ABS Code]="28",[Absence Monday],0)+IIf([3 ABS Code]="28",[Absence Tuesday],0)+IIf([4 ABS Code]="28",[Absence Wednesday],0)+IIf([5 ABS Code]="28",[Absence Thursday],0)+IIf([6 ABS Code]="28",[Absence Friday],0)+IIf([7 ABS Code]="28",[Absence Saturday],0)
HAVING ((([Time Cards].Operator)=[Forms]![Absent Dialog Box]![Field8]))
ORDER BY [Time Cards].Operator;
- Status
Similar threads
- Locked
- Question
- Question
- Locked
- Question