Delete Old Records

[dynamictiger]

I am having a lot of fun with this query design.

I have a list of results for a client.

I have sorted these into the most recent 5 results, by ID.

Now I want to delete all records for this client not in this list.

However all efforts generate an SQL error???

 

[Geoff Codd]

if i understand you correctly you have a query showing the records you want to keep from the table. if you save this and create an unmatched query from the wizard using the table as one source and the query as the second source it should give you a list of records not in the query. you can then change this to a delete query and delete all the unmatched records

 

[dynamictiger]

Thanks for the suggestion I tried this and I got an error, asking which table I would to delete the data from.

 

[Geoff Codd]

sorry my fault, because you unmatched query is based on 2 tables access does't know what table to delete from, the soulution is to create a delete query based on the unmatched query. and just select the unique id field which is contained in the main table

 

[Geoff Codd]

ignore my last post as that won't work either, there is a way to do it i'm just struggling to work it ou

 

[dynamictiger]

Me too!!!

 

[Geoff Codd]

go this from help i tried it out and it worked fine

When a delete query contains more than one table, such as a query that deletes duplicate records from one of the tables, the query’s UniqueRecords property must be set to Yes. For more information on setting the UniqueRecords property, click .

Prevent duplicate records in a query based on fields in the underlying table
Open a query in Design view.


Select the query by clicking anywhere in query Design view outside the design grid and the field lists.


Click Properties on the toolbar to display the query's property sheet.


Set the UniqueRecords property to Yes.
Notes

The UniqueRecords property has an effect only when you use more than one table in the query and select fields from those tables.


When the UniqueRecords property is set to Yes, Microsoft Access automatically sets the UniqueValues property to No.


As an alternative to setting the UniqueRecords property, you can add to the design grid the field that distinguishes one record from another.

 

[dynamictiger]

Thanks for the suggestion. Unique records only apply to select, append and make table queries.

This is still not solved my problem. I tried various combinations including making a new unmatched query and setting it's unique records to yes, then running a delete statement on that and got the same error.

 

[dynamictiger]

I just solved it.

You find the records you want to delete using the unmatched records.

You create a delete query based on the table and use the unmatched ID as the criteria. Duh