Duplicate values SQL (MS Access)
I need to find duplicate records across 2 or more fields. But using this does not work in Access:
SELECT assay.depth_from, assay.au_gt
FROM assay
GROUP BY depth_from, au_gt
HAVING count(*) >1;
Am I missing something? It does match up with various answers here so not sure what.
I just get a records with duplicate depth_from but the au_gt are not duplicate. Actually not all the depth_from are even all duplicated.
sql ms-access
asked Nov 13 '18 at 15:55
ElizabethElizabeth
106
add a comment |
I need to find duplicate records across 2 or more fields. But using this does not work in Access:
SELECT assay.depth_from, assay.au_gt
FROM assay
GROUP BY depth_from, au_gt
HAVING count(*) >1;
Am I missing something? It does match up with various answers here so not sure what.
I just get a records with duplicate depth_from but the au_gt are not duplicate. Actually not all the depth_from are even all duplicated.
sql ms-access
asked Nov 13 '18 at 15:55
ElizabethElizabeth
106
2
Sample data and desires results would help. What do you mean by "duplicate records"?
– Gordon Linoff
Nov 13 '18 at 15:56
1
Access even offers a built-in way to create a query to find duplicates, under Create -> Query Wizard -> Find Duplicates Query Wizard which walks you through the process. If you're inexperienced with SQL you can just use that.
– Erik von Asmuth
Nov 13 '18 at 16:17
Seems obvious but never even thought of it as an option. Just used the wizard as it was for a quick check. Thanks
– Elizabeth
Nov 26 '18 at 14:31
add a comment |
I need to find duplicate records across 2 or more fields. But using this does not work in Access:
SELECT assay.depth_from, assay.au_gt
FROM assay
GROUP BY depth_from, au_gt
HAVING count(*) >1;
Am I missing something? It does match up with various answers here so not sure what.
I just get a records with duplicate depth_from but the au_gt are not duplicate. Actually not all the depth_from are even all duplicated.
sql ms-access
asked Nov 13 '18 at 15:55
ElizabethElizabeth
106
I need to find duplicate records across 2 or more fields. But using this does not work in Access:
SELECT assay.depth_from, assay.au_gt
FROM assay
GROUP BY depth_from, au_gt
HAVING count(*) >1;
Am I missing something? It does match up with various answers here so not sure what.
I just get a records with duplicate depth_from but the au_gt are not duplicate. Actually not all the depth_from are even all duplicated.
sql ms-access
sql ms-access
asked Nov 13 '18 at 15:55
ElizabethElizabeth
106
asked Nov 13 '18 at 15:55
ElizabethElizabeth
106
asked Nov 13 '18 at 15:55
ElizabethElizabeth
106
asked Nov 13 '18 at 15:55
ElizabethElizabeth
106
asked Nov 13 '18 at 15:55
ElizabethElizabeth
106
106
2
Sample data and desires results would help. What do you mean by "duplicate records"?
– Gordon Linoff
Nov 13 '18 at 15:56
1
Access even offers a built-in way to create a query to find duplicates, under Create -> Query Wizard -> Find Duplicates Query Wizard which walks you through the process. If you're inexperienced with SQL you can just use that.
– Erik von Asmuth
Nov 13 '18 at 16:17
Seems obvious but never even thought of it as an option. Just used the wizard as it was for a quick check. Thanks
– Elizabeth
Nov 26 '18 at 14:31
add a comment |
2
Sample data and desires results would help. What do you mean by "duplicate records"?
– Gordon Linoff
Nov 13 '18 at 15:56
1
Access even offers a built-in way to create a query to find duplicates, under Create -> Query Wizard -> Find Duplicates Query Wizard which walks you through the process. If you're inexperienced with SQL you can just use that.
– Erik von Asmuth
Nov 13 '18 at 16:17
Seems obvious but never even thought of it as an option. Just used the wizard as it was for a quick check. Thanks
– Elizabeth
Nov 26 '18 at 14:31
2
2
Sample data and desires results would help. What do you mean by "duplicate records"?
– Gordon Linoff
Nov 13 '18 at 15:56
Sample data and desires results would help. What do you mean by "duplicate records"?
– Gordon Linoff
Nov 13 '18 at 15:56
1
1
Access even offers a built-in way to create a query to find duplicates, under Create -> Query Wizard -> Find Duplicates Query Wizard which walks you through the process. If you're inexperienced with SQL you can just use that.
– Erik von Asmuth
Nov 13 '18 at 16:17
Access even offers a built-in way to create a query to find duplicates, under Create -> Query Wizard -> Find Duplicates Query Wizard which walks you through the process. If you're inexperienced with SQL you can just use that.
– Erik von Asmuth
Nov 13 '18 at 16:17
Seems obvious but never even thought of it as an option. Just used the wizard as it was for a quick check. Thanks
– Elizabeth
Nov 26 '18 at 14:31
Seems obvious but never even thought of it as an option. Just used the wizard as it was for a quick check. Thanks
– Elizabeth
Nov 26 '18 at 14:31
add a comment |
1 Answer
1
active
oldest
votes
I see two possible syntax issues with your SQL. First, you probably don't need to use the assay. prefix before your field names since you have specified which table you are selecting from, and this makes your reference to those fields in GROUP BY inconsistent. If you do use assay. in your SELECT statement use it in GROUP BY as well. Secondly, you should include count(*) in the SELECT statement. This is basically for the same reason- whatever you reference in GROUP BY and HAVING should be the column names you specified in SELECT. Try this:
SELECT depth_from, au_gt, count(*)
FROM assay
GROUP BY depth_from, au_gt
HAVING count(*) >1;
answered Nov 13 '18 at 16:33
Elizabeth HamElizabeth Ham
1149
Thanks. Access just adds in the table name but that didn't work.
– Elizabeth
Nov 26 '18 at 14:30
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%2f53284793%2fduplicate-values-sql-ms-access%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
I see two possible syntax issues with your SQL. First, you probably don't need to use the assay. prefix before your field names since you have specified which table you are selecting from, and this makes your reference to those fields in GROUP BY inconsistent. If you do use assay. in your SELECT statement use it in GROUP BY as well. Secondly, you should include count(*) in the SELECT statement. This is basically for the same reason- whatever you reference in GROUP BY and HAVING should be the column names you specified in SELECT. Try this:
SELECT depth_from, au_gt, count(*)
FROM assay
GROUP BY depth_from, au_gt
HAVING count(*) >1;
answered Nov 13 '18 at 16:33
Elizabeth HamElizabeth Ham
1149
Thanks. Access just adds in the table name but that didn't work.
– Elizabeth
Nov 26 '18 at 14:30
add a comment |
I see two possible syntax issues with your SQL. First, you probably don't need to use the assay. prefix before your field names since you have specified which table you are selecting from, and this makes your reference to those fields in GROUP BY inconsistent. If you do use assay. in your SELECT statement use it in GROUP BY as well. Secondly, you should include count(*) in the SELECT statement. This is basically for the same reason- whatever you reference in GROUP BY and HAVING should be the column names you specified in SELECT. Try this:
SELECT depth_from, au_gt, count(*)
FROM assay
GROUP BY depth_from, au_gt
HAVING count(*) >1;
answered Nov 13 '18 at 16:33
Elizabeth HamElizabeth Ham
1149
Thanks. Access just adds in the table name but that didn't work.
– Elizabeth
Nov 26 '18 at 14:30
add a comment |
I see two possible syntax issues with your SQL. First, you probably don't need to use the assay. prefix before your field names since you have specified which table you are selecting from, and this makes your reference to those fields in GROUP BY inconsistent. If you do use assay. in your SELECT statement use it in GROUP BY as well. Secondly, you should include count(*) in the SELECT statement. This is basically for the same reason- whatever you reference in GROUP BY and HAVING should be the column names you specified in SELECT. Try this:
SELECT depth_from, au_gt, count(*)
FROM assay
GROUP BY depth_from, au_gt
HAVING count(*) >1;
answered Nov 13 '18 at 16:33
Elizabeth HamElizabeth Ham
1149
I see two possible syntax issues with your SQL. First, you probably don't need to use the assay. prefix before your field names since you have specified which table you are selecting from, and this makes your reference to those fields in GROUP BY inconsistent. If you do use assay. in your SELECT statement use it in GROUP BY as well. Secondly, you should include count(*) in the SELECT statement. This is basically for the same reason- whatever you reference in GROUP BY and HAVING should be the column names you specified in SELECT. Try this:
SELECT depth_from, au_gt, count(*)
FROM assay
GROUP BY depth_from, au_gt
HAVING count(*) >1;
answered Nov 13 '18 at 16:33
Elizabeth HamElizabeth Ham
1149
answered Nov 13 '18 at 16:33
Elizabeth HamElizabeth Ham
1149
answered Nov 13 '18 at 16:33
Elizabeth HamElizabeth Ham
1149
answered Nov 13 '18 at 16:33
Elizabeth HamElizabeth Ham
1149
1149
Thanks. Access just adds in the table name but that didn't work.
– Elizabeth
Nov 26 '18 at 14:30
add a comment |
Thanks. Access just adds in the table name but that didn't work.
– Elizabeth
Nov 26 '18 at 14:30
Thanks. Access just adds in the table name but that didn't work.
– Elizabeth
Nov 26 '18 at 14:30
Thanks. Access just adds in the table name but that didn't work.
– Elizabeth
Nov 26 '18 at 14:30
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%2f53284793%2fduplicate-values-sql-ms-access%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
Sample data and desires results would help. What do you mean by "duplicate records"?
– Gordon Linoff
Nov 13 '18 at 15:56
1
Access even offers a built-in way to create a query to find duplicates, under Create -> Query Wizard -> Find Duplicates Query Wizard which walks you through the process. If you're inexperienced with SQL you can just use that.
– Erik von Asmuth
Nov 13 '18 at 16:17
Seems obvious but never even thought of it as an option. Just used the wizard as it was for a quick check. Thanks
– Elizabeth
Nov 26 '18 at 14:31