Query to check duplicates within table

[sumdumgai]

Let's say there is a table (Tab1) of 500,000 records. In Tab1 there are fields Fld1, Fld2, Fld3, Fld4. For 100,000 of the records, Fld1 contains "AAA". For the rest of the records, Fld1 contains "BBB". So for example, some of the data looks like this:
Rcd# Fld1 Fld2 Fld3 Fld4
1 AAA 2 3 5
2 AAA 3 4 4
3 AAA 1 2 3
4 BBB 2 4 5
5 BBB 3 4 4
6 BBB 2 3 1
7 BBB 1 2 3

I need a query that will return those records where Fld1 = BBB and Fld2, Fld3, Fld4 are equal to the corresponding fields in records where Fld1 = AAA. For the sample data, the query should return records 5 and 7 because Fld2, Fld3 and Fld4 match.

I know how to do this if the AAA's and BBB's where in separate tables, but is there a query I can use with only one table?

Thanks.

 

[bob fitz]

Perhaps a "Totals" or Group By" query would help

 

[sumdumgai]

I'm a bit weak on anything but simple queries. Can you be more specific or give an example? Thanks.

 

[sumdumgai]

So if I have a query that selects Fld1 = AAA records, and another query that selects Fld1 = BBB records, then what is the syntax for joining Fld2, Fld3 and Fld4 and using those queries as my record sets? Thanks.