Find duplicates from two fields

  • Thread starter Thread starter Gary Dolliver
  • Start date Start date
G

Gary Dolliver

Hi all,
I am attempting to pull out records that have two fields identical, and
later to be based on a date filter.
The main table is [orders], and the fields I want to check are OPID and OTN
- basically if OPID and OTN are the same in any records, to bring the
culprits to screen. OTN may be the same in other records, but it should only
"flag" if the combination of OPID and OTN are the same.
The date filter will be added later as I will then only want to check orders
that were created after the date, but still go back and check all the
history.
Finally, I will then want to also check the archive tables - the only
difference here is the table name is now [orders_archive] - but the fields
are the same
I tried starting with the duplicate SQL syntax found on this board, but I
keep getting syntax errors - i was using this:
SELECT * FROM [orders] WHERE [orders].[OTN] = IN(SELECT [orders].[OTN] FROM
[orders] AS [tmp]
GROUP BY [orders].[OTN] HAVING Count(*)>1;);
Help is greatly appreicated!
-gary
 
That code will find different records with duplicate values in the same
fields. What You want is:

SELECT * FROM [orders] WHERE [orders].[OTN] = [orders.OPID];

That will return any records where the two fields in the same record have
the same value.
 
Your SQL is indeed wrong it should say:

SELECT * FROM orders
WHERE OTN IN (SELECT OTN FROM orders GROUP BY OTN HAVING Count(*)>1)

This though will find multiple records with the same value of OTN. In your
posting you say that you are looking for records with matching OTN and OPID
values which would be:

SELECT * FROM orders WHERE OTN = OPID

Over to you to decide which you need!

HTH
John
##################################
Don't Print - Save trees
 
Thank you for the reply. I think I am asking my question incorrectly. I
would like to pull records that have both PTID and OTN the same as another
record. For example:
record_num PTID OTN
1 1 1
2 1 2
3 1 3
4 2 1
5 2 7
6 1 2

I want the result of this to show me records 2 and 6, as they have the same
combination of PTID and OTN. Sorry, hope that makes sense
-gary

Klatuu said:
That code will find different records with duplicate values in the same
fields. What You want is:

SELECT * FROM [orders] WHERE [orders].[OTN] = [orders.OPID];

That will return any records where the two fields in the same record have
the same value.
--
Dave Hargis, Microsoft Access MVP


Gary Dolliver said:
Hi all,
I am attempting to pull out records that have two fields identical, and
later to be based on a date filter.
The main table is [orders], and the fields I want to check are OPID and OTN
- basically if OPID and OTN are the same in any records, to bring the
culprits to screen. OTN may be the same in other records, but it should only
"flag" if the combination of OPID and OTN are the same.
The date filter will be added later as I will then only want to check orders
that were created after the date, but still go back and check all the
history.
Finally, I will then want to also check the archive tables - the only
difference here is the table name is now [orders_archive] - but the fields
are the same
I tried starting with the duplicate SQL syntax found on this board, but I
keep getting syntax errors - i was using this:
SELECT * FROM [orders] WHERE [orders].[OTN] = IN(SELECT [orders].[OTN] FROM
[orders] AS [tmp]
GROUP BY [orders].[OTN] HAVING Count(*)>1;);
Help is greatly appreicated!
-gary
Click to expand...
Click to expand...
 
Thank you for the reply. I think I am asking my question incorrectly. I
would like to pull records that have both PTID and OTN the same as another
record. For example:
record_num PTID OTN
1 1 1
2 1 2
3 1 3
4 2 1
5 2 7
6 1 2

I want the result of this to show me records 2 and 6, as they have the same
combination of PTID and OTN. Sorry, hope that makes sense
-gary


John Smith said:
Your SQL is indeed wrong it should say:

SELECT * FROM orders
WHERE OTN IN (SELECT OTN FROM orders GROUP BY OTN HAVING Count(*)>1)

This though will find multiple records with the same value of OTN. In your
posting you say that you are looking for records with matching OTN and OPID
values which would be:

SELECT * FROM orders WHERE OTN = OPID

Over to you to decide which you need!

HTH
John
##################################
Don't Print - Save trees

