help deleting records that match between two tables

barmatt80

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

 SELECT First(table1.field1) AS row1, First(table1.field2) AS row2 INTO Table2

FROM table1

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

puppydogbuddy

Quote:

Originally Posted by barmatt80

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

 SELECT First(table1.field1) AS row1, First(table1.field2) AS row2 INTO Table2

FROM table1

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

barmatt80

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

 SELECT First(Table1.ID) AS FirstOfID, Table1.Field1

FROM Table1

GROUP BY Table1.Field1;

Second Query(qry_List_To_Delete):

Expand|Select|Wrap|Line Numbers

 SELECT Table1.ID

FROM Table1 LEFT JOIN qry_List_of_Firsts ON Table1.ID = qry_List_of_Firsts.FirstOfID

WHERE (((qry_List_of_Firsts.FirstOfID) Is Null));

And my Third Query(qry_delete)

Expand|Select|Wrap|Line Numbers

 DELETE Table1.*, Table1.ID

FROM Table1

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.

barmatt80

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.

puppydogbuddy

Quote:

Originally Posted by barmatt80

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!!!

help deleting records that match between two tables

re: help deleting records that match between two tables

re: help deleting records that match between two tables

re: help deleting records that match between two tables

re: help deleting records that match between two tables

/bytes/development

/bytes/it

/bytes/about