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

help deleting records that match between two tables

P: 55
I am having problems with some of my queries. I have a database(table1) that has two fields in it(field1, field2). Field 1 has repeating records. I used this sql to pull the first record out of table1 and creates table2:
Expand|Select|Wrap|Line Numbers
  1. SELECT First(table1.field1) AS row1, First(table1.field2) AS row2 INTO Table2
  2. FROM table1
  3. GROUP BY table1.field1;
That works great for me. What i am then needing to delete those records that were just copied to table2. I can figure it out for some reason. I am guessing I need to create a relationship between the tables, but i am lost from there on.

If you guys could help me out, I would greatly appreciate it.

thanks
Dec 31 '07 #1
Share this Question
Share on Google+
4 Replies


puppydogbuddy
Expert 100+
P: 1,923
I am having problems with some of my queries. I have a database(table1) that has two fields in it(field1, field2). Field 1 has repeating records. I used this sql to pull the first record out of table1 and creates table2:
Expand|Select|Wrap|Line Numbers
  1. SELECT First(table1.field1) AS row1, First(table1.field2) AS row2 INTO Table2
  2. FROM table1
  3. GROUP BY table1.field1;
That works great for me. What i am then needing to delete those records that were just copied to table2. I can figure it out for some reason. I am guessing I need to create a relationship between the tables, but i am lost from there on.

If you guys could help me out, I would greatly appreciate it.

thanks
See this link for a step by step:
http://www.fabalou.com/Access/Querie...pe_records.asp
Dec 31 '07 #2

P: 55
thanks for the help. I got the first two queries to work, but the last one keeps making access stop responding. I think I might be doing something wrong.

Here is my first query(qry_List_Of_Firsts:
Expand|Select|Wrap|Line Numbers
  1. SELECT First(Table1.ID) AS FirstOfID, Table1.Field1
  2. FROM Table1
  3. GROUP BY Table1.Field1;
  4.  
Second Query(qry_List_To_Delete):
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.ID
  2. FROM Table1 LEFT JOIN qry_List_of_Firsts ON Table1.ID = qry_List_of_Firsts.FirstOfID
  3. WHERE (((qry_List_of_Firsts.FirstOfID) Is Null));
  4.  
And my Third Query(qry_delete)
Expand|Select|Wrap|Line Numbers
  1. DELETE Table1.*, Table1.ID
  2. FROM Table1
  3. WHERE (((Table1.ID)=DLookUp("ID","qry_List_To_Delete","ID =" & [ID])));
The query gets about 25% done and then makes access stop responding. I used the number fields from the link above, i'll try the text one. I used number because my ID field is autonumber.

Any suggestions? Thanks.
Dec 31 '07 #3

P: 55
I did not find a solution to the particular problem I was doing. I did however find a work around, I just took the third query and created a table with the results, instead of deleting from the original table.
Dec 31 '07 #4

puppydogbuddy
Expert 100+
P: 1,923
I did not find a solution to the particular problem I was doing. I did however find a work around, I just took the third query and created a table with the results, instead of deleting from the original table.
A lot of DLookups can cause problems....I would do this:
1. Make a copy of your second query (qry_ListsToDelete) and rename it qryDeletedList.
2. Place this query in design view and go to the Access command menu and select delete from the query button list to change the query from a select query to a delete query. Save as your delete query.
3. Make sure your mdb is backed up, then execute your delete query.

Happy New Year!!!
Jan 1 '08 #5

Post your reply

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