Modifying a query using the Duplicates Wizard

[Guest]

I'm looking for help with queries. I have a gigantic database that has many
duplicate entries. I know how to use the duplicate query wizard (and have
used it successfully many times in the past), but it limits me to looking for
duplicates within certain fields and does not allow me to exclude certain
values. For example, I want to run a duplicate query looking for when "Field
A" and "Field B" (from a table called "Master Table") both contain duplicate
queries. This is easy to do...and using the expresison builder I get...

In (SELECT [Field A] FROM [Master Table] As Tmp GROUP BY [Field A],[Field B]
HAVING Count(*)>1 And [Field B] = [Master Table].[Field B])

My question is how do I add a third criteria specifying a NOT equal in a
third field? Right now my query shows me all entries for which both Field A
and Field B are equal. But what if I want to see where Field A is equal,
Field B is equal, but field C is NOT equal? How would I change this query?

 

[Wolfgang Kais]

Hello "ShayC".

I'm looking for help with queries. I have a gigantic database that has
many duplicate entries. I know how to use the duplicate query wizard
(and have used it successfully many times in the past), but it limits me
to looking for duplicates within certain fields and does not allow me to
exclude certain values. For example, I want to run a duplicate query
looking for when "Field A" and "Field B" (from a table called "Master
Table") both contain duplicate queries.
This is easy to do...and using the expresison builder I get...

In (SELECT [Field A] FROM [Master Table] As Tmp GROUP BY [Field A],[Field
B]
HAVING Count(*)>1 And [Field B] = [Master Table].[Field B])

My question is how do I add a third criteria specifying a NOT equal
in a third field? Right now my query shows me all entries for which
both Field A and Field B are equal. But what if I want to see where
Field A is equal, Field B is equal, but field C is NOT equal?
How would I change this query?

The goal isn't clear: What if you have 3 records: For two of them Field A,
Field B and Field C are equal, for the third one Field A and Field B are
equal and Field C is not equal to the Field C of the other two records:
Do you want the first two records not to be shown?
Do you want the third record to be shown?
If "No" and "Yes" you will see record 3, but where are "duplicates"?

 

[Guest]

Wolgnag, thank you for the reply. Let me try to clarify what I am looking
for. I have a table wil many transactions. I am trying to identify the
pairs of duplicate transactions for which the same "Invoice Date" (Field A)
and "Invoice Amount" (Field B) were entered but where the "Invoice Number"
(Field C) is different.

For example:
Invoice # a123 dated 1/01/01 for $100.00
and
Invoice # 123a dated 1/01/01 for $100.00

I do not want the following to appear in my results (if it is infact in the
database):
Invoice # a123 dated 1/01/01 for $100.00
and
Invoice # a123 dated 1/01/01 for $100.00

Essentially I am looking for instances where there was a keying error in one
field. Any suggestions on how to do this? Thanks in advance.

Hello "ShayC".

I'm looking for help with queries. I have a gigantic database that has
many duplicate entries. I know how to use the duplicate query wizard
(and have used it successfully many times in the past), but it limits me
to looking for duplicates within certain fields and does not allow me to
exclude certain values. For example, I want to run a duplicate query
looking for when "Field A" and "Field B" (from a table called "Master
Table") both contain duplicate queries.
This is easy to do...and using the expresison builder I get...

In (SELECT [Field A] FROM [Master Table] As Tmp GROUP BY [Field A],[Field
B]
HAVING Count(*)>1 And [Field B] = [Master Table].[Field B])

My question is how do I add a third criteria specifying a NOT equal
in a third field? Right now my query shows me all entries for which
both Field A and Field B are equal. But what if I want to see where
Field A is equal, Field B is equal, but field C is NOT equal?
How would I change this query?

The goal isn't clear: What if you have 3 records: For two of them Field A,
Field B and Field C are equal, for the third one Field A and Field B are
equal and Field C is not equal to the Field C of the other two records:
Do you want the first two records not to be shown?
Do you want the third record to be shown?
If "No" and "Yes" you will see record 3, but where are "duplicates"?

 

[Wolfgang Kais]

Hello "ShayC".

I'm looking for help with queries. I have a gigantic database that
has many duplicate entries. I know how to use the duplicate query
wizard (and have used it successfully many times in the past), but
it limits me to looking for duplicates within certain fields and does
not allow me to exclude certain values. For example, I want to run
a duplicate query looking for when "Field A" and "Field B" (from a
table called "Master Table") both contain duplicate queries.
This is easy to do...and using the expresison builder I get...

In (SELECT [Field A] FROM [Master Table] As Tmp GROUP BY
[Field A],[Field B]
HAVING Count(*)>1 And [Field B] = [Master Table].[Field B])

My question is how do I add a third criteria specifying a NOT equal
in a third field? Right now my query shows me all entries for which
both Field A and Field B are equal. But what if I want to see where
Field A is equal, Field B is equal, but field C is NOT equal?
How would I change this query?

The goal isn't clear: What if you have 3 records: For two of them
Field A, Field B and Field C are equal, for the third one Field A and
Field B are equal and Field C is not equal to the Field C of the other
two records: Do you want the first two records not to be shown?
Do you want the third record to be shown?
If "No" and "Yes" you will see record 3, but where are "duplicates"?

Wolgnag, thank you for the reply. Let me try to clarify what I am
looking for. I have a table wil many transactions. I am trying to
identify the pairs of duplicate transactions for which the same
"Invoice Date" (Field A) and "Invoice Amount" (Field B) were
entered but where the "Invoice Number" (Field C) is different.

For example:
Invoice # a123 dated 1/01/01 for $100.00
and
Invoice # 123a dated 1/01/01 for $100.00

I do not want the following to appear in my results (if it is infact
in the database):
Invoice # a123 dated 1/01/01 for $100.00
and
Invoice # a123 dated 1/01/01 for $100.00

Essentially I am looking for instances where there was a keying error
in one field. Any suggestions on how to do this? Thanks in advance.

Aaah, I see. In my example, you want the query to return The first or
second record and the third record. Unfortunately, no matter how the
query will be built, you will not be able to delete records in the grid.
The wizard doesn't create a query that lists pairs but lists records
with duplicate values.
A query that lists pairs (side by side) could look like this:

Select * From [Master Table] Inner Join [Master Table] As Master2
On [Master Table].[Field A] = Master2.[Field A] And
[Master Table].[Field B] = Master2.[Field B]
Where [Master Table].[Field C] < Master2.[Field C]