Merge queries with Union Select
1
I'm trying to merge two queries with Union Select, but I can't find what I'm doing wrong... I have used this method before with no problem, but there must be something that I don't realize about that is giving me problems.
The two queries I'm trying to merge are as follows:
SELECT Parejas.[ID Pareja], Parejas.NombrePareja AS [Nombre Pareja / Paciente], Parejas.FechaAlta AS [Fecha de alta], Count(Sesiones.[ID sesión]) AS [CuentaDeID sesión], Profesional.Nombre
FROM Paciente INNER JOIN (Profesional INNER JOIN (Parejas INNER JOIN Sesiones ON Parejas.[ID Pareja] = Sesiones.[ID Pareja]) ON Profesional.[ID Profesional] = Sesiones.[ID Profesional]) ON Paciente.[ID Paciente] = Parejas.Pacientes.Value
WHERE (((Parejas.FechaAlta)>[Formularios]![Consulta_duracion]![Texto15]))
GROUP BY Parejas.[ID Pareja], Parejas.NombrePareja, Parejas.FechaAlta, Profesional.Nombre;
And the second one is this one:
SELECT Parejas.[ID Pareja], Paciente.Nombre AS [Nombre Pareja / Paciente], Paciente.FechaAlta AS [Fecha de alta], Count(Sesiones.[ID sesión]) AS [CuentaDeID sesión], Profesional.Nombre
FROM (Profesional INNER JOIN (Paciente INNER JOIN Sesiones ON Paciente.[ID Paciente] = Sesiones.[ID Paciente]) ON Profesional.[ID Profesional] = Sesiones.[ID Profesional]) INNER JOIN Parejas ON Paciente.[ID Paciente] = Parejas.Pacientes.Value
WHERE (((Paciente.FechaAlta)>[Formularios]![Consulta_duracion]![Texto15]))
GROUP BY Parejas.[ID Pareja], Paciente.Nombre, Paciente.FechaAlta, Profesional.Nombre;
I use the Union clause to merge both queries, but I always get the following error message:
"your query does not include the specified expression 'ID Pareja" as part of an aggregate function"
May be any of you do have any clue about what the problem is?
Thank you so much!
edit: I attach a image with all the tables and relations. As you can see, "Paciente" and "Sesiones" are the main tables, over which all others are designed.
enter image description here
sql ms-access
asked Nov 23 '18 at 20:13
juanjoalv205juanjoalv205
62
|
show 2 more comments
1
I'm trying to merge two queries with Union Select, but I can't find what I'm doing wrong... I have used this method before with no problem, but there must be something that I don't realize about that is giving me problems.
The two queries I'm trying to merge are as follows:
SELECT Parejas.[ID Pareja], Parejas.NombrePareja AS [Nombre Pareja / Paciente], Parejas.FechaAlta AS [Fecha de alta], Count(Sesiones.[ID sesión]) AS [CuentaDeID sesión], Profesional.Nombre
FROM Paciente INNER JOIN (Profesional INNER JOIN (Parejas INNER JOIN Sesiones ON Parejas.[ID Pareja] = Sesiones.[ID Pareja]) ON Profesional.[ID Profesional] = Sesiones.[ID Profesional]) ON Paciente.[ID Paciente] = Parejas.Pacientes.Value
WHERE (((Parejas.FechaAlta)>[Formularios]![Consulta_duracion]![Texto15]))
GROUP BY Parejas.[ID Pareja], Parejas.NombrePareja, Parejas.FechaAlta, Profesional.Nombre;
And the second one is this one:
SELECT Parejas.[ID Pareja], Paciente.Nombre AS [Nombre Pareja / Paciente], Paciente.FechaAlta AS [Fecha de alta], Count(Sesiones.[ID sesión]) AS [CuentaDeID sesión], Profesional.Nombre
FROM (Profesional INNER JOIN (Paciente INNER JOIN Sesiones ON Paciente.[ID Paciente] = Sesiones.[ID Paciente]) ON Profesional.[ID Profesional] = Sesiones.[ID Profesional]) INNER JOIN Parejas ON Paciente.[ID Paciente] = Parejas.Pacientes.Value
WHERE (((Paciente.FechaAlta)>[Formularios]![Consulta_duracion]![Texto15]))
GROUP BY Parejas.[ID Pareja], Paciente.Nombre, Paciente.FechaAlta, Profesional.Nombre;
I use the Union clause to merge both queries, but I always get the following error message:
"your query does not include the specified expression 'ID Pareja" as part of an aggregate function"
May be any of you do have any clue about what the problem is?
Thank you so much!
edit: I attach a image with all the tables and relations. As you can see, "Paciente" and "Sesiones" are the main tables, over which all others are designed.
enter image description here
sql ms-access
asked Nov 23 '18 at 20:13
juanjoalv205juanjoalv205
62
Its problem with uneven id aggregation on unioning both selects try union first then apply group by it should work
– Himanshu Ahuja
Nov 23 '18 at 20:18
Save each query as a query. Then union the two saved queries using a straight union query.
– Gustav
Nov 23 '18 at 21:00
I have saved each one as independent queries, and it doesn't work. They do work separately, but not when I make a union query.
– juanjoalv205
Nov 23 '18 at 21:21
Hi Himanshu, thanks a lot for your comment. How is that? I don't understand what you mean by "union first then apply group by"...
– juanjoalv205
Nov 23 '18 at 21:22
1
With such similar queries, this might be a database design question. You might need normalization to avoid redundancy of records. Can you provide the background "story" of your relational model? What are you capturing between Professional, Patient, Couples, and Session tables? Any 1-to-1, 1-to-many, many-to-many relationships? Please edit post and not answer as a long comment.
– Parfait
Nov 23 '18 at 23:16
|
show 2 more comments
1
1
1
I'm trying to merge two queries with Union Select, but I can't find what I'm doing wrong... I have used this method before with no problem, but there must be something that I don't realize about that is giving me problems.
The two queries I'm trying to merge are as follows:
SELECT Parejas.[ID Pareja], Parejas.NombrePareja AS [Nombre Pareja / Paciente], Parejas.FechaAlta AS [Fecha de alta], Count(Sesiones.[ID sesión]) AS [CuentaDeID sesión], Profesional.Nombre
FROM Paciente INNER JOIN (Profesional INNER JOIN (Parejas INNER JOIN Sesiones ON Parejas.[ID Pareja] = Sesiones.[ID Pareja]) ON Profesional.[ID Profesional] = Sesiones.[ID Profesional]) ON Paciente.[ID Paciente] = Parejas.Pacientes.Value
WHERE (((Parejas.FechaAlta)>[Formularios]![Consulta_duracion]![Texto15]))
GROUP BY Parejas.[ID Pareja], Parejas.NombrePareja, Parejas.FechaAlta, Profesional.Nombre;
And the second one is this one:
SELECT Parejas.[ID Pareja], Paciente.Nombre AS [Nombre Pareja / Paciente], Paciente.FechaAlta AS [Fecha de alta], Count(Sesiones.[ID sesión]) AS [CuentaDeID sesión], Profesional.Nombre
FROM (Profesional INNER JOIN (Paciente INNER JOIN Sesiones ON Paciente.[ID Paciente] = Sesiones.[ID Paciente]) ON Profesional.[ID Profesional] = Sesiones.[ID Profesional]) INNER JOIN Parejas ON Paciente.[ID Paciente] = Parejas.Pacientes.Value
WHERE (((Paciente.FechaAlta)>[Formularios]![Consulta_duracion]![Texto15]))
GROUP BY Parejas.[ID Pareja], Paciente.Nombre, Paciente.FechaAlta, Profesional.Nombre;
I use the Union clause to merge both queries, but I always get the following error message:
"your query does not include the specified expression 'ID Pareja" as part of an aggregate function"
May be any of you do have any clue about what the problem is?
Thank you so much!
edit: I attach a image with all the tables and relations. As you can see, "Paciente" and "Sesiones" are the main tables, over which all others are designed.
enter image description here
sql ms-access
asked Nov 23 '18 at 20:13
juanjoalv205juanjoalv205
62
I'm trying to merge two queries with Union Select, but I can't find what I'm doing wrong... I have used this method before with no problem, but there must be something that I don't realize about that is giving me problems.
The two queries I'm trying to merge are as follows:
SELECT Parejas.[ID Pareja], Parejas.NombrePareja AS [Nombre Pareja / Paciente], Parejas.FechaAlta AS [Fecha de alta], Count(Sesiones.[ID sesión]) AS [CuentaDeID sesión], Profesional.Nombre
FROM Paciente INNER JOIN (Profesional INNER JOIN (Parejas INNER JOIN Sesiones ON Parejas.[ID Pareja] = Sesiones.[ID Pareja]) ON Profesional.[ID Profesional] = Sesiones.[ID Profesional]) ON Paciente.[ID Paciente] = Parejas.Pacientes.Value
WHERE (((Parejas.FechaAlta)>[Formularios]![Consulta_duracion]![Texto15]))
GROUP BY Parejas.[ID Pareja], Parejas.NombrePareja, Parejas.FechaAlta, Profesional.Nombre;
And the second one is this one:
SELECT Parejas.[ID Pareja], Paciente.Nombre AS [Nombre Pareja / Paciente], Paciente.FechaAlta AS [Fecha de alta], Count(Sesiones.[ID sesión]) AS [CuentaDeID sesión], Profesional.Nombre
FROM (Profesional INNER JOIN (Paciente INNER JOIN Sesiones ON Paciente.[ID Paciente] = Sesiones.[ID Paciente]) ON Profesional.[ID Profesional] = Sesiones.[ID Profesional]) INNER JOIN Parejas ON Paciente.[ID Paciente] = Parejas.Pacientes.Value
WHERE (((Paciente.FechaAlta)>[Formularios]![Consulta_duracion]![Texto15]))
GROUP BY Parejas.[ID Pareja], Paciente.Nombre, Paciente.FechaAlta, Profesional.Nombre;
I use the Union clause to merge both queries, but I always get the following error message:
"your query does not include the specified expression 'ID Pareja" as part of an aggregate function"
May be any of you do have any clue about what the problem is?
Thank you so much!
edit: I attach a image with all the tables and relations. As you can see, "Paciente" and "Sesiones" are the main tables, over which all others are designed.
enter image description here
sql ms-access
sql ms-access
asked Nov 23 '18 at 20:13
juanjoalv205juanjoalv205
62
asked Nov 23 '18 at 20:13
juanjoalv205juanjoalv205
62
edited Nov 25 '18 at 12:33
juanjoalv205
asked Nov 23 '18 at 20:13
juanjoalv205juanjoalv205
62
asked Nov 23 '18 at 20:13
juanjoalv205juanjoalv205
62
asked Nov 23 '18 at 20:13
juanjoalv205juanjoalv205
62
62
Its problem with uneven id aggregation on unioning both selects try union first then apply group by it should work
– Himanshu Ahuja
Nov 23 '18 at 20:18
Save each query as a query. Then union the two saved queries using a straight union query.
– Gustav
Nov 23 '18 at 21:00
I have saved each one as independent queries, and it doesn't work. They do work separately, but not when I make a union query.
– juanjoalv205
Nov 23 '18 at 21:21
Hi Himanshu, thanks a lot for your comment. How is that? I don't understand what you mean by "union first then apply group by"...
– juanjoalv205
Nov 23 '18 at 21:22
1
With such similar queries, this might be a database design question. You might need normalization to avoid redundancy of records. Can you provide the background "story" of your relational model? What are you capturing between Professional, Patient, Couples, and Session tables? Any 1-to-1, 1-to-many, many-to-many relationships? Please edit post and not answer as a long comment.
– Parfait
Nov 23 '18 at 23:16
|
show 2 more comments
Its problem with uneven id aggregation on unioning both selects try union first then apply group by it should work
– Himanshu Ahuja
Nov 23 '18 at 20:18
Save each query as a query. Then union the two saved queries using a straight union query.
– Gustav
Nov 23 '18 at 21:00
I have saved each one as independent queries, and it doesn't work. They do work separately, but not when I make a union query.
– juanjoalv205
Nov 23 '18 at 21:21
Hi Himanshu, thanks a lot for your comment. How is that? I don't understand what you mean by "union first then apply group by"...
– juanjoalv205
Nov 23 '18 at 21:22
1
With such similar queries, this might be a database design question. You might need normalization to avoid redundancy of records. Can you provide the background "story" of your relational model? What are you capturing between Professional, Patient, Couples, and Session tables? Any 1-to-1, 1-to-many, many-to-many relationships? Please edit post and not answer as a long comment.
– Parfait
Nov 23 '18 at 23:16
Its problem with uneven id aggregation on unioning both selects try union first then apply group by it should work
– Himanshu Ahuja
Nov 23 '18 at 20:18
Its problem with uneven id aggregation on unioning both selects try union first then apply group by it should work
– Himanshu Ahuja
Nov 23 '18 at 20:18
Save each query as a query. Then union the two saved queries using a straight union query.
– Gustav
Nov 23 '18 at 21:00
Save each query as a query. Then union the two saved queries using a straight union query.
– Gustav
Nov 23 '18 at 21:00
I have saved each one as independent queries, and it doesn't work. They do work separately, but not when I make a union query.
– juanjoalv205
Nov 23 '18 at 21:21
I have saved each one as independent queries, and it doesn't work. They do work separately, but not when I make a union query.
– juanjoalv205
Nov 23 '18 at 21:21
Hi Himanshu, thanks a lot for your comment. How is that? I don't understand what you mean by "union first then apply group by"...
– juanjoalv205
Nov 23 '18 at 21:22
Hi Himanshu, thanks a lot for your comment. How is that? I don't understand what you mean by "union first then apply group by"...
– juanjoalv205
Nov 23 '18 at 21:22
1
1
With such similar queries, this might be a database design question. You might need normalization to avoid redundancy of records. Can you provide the background "story" of your relational model? What are you capturing between Professional, Patient, Couples, and Session tables? Any 1-to-1, 1-to-many, many-to-many relationships? Please edit post and not answer as a long comment.
– Parfait
Nov 23 '18 at 23:16
With such similar queries, this might be a database design question. You might need normalization to avoid redundancy of records. Can you provide the background "story" of your relational model? What are you capturing between Professional, Patient, Couples, and Session tables? Any 1-to-1, 1-to-many, many-to-many relationships? Please edit post and not answer as a long comment.
– Parfait
Nov 23 '18 at 23:16
|
show 2 more comments
1 Answer
1
active
oldest
votes
0
I think the following should yield a valid UNION query -
SELECT u.*
FROM
(
SELECT
Parejas.[ID Pareja],
Parejas.NombrePareja AS [Nombre Pareja / Paciente],
Parejas.FechaAlta AS [Fecha de alta],
Count(Sesiones.[ID sesión]) AS [CuentaDeID sesión],
Profesional.Nombre
FROM
Paciente INNER JOIN
(
Profesional INNER JOIN
(
Parejas INNER JOIN Sesiones ON
Parejas.[ID Pareja] = Sesiones.[ID Pareja]
)
ON Profesional.[ID Profesional] = Sesiones.[ID Profesional]
)
ON Paciente.[ID Paciente] = Parejas.Pacientes.Value
WHERE
Parejas.FechaAlta > [Formularios]![Consulta_duracion]![Texto15]
GROUP BY
Parejas.[ID Pareja],
Parejas.NombrePareja,
Parejas.FechaAlta,
Profesional.Nombre
) u
UNION ALL
SELECT v.*
FROM
(
SELECT
Parejas.[ID Pareja],
Paciente.Nombre AS [Nombre Pareja / Paciente],
Paciente.FechaAlta AS [Fecha de alta],
Count(Sesiones.[ID sesión]) AS [CuentaDeID sesión],
Profesional.Nombre
FROM
(
Profesional INNER JOIN
(
Paciente INNER JOIN Sesiones ON
Paciente.[ID Paciente] = Sesiones.[ID Paciente]
)
ON Profesional.[ID Profesional] = Sesiones.[ID Profesional]
)
INNER JOIN Parejas ON
Paciente.[ID Paciente] = Parejas.Pacientes.Value
WHERE
Paciente.FechaAlta > [Formularios]![Consulta_duracion]![Texto15]
GROUP BY
Parejas.[ID Pareja],
Paciente.Nombre,
Paciente.FechaAlta,
Profesional.Nombre
) v
(Untested of course)
answered Nov 23 '18 at 22:49
Lee MacLee Mac
3,62131339
I think this proposal is near to getting it. When I try it, it doesn't work, saying "access syntax error missing operator in query expression "CuentaDeID sesión" ". I have tryied to do it without the Count(Sesiones.[ID sesión]) and then it works (at least it shows all the registers with the selected filters), but without counting the total number of registers, whis is precisely the most important part. May be there is some problem with an operator or something?
– juanjoalv205
Nov 24 '18 at 12:57
@juanjoalv205 I can't see any syntax errors in the SQL I have posted, and so do you receive that error when running the query in my post in isolation, or are you running another query which references the above query?
– Lee Mac
Nov 24 '18 at 13:01
No, I'm running it in isolation and it returns "syntax error"... I'm really clueless
– juanjoalv205
Nov 25 '18 at 12:20
@juanjoalv205 Try changingCount(Sesiones.[ID sesión])toCount(*)in both queries; though, this could yield different results ifSesiones.[ID sesión]can be null.
– Lee Mac
Nov 25 '18 at 12:33
No, it still doesnt work. Besides, if in Count(Sesiones.[ID sesión]) y change it to Count(*), I can also delete the statement AS [CuentaDeID sesión], then the error mesage changes to "Your query does not include the specified expresion 'ID Pareja' as part of an aggregated function"
– juanjoalv205
Nov 25 '18 at 12:40
|
show 1 more 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
});
}
});
draft saved
draft discarded
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%2f53452473%2fmerge-queries-with-union-select%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
0
I think the following should yield a valid UNION query -
SELECT u.*
FROM
(
SELECT
Parejas.[ID Pareja],
Parejas.NombrePareja AS [Nombre Pareja / Paciente],
Parejas.FechaAlta AS [Fecha de alta],
Count(Sesiones.[ID sesión]) AS [CuentaDeID sesión],
Profesional.Nombre
FROM
Paciente INNER JOIN
(
Profesional INNER JOIN
(
Parejas INNER JOIN Sesiones ON
Parejas.[ID Pareja] = Sesiones.[ID Pareja]
)
ON Profesional.[ID Profesional] = Sesiones.[ID Profesional]
)
ON Paciente.[ID Paciente] = Parejas.Pacientes.Value
WHERE
Parejas.FechaAlta > [Formularios]![Consulta_duracion]![Texto15]
GROUP BY
Parejas.[ID Pareja],
Parejas.NombrePareja,
Parejas.FechaAlta,
Profesional.Nombre
) u
UNION ALL
SELECT v.*
FROM
(
SELECT
Parejas.[ID Pareja],
Paciente.Nombre AS [Nombre Pareja / Paciente],
Paciente.FechaAlta AS [Fecha de alta],
Count(Sesiones.[ID sesión]) AS [CuentaDeID sesión],
Profesional.Nombre
FROM
(
Profesional INNER JOIN
(
Paciente INNER JOIN Sesiones ON
Paciente.[ID Paciente] = Sesiones.[ID Paciente]
)
ON Profesional.[ID Profesional] = Sesiones.[ID Profesional]
)
INNER JOIN Parejas ON
Paciente.[ID Paciente] = Parejas.Pacientes.Value
WHERE
Paciente.FechaAlta > [Formularios]![Consulta_duracion]![Texto15]
GROUP BY
Parejas.[ID Pareja],
Paciente.Nombre,
Paciente.FechaAlta,
Profesional.Nombre
) v
(Untested of course)
answered Nov 23 '18 at 22:49
Lee MacLee Mac
3,62131339
I think this proposal is near to getting it. When I try it, it doesn't work, saying "access syntax error missing operator in query expression "CuentaDeID sesión" ". I have tryied to do it without the Count(Sesiones.[ID sesión]) and then it works (at least it shows all the registers with the selected filters), but without counting the total number of registers, whis is precisely the most important part. May be there is some problem with an operator or something?
– juanjoalv205
Nov 24 '18 at 12:57
@juanjoalv205 I can't see any syntax errors in the SQL I have posted, and so do you receive that error when running the query in my post in isolation, or are you running another query which references the above query?
– Lee Mac
Nov 24 '18 at 13:01
No, I'm running it in isolation and it returns "syntax error"... I'm really clueless
– juanjoalv205
Nov 25 '18 at 12:20
@juanjoalv205 Try changingCount(Sesiones.[ID sesión])toCount(*)in both queries; though, this could yield different results ifSesiones.[ID sesión]can be null.
– Lee Mac
Nov 25 '18 at 12:33
No, it still doesnt work. Besides, if in Count(Sesiones.[ID sesión]) y change it to Count(*), I can also delete the statement AS [CuentaDeID sesión], then the error mesage changes to "Your query does not include the specified expresion 'ID Pareja' as part of an aggregated function"
– juanjoalv205
Nov 25 '18 at 12:40
|
show 1 more comment
0
I think the following should yield a valid UNION query -
SELECT u.*
FROM
(
SELECT
Parejas.[ID Pareja],
Parejas.NombrePareja AS [Nombre Pareja / Paciente],
Parejas.FechaAlta AS [Fecha de alta],
Count(Sesiones.[ID sesión]) AS [CuentaDeID sesión],
Profesional.Nombre
FROM
Paciente INNER JOIN
(
Profesional INNER JOIN
(
Parejas INNER JOIN Sesiones ON
Parejas.[ID Pareja] = Sesiones.[ID Pareja]
)
ON Profesional.[ID Profesional] = Sesiones.[ID Profesional]
)
ON Paciente.[ID Paciente] = Parejas.Pacientes.Value
WHERE
Parejas.FechaAlta > [Formularios]![Consulta_duracion]![Texto15]
GROUP BY
Parejas.[ID Pareja],
Parejas.NombrePareja,
Parejas.FechaAlta,
Profesional.Nombre
) u
UNION ALL
SELECT v.*
FROM
(
SELECT
Parejas.[ID Pareja],
Paciente.Nombre AS [Nombre Pareja / Paciente],
Paciente.FechaAlta AS [Fecha de alta],
Count(Sesiones.[ID sesión]) AS [CuentaDeID sesión],
Profesional.Nombre
FROM
(
Profesional INNER JOIN
(
Paciente INNER JOIN Sesiones ON
Paciente.[ID Paciente] = Sesiones.[ID Paciente]
)
ON Profesional.[ID Profesional] = Sesiones.[ID Profesional]
)
INNER JOIN Parejas ON
Paciente.[ID Paciente] = Parejas.Pacientes.Value
WHERE
Paciente.FechaAlta > [Formularios]![Consulta_duracion]![Texto15]
GROUP BY
Parejas.[ID Pareja],
Paciente.Nombre,
Paciente.FechaAlta,
Profesional.Nombre
) v
(Untested of course)
answered Nov 23 '18 at 22:49
Lee MacLee Mac
3,62131339
I think this proposal is near to getting it. When I try it, it doesn't work, saying "access syntax error missing operator in query expression "CuentaDeID sesión" ". I have tryied to do it without the Count(Sesiones.[ID sesión]) and then it works (at least it shows all the registers with the selected filters), but without counting the total number of registers, whis is precisely the most important part. May be there is some problem with an operator or something?
– juanjoalv205
Nov 24 '18 at 12:57
@juanjoalv205 I can't see any syntax errors in the SQL I have posted, and so do you receive that error when running the query in my post in isolation, or are you running another query which references the above query?
– Lee Mac
Nov 24 '18 at 13:01
No, I'm running it in isolation and it returns "syntax error"... I'm really clueless
– juanjoalv205
Nov 25 '18 at 12:20
@juanjoalv205 Try changingCount(Sesiones.[ID sesión])toCount(*)in both queries; though, this could yield different results ifSesiones.[ID sesión]can be null.
– Lee Mac
Nov 25 '18 at 12:33
No, it still doesnt work. Besides, if in Count(Sesiones.[ID sesión]) y change it to Count(*), I can also delete the statement AS [CuentaDeID sesión], then the error mesage changes to "Your query does not include the specified expresion 'ID Pareja' as part of an aggregated function"
– juanjoalv205
Nov 25 '18 at 12:40
|
show 1 more comment
0
0
0
I think the following should yield a valid UNION query -
SELECT u.*
FROM
(
SELECT
Parejas.[ID Pareja],
Parejas.NombrePareja AS [Nombre Pareja / Paciente],
Parejas.FechaAlta AS [Fecha de alta],
Count(Sesiones.[ID sesión]) AS [CuentaDeID sesión],
Profesional.Nombre
FROM
Paciente INNER JOIN
(
Profesional INNER JOIN
(
Parejas INNER JOIN Sesiones ON
Parejas.[ID Pareja] = Sesiones.[ID Pareja]
)
ON Profesional.[ID Profesional] = Sesiones.[ID Profesional]
)
ON Paciente.[ID Paciente] = Parejas.Pacientes.Value
WHERE
Parejas.FechaAlta > [Formularios]![Consulta_duracion]![Texto15]
GROUP BY
Parejas.[ID Pareja],
Parejas.NombrePareja,
Parejas.FechaAlta,
Profesional.Nombre
) u
UNION ALL
SELECT v.*
FROM
(
SELECT
Parejas.[ID Pareja],
Paciente.Nombre AS [Nombre Pareja / Paciente],
Paciente.FechaAlta AS [Fecha de alta],
Count(Sesiones.[ID sesión]) AS [CuentaDeID sesión],
Profesional.Nombre
FROM
(
Profesional INNER JOIN
(
Paciente INNER JOIN Sesiones ON
Paciente.[ID Paciente] = Sesiones.[ID Paciente]
)
ON Profesional.[ID Profesional] = Sesiones.[ID Profesional]
)
INNER JOIN Parejas ON
Paciente.[ID Paciente] = Parejas.Pacientes.Value
WHERE
Paciente.FechaAlta > [Formularios]![Consulta_duracion]![Texto15]
GROUP BY
Parejas.[ID Pareja],
Paciente.Nombre,
Paciente.FechaAlta,
Profesional.Nombre
) v
(Untested of course)
answered Nov 23 '18 at 22:49
Lee MacLee Mac
3,62131339
I think the following should yield a valid UNION query -
SELECT u.*
FROM
(
SELECT
Parejas.[ID Pareja],
Parejas.NombrePareja AS [Nombre Pareja / Paciente],
Parejas.FechaAlta AS [Fecha de alta],
Count(Sesiones.[ID sesión]) AS [CuentaDeID sesión],
Profesional.Nombre
FROM
Paciente INNER JOIN
(
Profesional INNER JOIN
(
Parejas INNER JOIN Sesiones ON
Parejas.[ID Pareja] = Sesiones.[ID Pareja]
)
ON Profesional.[ID Profesional] = Sesiones.[ID Profesional]
)
ON Paciente.[ID Paciente] = Parejas.Pacientes.Value
WHERE
Parejas.FechaAlta > [Formularios]![Consulta_duracion]![Texto15]
GROUP BY
Parejas.[ID Pareja],
Parejas.NombrePareja,
Parejas.FechaAlta,
Profesional.Nombre
) u
UNION ALL
SELECT v.*
FROM
(
SELECT
Parejas.[ID Pareja],
Paciente.Nombre AS [Nombre Pareja / Paciente],
Paciente.FechaAlta AS [Fecha de alta],
Count(Sesiones.[ID sesión]) AS [CuentaDeID sesión],
Profesional.Nombre
FROM
(
Profesional INNER JOIN
(
Paciente INNER JOIN Sesiones ON
Paciente.[ID Paciente] = Sesiones.[ID Paciente]
)
ON Profesional.[ID Profesional] = Sesiones.[ID Profesional]
)
INNER JOIN Parejas ON
Paciente.[ID Paciente] = Parejas.Pacientes.Value
WHERE
Paciente.FechaAlta > [Formularios]![Consulta_duracion]![Texto15]
GROUP BY
Parejas.[ID Pareja],
Paciente.Nombre,
Paciente.FechaAlta,
Profesional.Nombre
) v
(Untested of course)
answered Nov 23 '18 at 22:49
Lee MacLee Mac
3,62131339
answered Nov 23 '18 at 22:49
Lee MacLee Mac
3,62131339
answered Nov 23 '18 at 22:49
Lee MacLee Mac
3,62131339
answered Nov 23 '18 at 22:49
Lee MacLee Mac
3,62131339
3,62131339
I think this proposal is near to getting it. When I try it, it doesn't work, saying "access syntax error missing operator in query expression "CuentaDeID sesión" ". I have tryied to do it without the Count(Sesiones.[ID sesión]) and then it works (at least it shows all the registers with the selected filters), but without counting the total number of registers, whis is precisely the most important part. May be there is some problem with an operator or something?
– juanjoalv205
Nov 24 '18 at 12:57
@juanjoalv205 I can't see any syntax errors in the SQL I have posted, and so do you receive that error when running the query in my post in isolation, or are you running another query which references the above query?
– Lee Mac
Nov 24 '18 at 13:01
No, I'm running it in isolation and it returns "syntax error"... I'm really clueless
– juanjoalv205
Nov 25 '18 at 12:20
@juanjoalv205 Try changingCount(Sesiones.[ID sesión])toCount(*)in both queries; though, this could yield different results ifSesiones.[ID sesión]can be null.
– Lee Mac
Nov 25 '18 at 12:33
No, it still doesnt work. Besides, if in Count(Sesiones.[ID sesión]) y change it to Count(*), I can also delete the statement AS [CuentaDeID sesión], then the error mesage changes to "Your query does not include the specified expresion 'ID Pareja' as part of an aggregated function"
– juanjoalv205
Nov 25 '18 at 12:40
|
show 1 more comment
I think this proposal is near to getting it. When I try it, it doesn't work, saying "access syntax error missing operator in query expression "CuentaDeID sesión" ". I have tryied to do it without the Count(Sesiones.[ID sesión]) and then it works (at least it shows all the registers with the selected filters), but without counting the total number of registers, whis is precisely the most important part. May be there is some problem with an operator or something?
– juanjoalv205
Nov 24 '18 at 12:57
@juanjoalv205 I can't see any syntax errors in the SQL I have posted, and so do you receive that error when running the query in my post in isolation, or are you running another query which references the above query?
– Lee Mac
Nov 24 '18 at 13:01
No, I'm running it in isolation and it returns "syntax error"... I'm really clueless
– juanjoalv205
Nov 25 '18 at 12:20
@juanjoalv205 Try changingCount(Sesiones.[ID sesión])toCount(*)in both queries; though, this could yield different results ifSesiones.[ID sesión]can be null.
– Lee Mac
Nov 25 '18 at 12:33
No, it still doesnt work. Besides, if in Count(Sesiones.[ID sesión]) y change it to Count(*), I can also delete the statement AS [CuentaDeID sesión], then the error mesage changes to "Your query does not include the specified expresion 'ID Pareja' as part of an aggregated function"
– juanjoalv205
Nov 25 '18 at 12:40
I think this proposal is near to getting it. When I try it, it doesn't work, saying "access syntax error missing operator in query expression "CuentaDeID sesión" ". I have tryied to do it without the Count(Sesiones.[ID sesión]) and then it works (at least it shows all the registers with the selected filters), but without counting the total number of registers, whis is precisely the most important part. May be there is some problem with an operator or something?
– juanjoalv205
Nov 24 '18 at 12:57
I think this proposal is near to getting it. When I try it, it doesn't work, saying "access syntax error missing operator in query expression "CuentaDeID sesión" ". I have tryied to do it without the Count(Sesiones.[ID sesión]) and then it works (at least it shows all the registers with the selected filters), but without counting the total number of registers, whis is precisely the most important part. May be there is some problem with an operator or something?
– juanjoalv205
Nov 24 '18 at 12:57
@juanjoalv205 I can't see any syntax errors in the SQL I have posted, and so do you receive that error when running the query in my post in isolation, or are you running another query which references the above query?
– Lee Mac
Nov 24 '18 at 13:01
@juanjoalv205 I can't see any syntax errors in the SQL I have posted, and so do you receive that error when running the query in my post in isolation, or are you running another query which references the above query?
– Lee Mac
Nov 24 '18 at 13:01
No, I'm running it in isolation and it returns "syntax error"... I'm really clueless
– juanjoalv205
Nov 25 '18 at 12:20
No, I'm running it in isolation and it returns "syntax error"... I'm really clueless
– juanjoalv205
Nov 25 '18 at 12:20
@juanjoalv205 Try changing
Count(Sesiones.[ID sesión]) to Count(*) in both queries; though, this could yield different results if Sesiones.[ID sesión] can be null.– Lee Mac
Nov 25 '18 at 12:33
@juanjoalv205 Try changing
Count(Sesiones.[ID sesión]) to Count(*) in both queries; though, this could yield different results if Sesiones.[ID sesión] can be null.– Lee Mac
Nov 25 '18 at 12:33
No, it still doesnt work. Besides, if in Count(Sesiones.[ID sesión]) y change it to Count(*), I can also delete the statement AS [CuentaDeID sesión], then the error mesage changes to "Your query does not include the specified expresion 'ID Pareja' as part of an aggregated function"
– juanjoalv205
Nov 25 '18 at 12:40
No, it still doesnt work. Besides, if in Count(Sesiones.[ID sesión]) y change it to Count(*), I can also delete the statement AS [CuentaDeID sesión], then the error mesage changes to "Your query does not include the specified expresion 'ID Pareja' as part of an aggregated function"
– juanjoalv205
Nov 25 '18 at 12:40
|
show 1 more comment
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53452473%2fmerge-queries-with-union-select%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
Its problem with uneven id aggregation on unioning both selects try union first then apply group by it should work
– Himanshu Ahuja
Nov 23 '18 at 20:18
Save each query as a query. Then union the two saved queries using a straight union query.
– Gustav
Nov 23 '18 at 21:00
I have saved each one as independent queries, and it doesn't work. They do work separately, but not when I make a union query.
– juanjoalv205
Nov 23 '18 at 21:21
Hi Himanshu, thanks a lot for your comment. How is that? I don't understand what you mean by "union first then apply group by"...
– juanjoalv205
Nov 23 '18 at 21:22
1
With such similar queries, this might be a database design question. You might need normalization to avoid redundancy of records. Can you provide the background "story" of your relational model? What are you capturing between Professional, Patient, Couples, and Session tables? Any 1-to-1, 1-to-many, many-to-many relationships? Please edit post and not answer as a long comment.
– Parfait
Nov 23 '18 at 23:16