check for data

[Gary Dolliver]

Hi all,
I currently have 3 tables (order, creations, items) that I would like to
check and see if there are corresponding records in each. For example, I
would like to check and see if there are items for a specific order in the
items table, or the creations table. Is there a way I could have it show a
result set of all the orders that do not have a creation, or do not have an
item? the linking fields are Order_ID, Creation_ID, and Item_ID - how would
i set this up?
please help, thanks!
-gary

 

[Klatuu]

Create a query linking all 3 tables, select the fields you need.
Make the order table the left most table and link the other tables to it.
Edit the join and select #2.
Then in the Criteria row for the Creation_ID and Item_ID fields, enter
Is Null. Put each on a different line in the critera section of the query
builder so it will make it a Or>

Field: Creation_ID Item_ID
Criteria: Is Null
Is Null

It will return rows where one or both of the fields is Null

 

[Gary Dolliver]

awesome! thank you!
Now, is there a way I can delete these records, in the orders, creations or
item tables from this result set?
thanks!
-gary

 

[Klatuu]

Yes, but the query would be different for each table. For the orders table
you could just change it to a delete query and it would delete from the
orders table. To delete from the item table, make the item table the left
table, do the join to order table, and use the same type of criteria. Same
for the Creation table.