Compare Another Field of Duplicate Records

[ttp]

If you have duplicate records, is there a means to compare a field in the
records?

How can I create the following expression in MS Access

Following scenerio:
[shop order] has duplicate records with an [ICN] field
I want to compare [ICN] for the duplicate [shop order]
If all [ICN] begins with"CR", then return "HQ"
IF all [ICN] begins with "DR", then return "OC"
else return "N/A"

 

[Allen Browne]

Create a query using this table.

Depress the Total button (on the toolbar in query design.)
Access adds a Total row to the design grid.

In the total row under the field(s) that define 'duplicate', choose:
Group By.

In the total row under the primary key field, choose:
Count
and in the criteria under this choose:

1

This limits the results to those where a duplicate exists.

In the total row under the ICN field, choose:
Min

Add the ICN field a second time. This time, in the total row choose:
Max

The first ICN column will show the mininum value, and the second one the
maximum value. Where there 2 duplicates, you can now see both values. (If
there are 3 or more duplicates, you see only the highest and lowest values.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

If you have duplicate records, is there a means to compare a field in the
records?

How can I create the following expression in MS Access

Following scenerio:
[shop order] has duplicate records with an [ICN] field
I want to compare [ICN] for the duplicate [shop order]
If all [ICN] begins with"CR", then return "HQ"
IF all [ICN] begins with "DR", then return "OC"
else return "N/A"

 

[ttp]

I tried each step listed below. But, I'm having some issues. First, the
shop order field which contains the duplicates are not being grouped into one
record. I set the query up so that the primary key field is the same as the
duplicate field. Should this be an issue? Also, the variable that I am
comparing is a text field and not a number. Should the total row work for
text field also?

Create a query using this table.

Depress the Total button (on the toolbar in query design.)
Access adds a Total row to the design grid.

In the total row under the field(s) that define 'duplicate', choose:
Group By.

In the total row under the primary key field, choose:
Count
and in the criteria under this choose:

1

This limits the results to those where a duplicate exists.

In the total row under the ICN field, choose:
Min

Add the ICN field a second time. This time, in the total row choose:
Max

The first ICN column will show the mininum value, and the second one the
maximum value. Where there 2 duplicates, you can now see both values. (If
there are 3 or more duplicates, you see only the highest and lowest values.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

If you have duplicate records, is there a means to compare a field in the
records?

How can I create the following expression in MS Access

Following scenerio:
[shop order] has duplicate records with an [ICN] field
I want to compare [ICN] for the duplicate [shop order]
If all [ICN] begins with"CR", then return "HQ"
IF all [ICN] begins with "DR", then return "OC"
else return "N/A"

 

[ttp]

Seems some how I corrected my error. I now only see the individual record on
one row.

Additional question: If I am showing only min or max, how will I know if
all of the ICNs begin with a specific variable? In my If statement, I want
to compare the first variable of all ICNs. With the steps below, I can not
see all of the ICNs to know if all are the same or different. Maybe I am
still missing somehting. Can you assist further?

Create a query using this table.

Depress the Total button (on the toolbar in query design.)
Access adds a Total row to the design grid.

In the total row under the field(s) that define 'duplicate', choose:
Group By.

In the total row under the primary key field, choose:
Count
and in the criteria under this choose:

1

This limits the results to those where a duplicate exists.

In the total row under the ICN field, choose:
Min

Add the ICN field a second time. This time, in the total row choose:
Max

The first ICN column will show the mininum value, and the second one the
maximum value. Where there 2 duplicates, you can now see both values. (If
there are 3 or more duplicates, you see only the highest and lowest values.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

If you have duplicate records, is there a means to compare a field in the
records?

How can I create the following expression in MS Access

Following scenerio:
[shop order] has duplicate records with an [ICN] field
I want to compare [ICN] for the duplicate [shop order]
If all [ICN] begins with"CR", then return "HQ"
IF all [ICN] begins with "DR", then return "OC"
else return "N/A"

 

[John W. Vinson]

I set the query up so that the primary key field is the same as the
duplicate field. Should this be an issue?

Yes. The primary key must be unique in every record, so it cannot possibly
ever be a duplicate.

 

[ttp]

Once I have grouped the duplicate records (shop orders), are there additional
functions that I can use besides the operations listed? The operations
listed seem only for numbers. Whereas, my data is text. Once the duplicate
records are grouped, can I compare associated field to see if each shop order
(duplicate record) begins with a specific letter?

Seems some how I corrected my error. I now only see the individual record on
one row.

Additional question: If I am showing only min or max, how will I know if
all of the ICNs begin with a specific variable? In my If statement, I want
to compare the first variable of all ICNs. With the steps below, I can not
see all of the ICNs to know if all are the same or different. Maybe I am
still missing somehting. Can you assist further?

Create a query using this table.

Depress the Total button (on the toolbar in query design.)
Access adds a Total row to the design grid.

In the total row under the field(s) that define 'duplicate', choose:
Group By.

In the total row under the primary key field, choose:
Count
and in the criteria under this choose:

1

This limits the results to those where a duplicate exists.

In the total row under the ICN field, choose:
Min

Add the ICN field a second time. This time, in the total row choose:
Max

The first ICN column will show the mininum value, and the second one the
maximum value. Where there 2 duplicates, you can now see both values. (If
there are 3 or more duplicates, you see only the highest and lowest values.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

If you have duplicate records, is there a means to compare a field in the
records?

How can I create the following expression in MS Access

Following scenerio:
[shop order] has duplicate records with an [ICN] field
I want to compare [ICN] for the duplicate [shop order]
If all [ICN] begins with"CR", then return "HQ"
IF all [ICN] begins with "DR", then return "OC"
else return "N/A"