MS Access 2013, How to add totals row within SQL
I'm in need of some assistance. I have search and not found what I'm looking for. I have an assigment for school that requires me to use SQL. I have a query that pulls some colunms from two tables:
SELECT Course.CourseNo, Course.CrHrs, Sections.Yr, Sections.Term, Sections.Location
FROM Course
INNER JOIN Sections ON Course.CourseNo = Sections.CourseNo
WHERE Sections.Term="spring";
I need to add a Totals
row at the bottom to count the CourseNo
and Sum the CrHrs
. It has to be done through SQL query design as I need to paste the code. I know it can be done with the datasheet view but she will not accept that. Any advice?
sql ms-access
add a comment |
I'm in need of some assistance. I have search and not found what I'm looking for. I have an assigment for school that requires me to use SQL. I have a query that pulls some colunms from two tables:
SELECT Course.CourseNo, Course.CrHrs, Sections.Yr, Sections.Term, Sections.Location
FROM Course
INNER JOIN Sections ON Course.CourseNo = Sections.CourseNo
WHERE Sections.Term="spring";
I need to add a Totals
row at the bottom to count the CourseNo
and Sum the CrHrs
. It has to be done through SQL query design as I need to paste the code. I know it can be done with the datasheet view but she will not accept that. Any advice?
sql ms-access
which column(s) are you trying to total up? And what kind of "total" aggregation? (Sum, Count, etc)?
– ArcherBird
Nov 19 '18 at 16:50
add a comment |
I'm in need of some assistance. I have search and not found what I'm looking for. I have an assigment for school that requires me to use SQL. I have a query that pulls some colunms from two tables:
SELECT Course.CourseNo, Course.CrHrs, Sections.Yr, Sections.Term, Sections.Location
FROM Course
INNER JOIN Sections ON Course.CourseNo = Sections.CourseNo
WHERE Sections.Term="spring";
I need to add a Totals
row at the bottom to count the CourseNo
and Sum the CrHrs
. It has to be done through SQL query design as I need to paste the code. I know it can be done with the datasheet view but she will not accept that. Any advice?
sql ms-access
I'm in need of some assistance. I have search and not found what I'm looking for. I have an assigment for school that requires me to use SQL. I have a query that pulls some colunms from two tables:
SELECT Course.CourseNo, Course.CrHrs, Sections.Yr, Sections.Term, Sections.Location
FROM Course
INNER JOIN Sections ON Course.CourseNo = Sections.CourseNo
WHERE Sections.Term="spring";
I need to add a Totals
row at the bottom to count the CourseNo
and Sum the CrHrs
. It has to be done through SQL query design as I need to paste the code. I know it can be done with the datasheet view but she will not accept that. Any advice?
sql ms-access
sql ms-access
asked Nov 19 '18 at 16:45
Jared HammelJared Hammel
53
53
which column(s) are you trying to total up? And what kind of "total" aggregation? (Sum, Count, etc)?
– ArcherBird
Nov 19 '18 at 16:50
add a comment |
which column(s) are you trying to total up? And what kind of "total" aggregation? (Sum, Count, etc)?
– ArcherBird
Nov 19 '18 at 16:50
which column(s) are you trying to total up? And what kind of "total" aggregation? (Sum, Count, etc)?
– ArcherBird
Nov 19 '18 at 16:50
which column(s) are you trying to total up? And what kind of "total" aggregation? (Sum, Count, etc)?
– ArcherBird
Nov 19 '18 at 16:50
add a comment |
3 Answers
3
active
oldest
votes
To accomplish this, you can union your query together with an aggregation query. Its not clear from your question which columns you are trying to get "Totals" from, but here's an example of what I mean using your query and getting counts of each (kind of useless example - but you should be able to apply to what you are doing):
SELECT
[Course].[CourseNo]
, [Course].[CrHrs]
, [Sections].[Yr]
, [Sections].[Term]
, [Sections].[Location]
FROM
[Course]
INNER JOIN [Sections] ON [Course].[CourseNo] = [Sections].[CourseNo]
WHERE [Sections].[Term] = [spring]
UNION ALL
SELECT
"TOTALS"
, SUM([Course].[CrHrs])
, count([Sections].[Yr])
, Count([Sections].[Term])
, Count([Sections].[Location])
FROM
[Course]
INNER JOIN [Sections] ON [Course].[CourseNo] = [Sections].[CourseNo]
WHERE [Sections].[Term] = “spring”
This is exactly what I need! Perfect look! It doesn't sum the CrHrs though, I tried adding the line in the second select statement but could not get it to work. Is it possible to sum that column while counting the others? Thank you for your reply!!
– Jared Hammel
Nov 19 '18 at 21:01
updated my answer to add the SUM with the CrHrs column. It should be able to sum that one and count the other columns, yes. If that is not working, are you getting an error, or is it just not returning the answer you expect?
– ArcherBird
Nov 19 '18 at 21:05
That is perfect!! Thank you so much!!! I did have to encase the [spring] in quotes instead of brackets though. This was a huge help!
– Jared Hammel
Nov 19 '18 at 21:06
add a comment |
You can prepare your "total" query separately, and then output both query results together with "UNION".
It might look like:
SELECT Course.CourseNo, Course.CrHrs, Sections.Yr, Sections.Term, Sections.Location
FROM Course
INNER JOIN Sections ON Course.CourseNo = Sections.CourseNo
WHERE Sections.Term="spring"
UNION
SELECT "Total", SUM(Course.CrHrs), SUM(Sections.Yr), SUM(Sections.Term), SUM(Sections.Location)
FROM Course
INNER JOIN Sections ON Course.CourseNo = Sections.CourseNo
WHERE Sections.Term="spring";
I get an error when I run this that says "Data type mismatch in criteria expression."
– Jared Hammel
Nov 19 '18 at 20:59
That's highly possible because I didn't know your data types, so I put the "SUM" randomly. It should not be to too difficult to correct it I guess. However I agree with Lee Mac regarding the fact that it is a bad practice to mix data and aggregated data within the same query result.
– FloT
Nov 20 '18 at 9:01
add a comment |
Whilst you can certainly union
the aggregated totals query to the end of your original query, in my opinion this would be really bad practice and would be undesirable for any real-world application.
Consider that the resulting query could no longer be used for any meaningful analysis of the data: if displayed in a datagrid, the user would not be able to sort the data without the totals row being interspersed amongst the rest of the data; the user could no longer use the built-in Totals
option to perform their own aggregate operation, and the insertion of a row only identifiable by the term totals
could even conflict with other data within the set.
Instead, I would suggest displaying the totals within an entirely separate form control, using a separate query such as the following (based on your own example):
SELECT Count(Course.CourseNo) as Courses, Sum(Course.CrHrs) as Hours
FROM Course INNER JOIN Sections ON Course.CourseNo = Sections.CourseNo
WHERE Sections.Term = "spring";
However, since CrHrs
are fields within your Course
table and not within your Sections
table, the above may yield multiples of the desired result, with the number of hours multiplied by the number of corresponding records in the Sections
table.
If this is the case, the following may be more suitable:
SELECT Count(Course.CourseNo) as Courses, Sum(Course.CrHrs) as Hours
FROM
Course INNER JOIN
(SELECT DISTINCT s.CourseNo FROM Sections s WHERE s.Term = "spring") q
ON Course.CourseNo = q.CourseNo
Thank you for the reply. I like this code you post, but she requires all data to be shown. I will use this in future projects though!
– Jared Hammel
Nov 19 '18 at 20:59
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%2f53379174%2fms-access-2013-how-to-add-totals-row-within-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
To accomplish this, you can union your query together with an aggregation query. Its not clear from your question which columns you are trying to get "Totals" from, but here's an example of what I mean using your query and getting counts of each (kind of useless example - but you should be able to apply to what you are doing):
SELECT
[Course].[CourseNo]
, [Course].[CrHrs]
, [Sections].[Yr]
, [Sections].[Term]
, [Sections].[Location]
FROM
[Course]
INNER JOIN [Sections] ON [Course].[CourseNo] = [Sections].[CourseNo]
WHERE [Sections].[Term] = [spring]
UNION ALL
SELECT
"TOTALS"
, SUM([Course].[CrHrs])
, count([Sections].[Yr])
, Count([Sections].[Term])
, Count([Sections].[Location])
FROM
[Course]
INNER JOIN [Sections] ON [Course].[CourseNo] = [Sections].[CourseNo]
WHERE [Sections].[Term] = “spring”
This is exactly what I need! Perfect look! It doesn't sum the CrHrs though, I tried adding the line in the second select statement but could not get it to work. Is it possible to sum that column while counting the others? Thank you for your reply!!
– Jared Hammel
Nov 19 '18 at 21:01
updated my answer to add the SUM with the CrHrs column. It should be able to sum that one and count the other columns, yes. If that is not working, are you getting an error, or is it just not returning the answer you expect?
– ArcherBird
Nov 19 '18 at 21:05
That is perfect!! Thank you so much!!! I did have to encase the [spring] in quotes instead of brackets though. This was a huge help!
– Jared Hammel
Nov 19 '18 at 21:06
add a comment |
To accomplish this, you can union your query together with an aggregation query. Its not clear from your question which columns you are trying to get "Totals" from, but here's an example of what I mean using your query and getting counts of each (kind of useless example - but you should be able to apply to what you are doing):
SELECT
[Course].[CourseNo]
, [Course].[CrHrs]
, [Sections].[Yr]
, [Sections].[Term]
, [Sections].[Location]
FROM
[Course]
INNER JOIN [Sections] ON [Course].[CourseNo] = [Sections].[CourseNo]
WHERE [Sections].[Term] = [spring]
UNION ALL
SELECT
"TOTALS"
, SUM([Course].[CrHrs])
, count([Sections].[Yr])
, Count([Sections].[Term])
, Count([Sections].[Location])
FROM
[Course]
INNER JOIN [Sections] ON [Course].[CourseNo] = [Sections].[CourseNo]
WHERE [Sections].[Term] = “spring”
This is exactly what I need! Perfect look! It doesn't sum the CrHrs though, I tried adding the line in the second select statement but could not get it to work. Is it possible to sum that column while counting the others? Thank you for your reply!!
– Jared Hammel
Nov 19 '18 at 21:01
updated my answer to add the SUM with the CrHrs column. It should be able to sum that one and count the other columns, yes. If that is not working, are you getting an error, or is it just not returning the answer you expect?
– ArcherBird
Nov 19 '18 at 21:05
That is perfect!! Thank you so much!!! I did have to encase the [spring] in quotes instead of brackets though. This was a huge help!
– Jared Hammel
Nov 19 '18 at 21:06
add a comment |
To accomplish this, you can union your query together with an aggregation query. Its not clear from your question which columns you are trying to get "Totals" from, but here's an example of what I mean using your query and getting counts of each (kind of useless example - but you should be able to apply to what you are doing):
SELECT
[Course].[CourseNo]
, [Course].[CrHrs]
, [Sections].[Yr]
, [Sections].[Term]
, [Sections].[Location]
FROM
[Course]
INNER JOIN [Sections] ON [Course].[CourseNo] = [Sections].[CourseNo]
WHERE [Sections].[Term] = [spring]
UNION ALL
SELECT
"TOTALS"
, SUM([Course].[CrHrs])
, count([Sections].[Yr])
, Count([Sections].[Term])
, Count([Sections].[Location])
FROM
[Course]
INNER JOIN [Sections] ON [Course].[CourseNo] = [Sections].[CourseNo]
WHERE [Sections].[Term] = “spring”
To accomplish this, you can union your query together with an aggregation query. Its not clear from your question which columns you are trying to get "Totals" from, but here's an example of what I mean using your query and getting counts of each (kind of useless example - but you should be able to apply to what you are doing):
SELECT
[Course].[CourseNo]
, [Course].[CrHrs]
, [Sections].[Yr]
, [Sections].[Term]
, [Sections].[Location]
FROM
[Course]
INNER JOIN [Sections] ON [Course].[CourseNo] = [Sections].[CourseNo]
WHERE [Sections].[Term] = [spring]
UNION ALL
SELECT
"TOTALS"
, SUM([Course].[CrHrs])
, count([Sections].[Yr])
, Count([Sections].[Term])
, Count([Sections].[Location])
FROM
[Course]
INNER JOIN [Sections] ON [Course].[CourseNo] = [Sections].[CourseNo]
WHERE [Sections].[Term] = “spring”
edited Nov 20 '18 at 1:19
answered Nov 19 '18 at 16:54
ArcherBirdArcherBird
783219
783219
This is exactly what I need! Perfect look! It doesn't sum the CrHrs though, I tried adding the line in the second select statement but could not get it to work. Is it possible to sum that column while counting the others? Thank you for your reply!!
– Jared Hammel
Nov 19 '18 at 21:01
updated my answer to add the SUM with the CrHrs column. It should be able to sum that one and count the other columns, yes. If that is not working, are you getting an error, or is it just not returning the answer you expect?
– ArcherBird
Nov 19 '18 at 21:05
That is perfect!! Thank you so much!!! I did have to encase the [spring] in quotes instead of brackets though. This was a huge help!
– Jared Hammel
Nov 19 '18 at 21:06
add a comment |
This is exactly what I need! Perfect look! It doesn't sum the CrHrs though, I tried adding the line in the second select statement but could not get it to work. Is it possible to sum that column while counting the others? Thank you for your reply!!
– Jared Hammel
Nov 19 '18 at 21:01
updated my answer to add the SUM with the CrHrs column. It should be able to sum that one and count the other columns, yes. If that is not working, are you getting an error, or is it just not returning the answer you expect?
– ArcherBird
Nov 19 '18 at 21:05
That is perfect!! Thank you so much!!! I did have to encase the [spring] in quotes instead of brackets though. This was a huge help!
– Jared Hammel
Nov 19 '18 at 21:06
This is exactly what I need! Perfect look! It doesn't sum the CrHrs though, I tried adding the line in the second select statement but could not get it to work. Is it possible to sum that column while counting the others? Thank you for your reply!!
– Jared Hammel
Nov 19 '18 at 21:01
This is exactly what I need! Perfect look! It doesn't sum the CrHrs though, I tried adding the line in the second select statement but could not get it to work. Is it possible to sum that column while counting the others? Thank you for your reply!!
– Jared Hammel
Nov 19 '18 at 21:01
updated my answer to add the SUM with the CrHrs column. It should be able to sum that one and count the other columns, yes. If that is not working, are you getting an error, or is it just not returning the answer you expect?
– ArcherBird
Nov 19 '18 at 21:05
updated my answer to add the SUM with the CrHrs column. It should be able to sum that one and count the other columns, yes. If that is not working, are you getting an error, or is it just not returning the answer you expect?
– ArcherBird
Nov 19 '18 at 21:05
That is perfect!! Thank you so much!!! I did have to encase the [spring] in quotes instead of brackets though. This was a huge help!
– Jared Hammel
Nov 19 '18 at 21:06
That is perfect!! Thank you so much!!! I did have to encase the [spring] in quotes instead of brackets though. This was a huge help!
– Jared Hammel
Nov 19 '18 at 21:06
add a comment |
You can prepare your "total" query separately, and then output both query results together with "UNION".
It might look like:
SELECT Course.CourseNo, Course.CrHrs, Sections.Yr, Sections.Term, Sections.Location
FROM Course
INNER JOIN Sections ON Course.CourseNo = Sections.CourseNo
WHERE Sections.Term="spring"
UNION
SELECT "Total", SUM(Course.CrHrs), SUM(Sections.Yr), SUM(Sections.Term), SUM(Sections.Location)
FROM Course
INNER JOIN Sections ON Course.CourseNo = Sections.CourseNo
WHERE Sections.Term="spring";
I get an error when I run this that says "Data type mismatch in criteria expression."
– Jared Hammel
Nov 19 '18 at 20:59
That's highly possible because I didn't know your data types, so I put the "SUM" randomly. It should not be to too difficult to correct it I guess. However I agree with Lee Mac regarding the fact that it is a bad practice to mix data and aggregated data within the same query result.
– FloT
Nov 20 '18 at 9:01
add a comment |
You can prepare your "total" query separately, and then output both query results together with "UNION".
It might look like:
SELECT Course.CourseNo, Course.CrHrs, Sections.Yr, Sections.Term, Sections.Location
FROM Course
INNER JOIN Sections ON Course.CourseNo = Sections.CourseNo
WHERE Sections.Term="spring"
UNION
SELECT "Total", SUM(Course.CrHrs), SUM(Sections.Yr), SUM(Sections.Term), SUM(Sections.Location)
FROM Course
INNER JOIN Sections ON Course.CourseNo = Sections.CourseNo
WHERE Sections.Term="spring";
I get an error when I run this that says "Data type mismatch in criteria expression."
– Jared Hammel
Nov 19 '18 at 20:59
That's highly possible because I didn't know your data types, so I put the "SUM" randomly. It should not be to too difficult to correct it I guess. However I agree with Lee Mac regarding the fact that it is a bad practice to mix data and aggregated data within the same query result.
– FloT
Nov 20 '18 at 9:01
add a comment |
You can prepare your "total" query separately, and then output both query results together with "UNION".
It might look like:
SELECT Course.CourseNo, Course.CrHrs, Sections.Yr, Sections.Term, Sections.Location
FROM Course
INNER JOIN Sections ON Course.CourseNo = Sections.CourseNo
WHERE Sections.Term="spring"
UNION
SELECT "Total", SUM(Course.CrHrs), SUM(Sections.Yr), SUM(Sections.Term), SUM(Sections.Location)
FROM Course
INNER JOIN Sections ON Course.CourseNo = Sections.CourseNo
WHERE Sections.Term="spring";
You can prepare your "total" query separately, and then output both query results together with "UNION".
It might look like:
SELECT Course.CourseNo, Course.CrHrs, Sections.Yr, Sections.Term, Sections.Location
FROM Course
INNER JOIN Sections ON Course.CourseNo = Sections.CourseNo
WHERE Sections.Term="spring"
UNION
SELECT "Total", SUM(Course.CrHrs), SUM(Sections.Yr), SUM(Sections.Term), SUM(Sections.Location)
FROM Course
INNER JOIN Sections ON Course.CourseNo = Sections.CourseNo
WHERE Sections.Term="spring";
answered Nov 19 '18 at 16:57
FloTFloT
254110
254110
I get an error when I run this that says "Data type mismatch in criteria expression."
– Jared Hammel
Nov 19 '18 at 20:59
That's highly possible because I didn't know your data types, so I put the "SUM" randomly. It should not be to too difficult to correct it I guess. However I agree with Lee Mac regarding the fact that it is a bad practice to mix data and aggregated data within the same query result.
– FloT
Nov 20 '18 at 9:01
add a comment |
I get an error when I run this that says "Data type mismatch in criteria expression."
– Jared Hammel
Nov 19 '18 at 20:59
That's highly possible because I didn't know your data types, so I put the "SUM" randomly. It should not be to too difficult to correct it I guess. However I agree with Lee Mac regarding the fact that it is a bad practice to mix data and aggregated data within the same query result.
– FloT
Nov 20 '18 at 9:01
I get an error when I run this that says "Data type mismatch in criteria expression."
– Jared Hammel
Nov 19 '18 at 20:59
I get an error when I run this that says "Data type mismatch in criteria expression."
– Jared Hammel
Nov 19 '18 at 20:59
That's highly possible because I didn't know your data types, so I put the "SUM" randomly. It should not be to too difficult to correct it I guess. However I agree with Lee Mac regarding the fact that it is a bad practice to mix data and aggregated data within the same query result.
– FloT
Nov 20 '18 at 9:01
That's highly possible because I didn't know your data types, so I put the "SUM" randomly. It should not be to too difficult to correct it I guess. However I agree with Lee Mac regarding the fact that it is a bad practice to mix data and aggregated data within the same query result.
– FloT
Nov 20 '18 at 9:01
add a comment |
Whilst you can certainly union
the aggregated totals query to the end of your original query, in my opinion this would be really bad practice and would be undesirable for any real-world application.
Consider that the resulting query could no longer be used for any meaningful analysis of the data: if displayed in a datagrid, the user would not be able to sort the data without the totals row being interspersed amongst the rest of the data; the user could no longer use the built-in Totals
option to perform their own aggregate operation, and the insertion of a row only identifiable by the term totals
could even conflict with other data within the set.
Instead, I would suggest displaying the totals within an entirely separate form control, using a separate query such as the following (based on your own example):
SELECT Count(Course.CourseNo) as Courses, Sum(Course.CrHrs) as Hours
FROM Course INNER JOIN Sections ON Course.CourseNo = Sections.CourseNo
WHERE Sections.Term = "spring";
However, since CrHrs
are fields within your Course
table and not within your Sections
table, the above may yield multiples of the desired result, with the number of hours multiplied by the number of corresponding records in the Sections
table.
If this is the case, the following may be more suitable:
SELECT Count(Course.CourseNo) as Courses, Sum(Course.CrHrs) as Hours
FROM
Course INNER JOIN
(SELECT DISTINCT s.CourseNo FROM Sections s WHERE s.Term = "spring") q
ON Course.CourseNo = q.CourseNo
Thank you for the reply. I like this code you post, but she requires all data to be shown. I will use this in future projects though!
– Jared Hammel
Nov 19 '18 at 20:59
add a comment |
Whilst you can certainly union
the aggregated totals query to the end of your original query, in my opinion this would be really bad practice and would be undesirable for any real-world application.
Consider that the resulting query could no longer be used for any meaningful analysis of the data: if displayed in a datagrid, the user would not be able to sort the data without the totals row being interspersed amongst the rest of the data; the user could no longer use the built-in Totals
option to perform their own aggregate operation, and the insertion of a row only identifiable by the term totals
could even conflict with other data within the set.
Instead, I would suggest displaying the totals within an entirely separate form control, using a separate query such as the following (based on your own example):
SELECT Count(Course.CourseNo) as Courses, Sum(Course.CrHrs) as Hours
FROM Course INNER JOIN Sections ON Course.CourseNo = Sections.CourseNo
WHERE Sections.Term = "spring";
However, since CrHrs
are fields within your Course
table and not within your Sections
table, the above may yield multiples of the desired result, with the number of hours multiplied by the number of corresponding records in the Sections
table.
If this is the case, the following may be more suitable:
SELECT Count(Course.CourseNo) as Courses, Sum(Course.CrHrs) as Hours
FROM
Course INNER JOIN
(SELECT DISTINCT s.CourseNo FROM Sections s WHERE s.Term = "spring") q
ON Course.CourseNo = q.CourseNo
Thank you for the reply. I like this code you post, but she requires all data to be shown. I will use this in future projects though!
– Jared Hammel
Nov 19 '18 at 20:59
add a comment |
Whilst you can certainly union
the aggregated totals query to the end of your original query, in my opinion this would be really bad practice and would be undesirable for any real-world application.
Consider that the resulting query could no longer be used for any meaningful analysis of the data: if displayed in a datagrid, the user would not be able to sort the data without the totals row being interspersed amongst the rest of the data; the user could no longer use the built-in Totals
option to perform their own aggregate operation, and the insertion of a row only identifiable by the term totals
could even conflict with other data within the set.
Instead, I would suggest displaying the totals within an entirely separate form control, using a separate query such as the following (based on your own example):
SELECT Count(Course.CourseNo) as Courses, Sum(Course.CrHrs) as Hours
FROM Course INNER JOIN Sections ON Course.CourseNo = Sections.CourseNo
WHERE Sections.Term = "spring";
However, since CrHrs
are fields within your Course
table and not within your Sections
table, the above may yield multiples of the desired result, with the number of hours multiplied by the number of corresponding records in the Sections
table.
If this is the case, the following may be more suitable:
SELECT Count(Course.CourseNo) as Courses, Sum(Course.CrHrs) as Hours
FROM
Course INNER JOIN
(SELECT DISTINCT s.CourseNo FROM Sections s WHERE s.Term = "spring") q
ON Course.CourseNo = q.CourseNo
Whilst you can certainly union
the aggregated totals query to the end of your original query, in my opinion this would be really bad practice and would be undesirable for any real-world application.
Consider that the resulting query could no longer be used for any meaningful analysis of the data: if displayed in a datagrid, the user would not be able to sort the data without the totals row being interspersed amongst the rest of the data; the user could no longer use the built-in Totals
option to perform their own aggregate operation, and the insertion of a row only identifiable by the term totals
could even conflict with other data within the set.
Instead, I would suggest displaying the totals within an entirely separate form control, using a separate query such as the following (based on your own example):
SELECT Count(Course.CourseNo) as Courses, Sum(Course.CrHrs) as Hours
FROM Course INNER JOIN Sections ON Course.CourseNo = Sections.CourseNo
WHERE Sections.Term = "spring";
However, since CrHrs
are fields within your Course
table and not within your Sections
table, the above may yield multiples of the desired result, with the number of hours multiplied by the number of corresponding records in the Sections
table.
If this is the case, the following may be more suitable:
SELECT Count(Course.CourseNo) as Courses, Sum(Course.CrHrs) as Hours
FROM
Course INNER JOIN
(SELECT DISTINCT s.CourseNo FROM Sections s WHERE s.Term = "spring") q
ON Course.CourseNo = q.CourseNo
answered Nov 19 '18 at 17:58
Lee MacLee Mac
4,46431541
4,46431541
Thank you for the reply. I like this code you post, but she requires all data to be shown. I will use this in future projects though!
– Jared Hammel
Nov 19 '18 at 20:59
add a comment |
Thank you for the reply. I like this code you post, but she requires all data to be shown. I will use this in future projects though!
– Jared Hammel
Nov 19 '18 at 20:59
Thank you for the reply. I like this code you post, but she requires all data to be shown. I will use this in future projects though!
– Jared Hammel
Nov 19 '18 at 20:59
Thank you for the reply. I like this code you post, but she requires all data to be shown. I will use this in future projects though!
– Jared Hammel
Nov 19 '18 at 20:59
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.
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%2f53379174%2fms-access-2013-how-to-add-totals-row-within-sql%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
which column(s) are you trying to total up? And what kind of "total" aggregation? (Sum, Count, etc)?
– ArcherBird
Nov 19 '18 at 16:50