delete query from a query

[token_remedie]

Hey, need a little help here, I have a query that finds duplicates and returns a single value in an attempt to identify duplicates in a table.

SELECT DISTINCT Books.[Asset Number], Books.[Serial No], Books.CoCd, Books.Class, Books.[Asset Description], Books.[Invent No], Books.CostCentre, Books.Plnt, Books.Location, Books.[Location Code], Books.[Location Name], Books.FundTyp, Books.ProgSrc, Books.SubClass, Books.Vendor, Books.Manufacturer, Books.Cost, Books.[W Start], Books.Field1, Books.R2, Books.Formula, Books.deployment
FROM Books
WHERE (((Books.[Asset Number]) In (SELECT [Asset Number] FROM [Books] As Tmp GROUP BY [Asset Number],[Serial No] HAVING Count(*)>1  And [Serial No] = [Books].[Serial No])))
ORDER BY Books.[Asset Number], Books.[Serial No];

Now I'm trying to delete those duplicate entries by deleting from another query and it's not working, I'm getting a range of errors from ' cannot delete from the specified table' to just nothing happening, depending on what i play around with. Any ideas?
thanks

DELETE books.*
FROM books, Find_duplicates_for_Books where exists (select * from find_duplicates_for_books);

 

[Guus2005]

If your first query works and produces a unique identifier for duplicate records in the Books table here the query to delete those duplicates:

delete from books where Books.[Asset Number] in (select Books.[Asset Number] from find_duplicates_for_books)

I am not sure which of the fields is a primary key so i am guessing [Asset Number]

HTH

 

[token_remedie]

I ended up using first:

SELECT First(find_books_dupes.[Asset Number]) AS [FirstOfAsset Number], First(find_books_dupes.id) AS FirstOfid
FROM find_books_dupes;

And then a delete

DELETE books.*, books.ID
FROM books
WHERE (((books.ID) In (SELECT  firstofid FROM firstoffbooks)));

Problem was there was NO primary key! so slap on the wrist for that, it caused issues so I just put one in and it all worked.

 

[Guus2005]

when you have cleanup all the duplicates, start working on the design to prevent it from happening. . . and add some primary keys while you're at it.

Ka'Plah!

 

[token_remedie]

its an asset database, pretty much like a library catalogue, so to keep a history I was moving the record to the history table, and then putting the new record in the main table. If there's a better way of doing it I'd be interested to hear suggestions