Delete records from main table if subtable is empty

[kstevens]

I have tables setup with a main table for information and a subtable that records the "multiple" records for the main record. I have written a query to go in and find Null or "" values to delete records that somebody started and closed out of, but then I realized that it wont delete the main records that information is recorded in if the sub record only contains 1 New record, because they arent neccesarily Null or "".

Does anybody have any ideas about how to accomplish this. As always any help is appreciated.

[ADezii]

@kstevens

1. What is the Main Table Name?
2. What is the Child Table Name?
3. What is the Name and Data Type of the Linking Fields in both Tables?
4. Any other information that you think is relevant
5. For each Record in the Main Table, if no Child Records exist, Delete the Record in the Main Table? Is this your question?

[kstevens]

@ADezii
This is correct. I was really looking for the criteria that i would need to put in a select query to show me the results, then i can delete the records from the select query. This is maintenance that would only be done by me, and wont involve other people.

1. tblOpenOrders
2. tblOpenOrderDet ails
3. tblOpenOrders.W orkOrder (Long Integer), tblOpenOrderDet ails.WorkOrderD et (Long Integer)
4. I really dont think anything else would be relevant.
5. Definately

Thanks for responding! :)

[ADezii]

@kstevens
The following SQL should indicate the Records that have to be Deleted:

SELECT  * FROM tblOpenOrders
LEFT JOIN tblOpenOrderDetails ON tblOpenOrders.WorkOrder = tblOpenOrderDetails.WorkOrderDet
WHERE tblOpenOrderDetails.WorkOrderDet Is Null;

[kstevens]

I tried this approach.....th is only works when a sub record has been started, information entered, and deleted (only when the subtable shows 1 record...used empty record and a new record). It does not work if a subrecord has never been touched. I need to find the main records that only have "NewRecord" sub records.

When i use the term "NewRecord" i am referring to an "undirty" record....one that is waiting for information to be entered....not one that is blank.

[ADezii]

@kstevens
The way I interpret this, kstevens, is that you wish to Delete any Main Record, but only for a Sub-Record which technically doesn't even exist except in Memory, for the conditions you specify ('NewRecord/Undirty'). Is my thinking correct on this matter?

[kstevens]

Yes....and when i say "undirty" (funny word, lol) i am referring to never has been dirty, or just never used.

How about this. What if i build the select query to pull related information from both tables, and just look in one of the fields (that should have information). Would the select query look at the related field (no, not the linking field) as a null value, or a newrecord.....I will try this later. My current query is just looking at the subrecord (table) not both together.....i think, i guess i better check on that too.