MSAccess 2013: UNION any 2 of 3 tables works, UNION all 3 crashes
It's been years since I've worked with UNION queries in Access, but I don't see why this doesn't work. What am I missing?
SELECT IDAudit, TableName, RowID, ChangeDate, IDAPSCase
FROM dbo_APSCEI INNER JOIN (dbo_Audit INNER JOIN dbo_TableType ON dbo_Audit.IDTableType = dbo_TableType.IDTableType)
ON dbo_APSCEI.IDAPSCEI = dbo_Audit.RowID
WHERE (((TableName)="APSCEI") AND ((IDAPSCase)=379017))
UNION SELECT IDAudit, TableName, RowID, ChangeDate, IDAPSCase
FROM dbo_APCSUN INNER JOIN (dbo_Audit INNER JOIN dbo_TableType ON dbo_Audit.IDTableType = dbo_TableType.IDTableType)
ON dbo_APCSUN.IDAPCSUN= dbo_Audit.RowID
WHERE (((TableName)="APCSUN") AND ((IDAPSCase)=379017))
UNION SELECT IDAudit, TableName, RowID, ChangeDate, IDAPSCase
FROM dbo_APSCAI INNER JOIN (dbo_Audit INNER JOIN dbo_TableType ON dbo_Audit.IDTableType = dbo_TableType.IDTableType)
ON dbo_APSCAI.IDAPSCAI = dbo_audit.RowID
WHERE (((TableName)="APSCAI") AND ((IDAPSCase)=379017))
ORDER BY ChangeDate DESC , IDAPSCase;
Individually, all 3 SELECT statements work. I can remove any 1 of the 3 SELECT statements, and it will work. But with all 3, trying to run it, or even saving it, crashes Access. But, it does save the edits, despite crashing. (Microsoft Access has stopped working. Windows can try to recover your information.)
2 of the 3 tables involved (dbo_Audit and dbo_TableType) are always the same; only the 3rd table changes. Output columns are always the same. Syntax is always the same.
If I add an extra ')' to any of the WHERE clauses, it catches the error without crashing. When I remove it and try to run or save it, it crashes Access, again. I take this to mean that the syntax checker can handle it, but the query optimizer can't.
sql ms-access crash union
edited Nov 24 '18 at 1:47
Gordon Linoff
760k35294399
asked Nov 23 '18 at 19:00
DaveInAZDaveInAZ
1337
|
show 1 more comment
It's been years since I've worked with UNION queries in Access, but I don't see why this doesn't work. What am I missing?
SELECT IDAudit, TableName, RowID, ChangeDate, IDAPSCase
FROM dbo_APSCEI INNER JOIN (dbo_Audit INNER JOIN dbo_TableType ON dbo_Audit.IDTableType = dbo_TableType.IDTableType)
ON dbo_APSCEI.IDAPSCEI = dbo_Audit.RowID
WHERE (((TableName)="APSCEI") AND ((IDAPSCase)=379017))
UNION SELECT IDAudit, TableName, RowID, ChangeDate, IDAPSCase
FROM dbo_APCSUN INNER JOIN (dbo_Audit INNER JOIN dbo_TableType ON dbo_Audit.IDTableType = dbo_TableType.IDTableType)
ON dbo_APCSUN.IDAPCSUN= dbo_Audit.RowID
WHERE (((TableName)="APCSUN") AND ((IDAPSCase)=379017))
UNION SELECT IDAudit, TableName, RowID, ChangeDate, IDAPSCase
FROM dbo_APSCAI INNER JOIN (dbo_Audit INNER JOIN dbo_TableType ON dbo_Audit.IDTableType = dbo_TableType.IDTableType)
ON dbo_APSCAI.IDAPSCAI = dbo_audit.RowID
WHERE (((TableName)="APSCAI") AND ((IDAPSCase)=379017))
ORDER BY ChangeDate DESC , IDAPSCase;
Individually, all 3 SELECT statements work. I can remove any 1 of the 3 SELECT statements, and it will work. But with all 3, trying to run it, or even saving it, crashes Access. But, it does save the edits, despite crashing. (Microsoft Access has stopped working. Windows can try to recover your information.)
2 of the 3 tables involved (dbo_Audit and dbo_TableType) are always the same; only the 3rd table changes. Output columns are always the same. Syntax is always the same.
If I add an extra ')' to any of the WHERE clauses, it catches the error without crashing. When I remove it and try to run or save it, it crashes Access, again. I take this to mean that the syntax checker can handle it, but the query optimizer can't.
sql ms-access crash union
edited Nov 24 '18 at 1:47
Gordon Linoff
760k35294399
asked Nov 23 '18 at 19:00
DaveInAZDaveInAZ
1337
What happens if you remove all the parentheses in theWHEREclauses?
– forpas
Nov 23 '18 at 19:08
@forpas - The same thing
– DaveInAZ
Nov 23 '18 at 19:59
I found something similar (maybe) that was solved by removing the ORDER BY. Give it try.
– forpas
Nov 23 '18 at 20:05
And another one that replacedUNIONwithUNION ALL.
– forpas
Nov 23 '18 at 20:07
Sonofagun! Removing the Order By clause worked. I don't see WHY that worked, when the only fields in it were common to all three subqueries. I guess I can wrap another query around the union query to handle the sorting, but that's lame.
– DaveInAZ
Nov 23 '18 at 20:22
|
show 1 more comment
It's been years since I've worked with UNION queries in Access, but I don't see why this doesn't work. What am I missing?
SELECT IDAudit, TableName, RowID, ChangeDate, IDAPSCase
FROM dbo_APSCEI INNER JOIN (dbo_Audit INNER JOIN dbo_TableType ON dbo_Audit.IDTableType = dbo_TableType.IDTableType)
ON dbo_APSCEI.IDAPSCEI = dbo_Audit.RowID
WHERE (((TableName)="APSCEI") AND ((IDAPSCase)=379017))
UNION SELECT IDAudit, TableName, RowID, ChangeDate, IDAPSCase
FROM dbo_APCSUN INNER JOIN (dbo_Audit INNER JOIN dbo_TableType ON dbo_Audit.IDTableType = dbo_TableType.IDTableType)
ON dbo_APCSUN.IDAPCSUN= dbo_Audit.RowID
WHERE (((TableName)="APCSUN") AND ((IDAPSCase)=379017))
UNION SELECT IDAudit, TableName, RowID, ChangeDate, IDAPSCase
FROM dbo_APSCAI INNER JOIN (dbo_Audit INNER JOIN dbo_TableType ON dbo_Audit.IDTableType = dbo_TableType.IDTableType)
ON dbo_APSCAI.IDAPSCAI = dbo_audit.RowID
WHERE (((TableName)="APSCAI") AND ((IDAPSCase)=379017))
ORDER BY ChangeDate DESC , IDAPSCase;
Individually, all 3 SELECT statements work. I can remove any 1 of the 3 SELECT statements, and it will work. But with all 3, trying to run it, or even saving it, crashes Access. But, it does save the edits, despite crashing. (Microsoft Access has stopped working. Windows can try to recover your information.)
2 of the 3 tables involved (dbo_Audit and dbo_TableType) are always the same; only the 3rd table changes. Output columns are always the same. Syntax is always the same.
If I add an extra ')' to any of the WHERE clauses, it catches the error without crashing. When I remove it and try to run or save it, it crashes Access, again. I take this to mean that the syntax checker can handle it, but the query optimizer can't.
sql ms-access crash union
edited Nov 24 '18 at 1:47
Gordon Linoff
760k35294399
asked Nov 23 '18 at 19:00
DaveInAZDaveInAZ
1337
It's been years since I've worked with UNION queries in Access, but I don't see why this doesn't work. What am I missing?
SELECT IDAudit, TableName, RowID, ChangeDate, IDAPSCase
FROM dbo_APSCEI INNER JOIN (dbo_Audit INNER JOIN dbo_TableType ON dbo_Audit.IDTableType = dbo_TableType.IDTableType)
ON dbo_APSCEI.IDAPSCEI = dbo_Audit.RowID
WHERE (((TableName)="APSCEI") AND ((IDAPSCase)=379017))
UNION SELECT IDAudit, TableName, RowID, ChangeDate, IDAPSCase
FROM dbo_APCSUN INNER JOIN (dbo_Audit INNER JOIN dbo_TableType ON dbo_Audit.IDTableType = dbo_TableType.IDTableType)
ON dbo_APCSUN.IDAPCSUN= dbo_Audit.RowID
WHERE (((TableName)="APCSUN") AND ((IDAPSCase)=379017))
UNION SELECT IDAudit, TableName, RowID, ChangeDate, IDAPSCase
FROM dbo_APSCAI INNER JOIN (dbo_Audit INNER JOIN dbo_TableType ON dbo_Audit.IDTableType = dbo_TableType.IDTableType)
ON dbo_APSCAI.IDAPSCAI = dbo_audit.RowID
WHERE (((TableName)="APSCAI") AND ((IDAPSCase)=379017))
ORDER BY ChangeDate DESC , IDAPSCase;
Individually, all 3 SELECT statements work. I can remove any 1 of the 3 SELECT statements, and it will work. But with all 3, trying to run it, or even saving it, crashes Access. But, it does save the edits, despite crashing. (Microsoft Access has stopped working. Windows can try to recover your information.)
2 of the 3 tables involved (dbo_Audit and dbo_TableType) are always the same; only the 3rd table changes. Output columns are always the same. Syntax is always the same.
If I add an extra ')' to any of the WHERE clauses, it catches the error without crashing. When I remove it and try to run or save it, it crashes Access, again. I take this to mean that the syntax checker can handle it, but the query optimizer can't.
sql ms-access crash union
sql ms-access crash union
edited Nov 24 '18 at 1:47
Gordon Linoff
760k35294399
asked Nov 23 '18 at 19:00
DaveInAZDaveInAZ
1337
edited Nov 24 '18 at 1:47
Gordon Linoff
760k35294399
asked Nov 23 '18 at 19:00
DaveInAZDaveInAZ
1337
edited Nov 24 '18 at 1:47
Gordon Linoff
760k35294399
edited Nov 24 '18 at 1:47
Gordon Linoff
760k35294399
edited Nov 24 '18 at 1:47
Gordon Linoff
760k35294399
760k35294399
asked Nov 23 '18 at 19:00
DaveInAZDaveInAZ
1337
asked Nov 23 '18 at 19:00
DaveInAZDaveInAZ
1337
asked Nov 23 '18 at 19:00
DaveInAZDaveInAZ
1337
1337
What happens if you remove all the parentheses in theWHEREclauses?
– forpas
Nov 23 '18 at 19:08
@forpas - The same thing
– DaveInAZ
Nov 23 '18 at 19:59
I found something similar (maybe) that was solved by removing the ORDER BY. Give it try.
– forpas
Nov 23 '18 at 20:05
And another one that replacedUNIONwithUNION ALL.
– forpas
Nov 23 '18 at 20:07
Sonofagun! Removing the Order By clause worked. I don't see WHY that worked, when the only fields in it were common to all three subqueries. I guess I can wrap another query around the union query to handle the sorting, but that's lame.
– DaveInAZ
Nov 23 '18 at 20:22
|
show 1 more comment
What happens if you remove all the parentheses in theWHEREclauses?
– forpas
Nov 23 '18 at 19:08
@forpas - The same thing
– DaveInAZ
Nov 23 '18 at 19:59
I found something similar (maybe) that was solved by removing the ORDER BY. Give it try.
– forpas
Nov 23 '18 at 20:05
And another one that replacedUNIONwithUNION ALL.
– forpas
Nov 23 '18 at 20:07
Sonofagun! Removing the Order By clause worked. I don't see WHY that worked, when the only fields in it were common to all three subqueries. I guess I can wrap another query around the union query to handle the sorting, but that's lame.
– DaveInAZ
Nov 23 '18 at 20:22
What happens if you remove all the parentheses in the
WHERE clauses?– forpas
Nov 23 '18 at 19:08
What happens if you remove all the parentheses in the
WHERE clauses?– forpas
Nov 23 '18 at 19:08
@forpas - The same thing
– DaveInAZ
Nov 23 '18 at 19:59
@forpas - The same thing
– DaveInAZ
Nov 23 '18 at 19:59
I found something similar (maybe) that was solved by removing the ORDER BY. Give it try.
– forpas
Nov 23 '18 at 20:05
I found something similar (maybe) that was solved by removing the ORDER BY. Give it try.
– forpas
Nov 23 '18 at 20:05
And another one that replaced
UNION with UNION ALL.– forpas
Nov 23 '18 at 20:07
And another one that replaced
UNION with UNION ALL.– forpas
Nov 23 '18 at 20:07
Sonofagun! Removing the Order By clause worked. I don't see WHY that worked, when the only fields in it were common to all three subqueries. I guess I can wrap another query around the union query to handle the sorting, but that's lame.
– DaveInAZ
Nov 23 '18 at 20:22
Sonofagun! Removing the Order By clause worked. I don't see WHY that worked, when the only fields in it were common to all three subqueries. I guess I can wrap another query around the union query to handle the sorting, but that's lame.
– DaveInAZ
Nov 23 '18 at 20:22
|
show 1 more comment
1 Answer
1
active
oldest
votes
It looks to me as if the order by is only affecting the last query.
If you need to order the result, make a query that calls this one like
SELECT * FROM MyUnionQuery ORDER BY ChangeDate DESC , IDAPSCase
and all should be good!
answered Nov 26 '18 at 5:43
trevortrevor
1045
Yeah, that's what I was saying. But, Union queries can only have one Order By clause, and it's applied to the unioned superset of records.
– DaveInAZ
Nov 26 '18 at 19:56
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53451788%2fmsaccess-2013-union-any-2-of-3-tables-works-union-all-3-crashes%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
It looks to me as if the order by is only affecting the last query.
If you need to order the result, make a query that calls this one like
SELECT * FROM MyUnionQuery ORDER BY ChangeDate DESC , IDAPSCase
and all should be good!
answered Nov 26 '18 at 5:43
trevortrevor
1045
Yeah, that's what I was saying. But, Union queries can only have one Order By clause, and it's applied to the unioned superset of records.
– DaveInAZ
Nov 26 '18 at 19:56
add a comment |
It looks to me as if the order by is only affecting the last query.
If you need to order the result, make a query that calls this one like
SELECT * FROM MyUnionQuery ORDER BY ChangeDate DESC , IDAPSCase
and all should be good!
answered Nov 26 '18 at 5:43
trevortrevor
1045
Yeah, that's what I was saying. But, Union queries can only have one Order By clause, and it's applied to the unioned superset of records.
– DaveInAZ
Nov 26 '18 at 19:56
add a comment |
It looks to me as if the order by is only affecting the last query.
If you need to order the result, make a query that calls this one like
SELECT * FROM MyUnionQuery ORDER BY ChangeDate DESC , IDAPSCase
and all should be good!
answered Nov 26 '18 at 5:43
trevortrevor
1045
It looks to me as if the order by is only affecting the last query.
If you need to order the result, make a query that calls this one like
SELECT * FROM MyUnionQuery ORDER BY ChangeDate DESC , IDAPSCase
and all should be good!
answered Nov 26 '18 at 5:43
trevortrevor
1045
answered Nov 26 '18 at 5:43
trevortrevor
1045
answered Nov 26 '18 at 5:43
trevortrevor
1045
answered Nov 26 '18 at 5:43
trevortrevor
1045
1045
Yeah, that's what I was saying. But, Union queries can only have one Order By clause, and it's applied to the unioned superset of records.
– DaveInAZ
Nov 26 '18 at 19:56
add a comment |
Yeah, that's what I was saying. But, Union queries can only have one Order By clause, and it's applied to the unioned superset of records.
– DaveInAZ
Nov 26 '18 at 19:56
Yeah, that's what I was saying. But, Union queries can only have one Order By clause, and it's applied to the unioned superset of records.
– DaveInAZ
Nov 26 '18 at 19:56
Yeah, that's what I was saying. But, Union queries can only have one Order By clause, and it's applied to the unioned superset of records.
– DaveInAZ
Nov 26 '18 at 19:56
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53451788%2fmsaccess-2013-union-any-2-of-3-tables-works-union-all-3-crashes%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What happens if you remove all the parentheses in the
WHEREclauses?– forpas
Nov 23 '18 at 19:08
@forpas - The same thing
– DaveInAZ
Nov 23 '18 at 19:59
I found something similar (maybe) that was solved by removing the ORDER BY. Give it try.
– forpas
Nov 23 '18 at 20:05
And another one that replaced
UNIONwithUNION ALL.– forpas
Nov 23 '18 at 20:07
Sonofagun! Removing the Order By clause worked. I don't see WHY that worked, when the only fields in it were common to all three subqueries. I guess I can wrap another query around the union query to handle the sorting, but that's lame.
– DaveInAZ
Nov 23 '18 at 20:22