I have a report that is giving me some staffing information for events. I have one table that has some basic event info like customer, date, event ID etc. I have a second joined table that has details for the event like chairs, tables, etc. I can have several entries from the second table for each record in the first table. I need to create a formula or select that will filter out records from the first table based on the details in the second table. Here is what I have

Table1.recordID     Table1. ClientName  Table1.EventDate   Table2.Supplies
5                               Bob's Trucking            5/15/2010                chairs
5                               Bob's Trucking            5/15/2010                tables
5                               Bob's Trucking            5/15/2010                Tent
6                                Joe's Plumbing           6/5/2010                  chairs
6                                Joe's Plumbing           6/5/2010                  tables

What I am trying to do is if the Table2.Supplies field = Tent then
Skip the Table1.RecordID completely. Currently if I filter out "Tent"
I get
Table1.recordID     Table1. ClientName  Table1.EventDate   Table2.Supplies
5                               Bob's Trucking            5/15/2010                chairs
5                               Bob's Trucking            5/15/2010                tables
6                                Joe's Plumbing           6/5/2010                  chairs
6                                Joe's Plumbing           6/5/2010                  tables

and what I need to get is just

6                                Joe's Plumbing           6/5/2010                  chairs
6                                Joe's Plumbing           6/5/2010                  tables

Basically I need any record from Table1 as long as none of the matching records in Table.Supplies = Tent.

Thanks,
Flanman

or you can do this in a SQL view or command writing your join to handle this issue