find dups over 4 files

[Justh8me]

Sorry if this is not the right place for this.

I have 4 files, all are .csv's all contain 2 fields: Account #, Namespace
I need to find if I have any account #'s that match in all of the namespaces. I have done a simple query. But I did it as a namespace A-B, A-C, A-D, etc. My issue is if I have accounts that are in 3 namespaces, or even all 4 I need to see them.

I know I can throw them in excel but the problem is there are 614K accounts, and if I want to do a lookup I need to have them in twice, which makes it 1.2 million records. And it is killing my PC to do it that way. My obvious problem is that I am a bit new to Access.

What is the best way to scrub for duplicates across these 4 files?

For instance:
File 1 File 2 File 3
1111 A 1110 B 1111 C


1112 A 1111 B 1119 C
1113 A 2222 B 1113 C

So in this example I would need to report account number 1111 is in namespace A, B, and C. And 1113 is in A and C.

Any help would be greatly appreciated.

[ssanfu]

Easiest is to import the csv files into one table, then run a Fine Dups query.

I imported the csv files into a table "AccountNums"

Account
Namespace

1111
A

1112
A

1113
A

1110
B

1111
B

2222
B

1111
C

1119
C

1113
C

Then used the wizard to create the find duplicates query:

SELECT AccountNums.Account, AccountNums.Namespace
FROM AccountNums
WHERE (((AccountNums.Account) In (SELECT [Account] FROM [AccountNums] As Tmp GROUP BY [Account] HAVING Count(*)>1 )))
ORDER BY AccountNums.Account;

The result is:

Account
Namespace

1111
C

1111
B

1111
A

1113
C

1113
A