MS-ACCESS: deleting all rows execpt for top 1 and updating a table from a query.

Question

I'm almost done with this, just a few last hiccups. I now need to delete all records from a table except for the top 1 where readings_miu_id is the "DISTINCT" column. In other words words i need to delete all records from a table other than the first DISTINCT readings_miu_id. I am assuming all i need to do is modify the basic delete statement:

DELETE FROM analyzedCopy2
WHERE readings_miu_id = some_value

but i cant figure out how to change the some_column=some_value part to something like:

where some_column notequal to (select top 1 from analyzedCopy2 as A 
where analyzedCopy2.readings_miu_id = A.readings_miu_id)

and then i need to figure out how to use an UPDATE statement to update a table (analyzedCopy2) from a query (which is where all of the values i want stored into column RSSI in table analyzedCopy2 are currently located). I've tried this:

UPDATE analyzedCopy2 from testQuery3 SET analyzedCopy2.RSSI = 
(select AvgOfRSSI from testQuery3 INNER JOIN  analyzedCopy2 on analyzedCopy2.readings_miu_id =  testQuery3.readings_miu_id where analyzedCopy2.readings_miu_id = testQuery3.readings_miu_id) 
where analyzedCopy2.readings_miu_id = testQuery3.readings_miu_id

but apparantly i cant use FROM inside of an update statement. Any thoughts?

I'm sure i'm going about this a very nonstandard (and possibly if not probably the flat out wrong) way but im not being allowed to use vb.net2008 to pull and manipulate then store the data like i would like to so i'm stuck right now using sql statements in ms-access which is a good learning experience (Even if trying to do such odd things as i've been having to do in sql statements is making me beat my head against my deck figuratively of course)

Answer 1

I don't have a copy of access on this machine, and it's been a few years since I dabbled in access, so I'm taking a wild stab here, but can you do a

delete from analyzedCopy2
where readings_miu_id not in (select top 1 readings_miu_id from analyzedCopy2 order by...)

(you'll need the order by to get the proper top 1 record, order by the id maybe?)

I've got no hope of helping you with the second one without a copy of access. I know how I'd do it in TSQL, but access is a whole different kettle of wtf's :-)

Answer 2

MS Access UPDATE sql statements cannot reference queries, but they can reference tables. So the thing to do is store the query results into a table.

SELECT YourQuery.* 
INTO TempTable1
FROM YourQuery

Now you can use TempTable1 in an UPDATE query:

UPDATE TargetTable
INNER JOIN TempTable1 ON TempTable1.TargetTableId = TargetTable.Id
SET TargetTable.TargetField = TempTable1.SourceField

See my answer to this question.

Answer 3

I was trying to make too complicated, since all of the records that i needed to pull had the same information in each field that i needed all i had to do was use:

   SELECT DISTINCT readings_miu_id, DateRange, RSSI, ColRSSI, Firmware, CFGDate, FreqCorr, Active, OriginCol, ColID, Ownage, SiteID, PremID, prem_group1, prem_group2
FROM analyzedCopy2   
ORDER BY readings_miu_id;

in order to pull the top 1 record per readings_miu_id.