Mark all records based on record groups

[litrelord]

Hi,

sorry for the awful question title but i couldn't think how to put this simply.

I have a table full of records, many of which have more than one row with the same account number. If one row containing the account number matches a certain criteria I'd like to mark all records with that account number. e.g. account number 12345 has one record that has a column marked true, I need all records for 12345 to have another column marked as true.

I ran a query for all records marked as true (Qry_True_Test) and their file numbers thinking I could use a join on the two queries to test but it's marking all records whether or not they appear in the Qry_True_Test query.

I realise it would be more efficient to have all the account details stored in one table and the rest of the data in another. That's a whole different project for me though :D

Thanks

Nick

 

[Joe4]

I would create an Aggregate query that lists your account number, and the field you want to check to see if it is true. Place your criteria under this field to limit it to "True" responses. Then click on the Total icon (looks like a Simga). This would "Group" all the records toegether of like values (so if you have two records with Account # 12345 with a value of True, it will only show up once).

Now link this aggregate query to your original table on Account Number. Be sure to do a Left Join so it will include all of the records in your table. You can then return the other field (True) from the Aggregate Query.

I think that should give you what you want.

 

[litrelord]

I'll give it a try.

Thanks

Nick

 

[litrelord]

That was it perfectly. I hadn't grouped the query and that made all the difference.

Many thanks

Nick