Separating records 1

[teqtaq]

I am having records

AAA___2
AAA___3
AAA___4
AAA___5

BBB___3
BBB___4

CCC__2
CCC__1

I need to pull records that are = 5 into Bucket5 table.
Those records that have no 5s in ALLBucket table.

So AAA records are belong into Bucket5 table.
BBB and CCC are in ALLTable. No AAA records in ALLBucket.

I am having a problem separating those AAAs from the rest.

Thanks for your help

 

[Remou]

Does this get the records you want?

AAA, BBB etc=Field1
Number=Field2


SELECT Field1, Field2
FROM tblT
WHERE Field1
IN (SELECT Field1 FROM tblT WHERE Field2=5)

 

[teqtaq]

All right. It does not do what I wanted.
Partially because I was not such a good at explaining.
AAA___2
AAA___3
AAA___4
AAA___5

BBB___3
BBB___4

CCC__2
CCC__1

I would have Select Field1 where Field2=5 in a Bucket5 table. This is what Bucket5 needs.
BucketALL should be populated with all Fields1 except those where there is occurence of =5 per group.
So Group AAA is excluded just because there was 1 occurence of the 5.

I did built Bucket5 table where Field1=5.
I have table with all records as well.
If I could use MINUS query in Access it would be best.
But somehow I am not sure why it is not working.

I am looking for another approach still and thanks for hanging there with me.

 

[Remou]

I am still not sure that I understand. The example I posted was for records that are to be added to the Bucket5 table. The reverse, that is NOT IN, would be added to the BucketALL table. It seems that this is not what you want? Perhaps you could post what Bucket5 and BucketALL are to look like after you run the query?

 

[teqtaq]

Remou,
I resolved it by having an Update query.
Thanks so much!