CODEWITHSUNDEEP
sqlvbams-accessduplicates
Jordan
Jordan

Reputation: 51

Using a query in Microsoft Access to compare two fields and find multiple matching values

I have a problem I am trying to solve using a query instead of VBA.

I have two fields which we'll call "FPC" and "Code". Both fields contain numbers. An FPC value will match a Code value. What I want to make sure is that once an FPC value matches a Code value, the same FPC value does not match up with a DIFFERENT Code Value and vice versa - Once a Code Value is used, I don't want the Code Value to match up with more than one FPC.

It is important to note that there are duplicate values used in both fields.

Here is an example:

FPC         CODE
1            12
1            12
1            14
2            16
3            11
3            11
4            17
5            19 
6            16

There are two errors here:
1. The FPC "1" is matched up with two different Code Values.
2. The Code "16" is matched up with two different FPC values.

Please let me know your suggestions. I was thinking a query would help, and then running VBA to pull the results (there is tens of thousands of records).

Upvotes: 3

Views: 5311

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

What is the purpose of the query? Just to identify problems? If yes then something like

select FPC, count(distinct(CODE)) from tableName where count(distinct(CODE)) >1 group by FPC

(and the converse query for CODE vs. FPC) should be OK.

Upvotes: 1

Related Questions