Gary said:
I am attempting to pull out records that have two fields identical, and
later to be based on a date filter.
The main table is [orders], and the fields I want to check are OPID and OTN
- basically if OPID and OTN are the same in any records, to bring the
culprits to screen. OTN may be the same in other records, but it should only
"flag" if the combination of OPID and OTN are the same.
The date filter will be added later as I will then only want to check orders
that were created after the date, but still go back and check all the
history.
Finally, I will then want to also check the archive tables - the only
difference here is the table name is now [orders_archive] - but the fields
are the same
I tried starting with the duplicate SQL syntax found on this board, but I
keep getting syntax errors - i was using this:
SELECT * FROM [orders] WHERE [orders].[OTN] = IN(SELECT [orders].[OTN] FROM
[orders] AS [tmp]
GROUP BY [orders].[OTN] HAVING Count(*)>1;);
Help is greatly appreicated!
Click to expand...
Click to expand...
 
Hi John,
I have tried replying with a similar response given to Mr. Hargis above, but
it keeps removing it from the list... if possible, please read my reply above
as I think I have not explained what I am trying to do correctly, thanks
-gary

John Smith said:
Your SQL is indeed wrong it should say:

SELECT * FROM orders
WHERE OTN IN (SELECT OTN FROM orders GROUP BY OTN HAVING Count(*)>1)

This though will find multiple records with the same value of OTN. In your
posting you say that you are looking for records with matching OTN and OPID
values which would be:

SELECT * FROM orders WHERE OTN = OPID

Over to you to decide which you need!

HTH
John
##################################
Don't Print - Save trees

Gary said:
I am attempting to pull out records that have two fields identical, and
later to be based on a date filter.
The main table is [orders], and the fields I want to check are OPID and OTN
- basically if OPID and OTN are the same in any records, to bring the
culprits to screen. OTN may be the same in other records, but it should only
"flag" if the combination of OPID and OTN are the same.
The date filter will be added later as I will then only want to check orders
that were created after the date, but still go back and check all the
history.
Finally, I will then want to also check the archive tables - the only
difference here is the table name is now [orders_archive] - but the fields
are the same
I tried starting with the duplicate SQL syntax found on this board, but I
keep getting syntax errors - i was using this:
SELECT * FROM [orders] WHERE [orders].[OTN] = IN(SELECT [orders].[OTN] FROM
[orders] AS [tmp]
GROUP BY [orders].[OTN] HAVING Count(*)>1;);
Help is greatly appreicated!
Click to expand...
Click to expand...
 
In that case you want a variation of the first query:

SELECT * FROM orders
WHERE OTN & PTID IN (SELECT OTN & PTID FROM orders GROUP BY OTN & PTID HAVING
Count(*)>1)

HTH
John
##################################
Don't Print - Save trees

Gary said:
Thank you for the reply. I think I am asking my question incorrectly. I
would like to pull records that have both PTID and OTN the same as another
record. For example:
record_num PTID OTN
1 1 1
2 1 2
3 1 3
4 2 1
5 2 7
6 1 2

I want the result of this to show me records 2 and 6, as they have the same
combination of PTID and OTN. Sorry, hope that makes sense
-gary

Klatuu said:
That code will find different records with duplicate values in the same
fields. What You want is:

SELECT * FROM [orders] WHERE [orders].[OTN] = [orders.OPID];

That will return any records where the two fields in the same record have
the same value.
--
Dave Hargis, Microsoft Access MVP


Gary Dolliver said:
Hi all,
I am attempting to pull out records that have two fields identical, and
later to be based on a date filter.
The main table is [orders], and the fields I want to check are OPID and OTN
- basically if OPID and OTN are the same in any records, to bring the
culprits to screen. OTN may be the same in other records, but it should only
"flag" if the combination of OPID and OTN are the same.
The date filter will be added later as I will then only want to check orders
that were created after the date, but still go back and check all the
history.
Finally, I will then want to also check the archive tables - the only
difference here is the table name is now [orders_archive] - but the fields
are the same
I tried starting with the duplicate SQL syntax found on this board, but I
keep getting syntax errors - i was using this:
SELECT * FROM [orders] WHERE [orders].[OTN] = IN(SELECT [orders].[OTN] FROM
[orders] AS [tmp]
GROUP BY [orders].[OTN] HAVING Count(*)>1;);
Help is greatly appreicated!
-gary
Click to expand...
Click to expand...
Click to expand...
 
Back
Top