what's the best way to do this?

[token_remedie]

bit of a noob...
so I'm working with access's template of the library database, and so far so good, I've managed to import data into a new table to work with in the hopes of cleaning it and putting it in the main table called books. So the import table is called importtable, main table is books, and the history table is called transactions.

What I'm trying to do is this:

if books.[asset number] Inner Join importtable.[serial number]
count < 1 check books.[location name] against importtable.[location name]
if they're equal then delete the record from importtable.

Else copy the qhole record from books to transactions delete the entry from books and append the entry from importtable to books.

the hope is that basically if an asset has moved locations that will show up in transactions, and only the most recent location will be in the main table.
I've kind of worked out the SQL for it, but it returns 0 entries, and asks if I want to update this table with 0 entries....so it's wrong somewhere, maybe I've been staring at it all too long. Ideally I'd like to have it in VBA but if I can just have a query and use docmd.runquery then that's fine too.

Here's my dodgy code so far:

INSERT INTO Transactions ( [Asset Number], [Location Code], [Serial No] )
SELECT Books.[Asset Number], Books.[Location Code], Count(Books.[Serial No]) AS [CountOfSerial No]
FROM Books INNER JOIN importtable ON (Books.[Serial No] = importtable.[Serial No#]) AND (Books.[Asset Number] = importtable.[Asset Number])
GROUP BY Books.[Asset Number], Books.[Location Code]
HAVING (((Count(Books.[Asset Number]))>1));

 

[token_remedie]

I actually think I did it:

INSERT INTO Transactions ( CoCd, Class, [Asset Description], [Invent No], CostCentre, Plnt, Location, [Location Code], [Location Name], FundTyp, ProgSrc, SubClass, Vendor, Manufacturer, Cost, [W Start], Remarks, R2, Formula, [Asset Number] )
SELECT importtable.CoCd, importtable.Class, importtable.[Asset Description], importtable.[Invent No], importtable.CostCentre, importtable.Plnt, importtable.Location, importtable.[Location Code], importtable.[Location Name], importtable.FundTyp, importtable.ProgSrc, importtable.SubClass, importtable.Vendor, importtable.Manufacturer, importtable.Cost, importtable.[W Start], importtable.Remarks, importtable.R2, importtable.Formula, importtable.[Asset Number]
FROM Books LEFT JOIN importtable ON Books.[Asset Number] = importtable.[Asset Number] AND importtable.[serial No#] = books.[serial no]
WHERE (((importtable.[Asset Number]) Is Not Null)AND books.[serial no] Is Not Null);