MS Access Count unique values of one table appearing in second table which is related to a third table
I am working with my lab database and close to complete it. But i am stuck in a query and a few similar queries which all give back the similar results.
Here is the Query in design mode
and this is what it gives out
This query is counting the number of ID values in table PatientTestIDs whereas I want to count the number of unique PatientID values grouped by each department
I have even tried Unique Values and Unique Records properties but all the times it gives the same result.
sql ms-access unique-values
edited Jan 3 at 7:43
Pham X. Bach
3,83821629
asked Jan 3 at 7:39
junaid bashirjunaid bashir
51
add a comment |
I am working with my lab database and close to complete it. But i am stuck in a query and a few similar queries which all give back the similar results.
Here is the Query in design mode
and this is what it gives out
This query is counting the number of ID values in table PatientTestIDs whereas I want to count the number of unique PatientID values grouped by each department
I have even tried Unique Values and Unique Records properties but all the times it gives the same result.
sql ms-access unique-values
edited Jan 3 at 7:43
Pham X. Bach
3,83821629
asked Jan 3 at 7:39
junaid bashirjunaid bashir
51
2
Most people here want sample data and the expected result as formatted text, not as images (or links to images.)
– jarlh
Jan 3 at 7:41
add a comment |
I am working with my lab database and close to complete it. But i am stuck in a query and a few similar queries which all give back the similar results.
Here is the Query in design mode
and this is what it gives out
This query is counting the number of ID values in table PatientTestIDs whereas I want to count the number of unique PatientID values grouped by each department
I have even tried Unique Values and Unique Records properties but all the times it gives the same result.
sql ms-access unique-values
edited Jan 3 at 7:43
Pham X. Bach
3,83821629
asked Jan 3 at 7:39
junaid bashirjunaid bashir
51
I am working with my lab database and close to complete it. But i am stuck in a query and a few similar queries which all give back the similar results.
Here is the Query in design mode
and this is what it gives out
This query is counting the number of ID values in table PatientTestIDs whereas I want to count the number of unique PatientID values grouped by each department
I have even tried Unique Values and Unique Records properties but all the times it gives the same result.
sql ms-access unique-values
sql ms-access unique-values
edited Jan 3 at 7:43
Pham X. Bach
3,83821629
asked Jan 3 at 7:39
junaid bashirjunaid bashir
51
edited Jan 3 at 7:43
Pham X. Bach
3,83821629
asked Jan 3 at 7:39
junaid bashirjunaid bashir
51
edited Jan 3 at 7:43
Pham X. Bach
3,83821629
edited Jan 3 at 7:43
Pham X. Bach
3,83821629
edited Jan 3 at 7:43
Pham X. Bach
3,83821629
3,83821629
asked Jan 3 at 7:39
junaid bashirjunaid bashir
51
asked Jan 3 at 7:39
junaid bashirjunaid bashir
51
asked Jan 3 at 7:39
junaid bashirjunaid bashir
51
51
2
Most people here want sample data and the expected result as formatted text, not as images (or links to images.)
– jarlh
Jan 3 at 7:41
add a comment |
2
Most people here want sample data and the expected result as formatted text, not as images (or links to images.)
– jarlh
Jan 3 at 7:41
2
2
Most people here want sample data and the expected result as formatted text, not as images (or links to images.)
– jarlh
Jan 3 at 7:41
Most people here want sample data and the expected result as formatted text, not as images (or links to images.)
– jarlh
Jan 3 at 7:41
add a comment |
1 Answer
1
active
oldest
votes
What you want requires two queries.
Query1:
SELECT DISTINCT PatientID, DepartmentID FROM PatientTestIDs;
Query2:
SELECT Count(*) AS PatientsPerDept, DepartmentID FROM Query1 GROUP BY DepartmentID;
Nested all in one:
SELECT Count(*) AS PatientsPerDept, DepartmentID FROM (SELECT DISTINCT PatientID, DepartmentID FROM PatientTestIDs) AS Query1 GROUP BY DepartmentID;
You can include the Departments table in query 2 (or the nested version) to pull in descriptive fields but will have to include those additional fields in the GROUP BY.
answered Jan 3 at 8:48
June7June7
5,43151227
Thank you very much june7, exactly that was what i needed. I must've given you a bundle of thanks a long ago, but i got just a bit busy in other things and could not come here. This is very appreciating that you guys put your precious time for society. Thank you again from all the people being helped here
– junaid bashir
Jan 22 at 7:24
Might mark it as accepted answer if it met your needs.
– June7
Jan 22 at 7:54
how to do it. i didn't find the option. Plus i have another problem please check.
– junaid bashir
Mar 21 at 17:10
Should be a check mark icon on the left side of answer you can click.
– June7
Mar 21 at 19:02
Thanks for the help
– junaid bashir
Mar 21 at 20:47
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%2f54018169%2fms-access-count-unique-values-of-one-table-appearing-in-second-table-which-is-re%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
What you want requires two queries.
Query1:
SELECT DISTINCT PatientID, DepartmentID FROM PatientTestIDs;
Query2:
SELECT Count(*) AS PatientsPerDept, DepartmentID FROM Query1 GROUP BY DepartmentID;
Nested all in one:
SELECT Count(*) AS PatientsPerDept, DepartmentID FROM (SELECT DISTINCT PatientID, DepartmentID FROM PatientTestIDs) AS Query1 GROUP BY DepartmentID;
You can include the Departments table in query 2 (or the nested version) to pull in descriptive fields but will have to include those additional fields in the GROUP BY.
answered Jan 3 at 8:48
June7June7
5,43151227
Thank you very much june7, exactly that was what i needed. I must've given you a bundle of thanks a long ago, but i got just a bit busy in other things and could not come here. This is very appreciating that you guys put your precious time for society. Thank you again from all the people being helped here
– junaid bashir
Jan 22 at 7:24
Might mark it as accepted answer if it met your needs.
– June7
Jan 22 at 7:54
how to do it. i didn't find the option. Plus i have another problem please check.
– junaid bashir
Mar 21 at 17:10
Should be a check mark icon on the left side of answer you can click.
– June7
Mar 21 at 19:02
Thanks for the help
– junaid bashir
Mar 21 at 20:47
add a comment |
What you want requires two queries.
Query1:
SELECT DISTINCT PatientID, DepartmentID FROM PatientTestIDs;
Query2:
SELECT Count(*) AS PatientsPerDept, DepartmentID FROM Query1 GROUP BY DepartmentID;
Nested all in one:
SELECT Count(*) AS PatientsPerDept, DepartmentID FROM (SELECT DISTINCT PatientID, DepartmentID FROM PatientTestIDs) AS Query1 GROUP BY DepartmentID;
You can include the Departments table in query 2 (or the nested version) to pull in descriptive fields but will have to include those additional fields in the GROUP BY.
answered Jan 3 at 8:48
June7June7
5,43151227
Thank you very much june7, exactly that was what i needed. I must've given you a bundle of thanks a long ago, but i got just a bit busy in other things and could not come here. This is very appreciating that you guys put your precious time for society. Thank you again from all the people being helped here
– junaid bashir
Jan 22 at 7:24
Might mark it as accepted answer if it met your needs.
– June7
Jan 22 at 7:54
how to do it. i didn't find the option. Plus i have another problem please check.
– junaid bashir
Mar 21 at 17:10
Should be a check mark icon on the left side of answer you can click.
– June7
Mar 21 at 19:02
Thanks for the help
– junaid bashir
Mar 21 at 20:47
add a comment |
What you want requires two queries.
Query1:
SELECT DISTINCT PatientID, DepartmentID FROM PatientTestIDs;
Query2:
SELECT Count(*) AS PatientsPerDept, DepartmentID FROM Query1 GROUP BY DepartmentID;
Nested all in one:
SELECT Count(*) AS PatientsPerDept, DepartmentID FROM (SELECT DISTINCT PatientID, DepartmentID FROM PatientTestIDs) AS Query1 GROUP BY DepartmentID;
You can include the Departments table in query 2 (or the nested version) to pull in descriptive fields but will have to include those additional fields in the GROUP BY.
answered Jan 3 at 8:48
June7June7
5,43151227
What you want requires two queries.
Query1:
SELECT DISTINCT PatientID, DepartmentID FROM PatientTestIDs;
Query2:
SELECT Count(*) AS PatientsPerDept, DepartmentID FROM Query1 GROUP BY DepartmentID;
Nested all in one:
SELECT Count(*) AS PatientsPerDept, DepartmentID FROM (SELECT DISTINCT PatientID, DepartmentID FROM PatientTestIDs) AS Query1 GROUP BY DepartmentID;
You can include the Departments table in query 2 (or the nested version) to pull in descriptive fields but will have to include those additional fields in the GROUP BY.
answered Jan 3 at 8:48
June7June7
5,43151227
answered Jan 3 at 8:48
June7June7
5,43151227
answered Jan 3 at 8:48
June7June7
5,43151227
answered Jan 3 at 8:48
June7June7
5,43151227
5,43151227
Thank you very much june7, exactly that was what i needed. I must've given you a bundle of thanks a long ago, but i got just a bit busy in other things and could not come here. This is very appreciating that you guys put your precious time for society. Thank you again from all the people being helped here
– junaid bashir
Jan 22 at 7:24
Might mark it as accepted answer if it met your needs.
– June7
Jan 22 at 7:54
how to do it. i didn't find the option. Plus i have another problem please check.
– junaid bashir
Mar 21 at 17:10
Should be a check mark icon on the left side of answer you can click.
– June7
Mar 21 at 19:02
Thanks for the help
– junaid bashir
Mar 21 at 20:47
add a comment |
Thank you very much june7, exactly that was what i needed. I must've given you a bundle of thanks a long ago, but i got just a bit busy in other things and could not come here. This is very appreciating that you guys put your precious time for society. Thank you again from all the people being helped here
– junaid bashir
Jan 22 at 7:24
Might mark it as accepted answer if it met your needs.
– June7
Jan 22 at 7:54
how to do it. i didn't find the option. Plus i have another problem please check.
– junaid bashir
Mar 21 at 17:10
Should be a check mark icon on the left side of answer you can click.
– June7
Mar 21 at 19:02
Thanks for the help
– junaid bashir
Mar 21 at 20:47
Thank you very much june7, exactly that was what i needed. I must've given you a bundle of thanks a long ago, but i got just a bit busy in other things and could not come here. This is very appreciating that you guys put your precious time for society. Thank you again from all the people being helped here
– junaid bashir
Jan 22 at 7:24
Thank you very much june7, exactly that was what i needed. I must've given you a bundle of thanks a long ago, but i got just a bit busy in other things and could not come here. This is very appreciating that you guys put your precious time for society. Thank you again from all the people being helped here
– junaid bashir
Jan 22 at 7:24
Might mark it as accepted answer if it met your needs.
– June7
Jan 22 at 7:54
Might mark it as accepted answer if it met your needs.
– June7
Jan 22 at 7:54
how to do it. i didn't find the option. Plus i have another problem please check.
– junaid bashir
Mar 21 at 17:10
how to do it. i didn't find the option. Plus i have another problem please check.
– junaid bashir
Mar 21 at 17:10
Should be a check mark icon on the left side of answer you can click.
– June7
Mar 21 at 19:02
Should be a check mark icon on the left side of answer you can click.
– June7
Mar 21 at 19:02
Thanks for the help
– junaid bashir
Mar 21 at 20:47
Thanks for the help
– junaid bashir
Mar 21 at 20:47
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%2f54018169%2fms-access-count-unique-values-of-one-table-appearing-in-second-table-which-is-re%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
2
Most people here want sample data and the expected result as formatted text, not as images (or links to images.)
– jarlh
Jan 3 at 7:41