Working with multiple records with strict criteria

[Grunners]

All,

I shall try and explain this problem in a far less vague way than the title suggests!

1 table with 3 fields. MemberNo/date/paid
e.g.
4000,01/01/2004,True
4000,08/01/2004,True etc...

What I'm trying to do is sort firstly by [MemberNo], then by [paid]. If ALL and ONLY ALL of a persons records are true then I want to delete all their records from the table, (or append them to another table but delete will do here).

If for instance [MemberNo] = 4000 and his first 9 records are true but the 10th is false then ALL of his records need to stay in the table, i.e. do nothing with them and move on to the next [MemberNo]

Thus I want to go thru the whole table, finding MemberNo's where ALL their records in the paid field are the same and then 'do something' with the records. The 'do something' appears to be the easy bit!

Simple multiple queries don't appear to be the answer but feel free to set me straight if I've just not stuck at it long enough...

I've tried doing this in code and so far managed not much at all. I'll no doubt have it down by the end of the weekend but if I spend the next two days coding my wife may attempt to kill me. Please take pitty! Any suggestions, aside from "get divorced" would be much appreciated!

Thank you...

 

[KenHigg]

Hum...

How about this:

Create a temp table with a member fld. Then do an totals query that groups by member id and and counts the number of 'false' records and only display records where this number is '0'. This should give you a list of member ids to delete. (Hopefully )

Then in another query bring in your main table and link in the total query. Bring in the pk from the main fld and the pk from the total query. In the second pk fld, as a criteria, use 'is not null'. This should return all the records you need to delete (Hopefully ). Once this works, simply change it to a delete query...

 

[Grunners]

Thanks Ken

Just finished! Works fine, cheers...