By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,825 Members | 1,697 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,825 IT Pros & Developers. It's quick & easy.

Delete records from main table if subtable is empty

P: 74
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.
Aug 2 '09 #1
Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,615
@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?
Aug 3 '09 #2

P: 74
@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. tblOpenOrderDetails
3. tblOpenOrders.WorkOrder (Long Integer), tblOpenOrderDetails.WorkOrderDet (Long Integer)
4. I really dont think anything else would be relevant.
5. Definately

Thanks for responding! :)
Aug 3 '09 #3

ADezii
Expert 5K+
P: 8,615
@kstevens
The following SQL should indicate the Records that have to be Deleted:
Expand|Select|Wrap|Line Numbers
  1. SELECT  * FROM tblOpenOrders
  2. LEFT JOIN tblOpenOrderDetails ON tblOpenOrders.WorkOrder = tblOpenOrderDetails.WorkOrderDet
  3. WHERE tblOpenOrderDetails.WorkOrderDet Is Null;
Aug 4 '09 #4

P: 74
I tried this approach.....this 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.
Aug 4 '09 #5

ADezii
Expert 5K+
P: 8,615
@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?
Aug 4 '09 #6

P: 74
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.
Aug 4 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.