MS-ACCESS: Remove all rows except the first and update the table from the query
I'm almost done with that, just a few last hiccups. Now I need to delete all records from the table except the first one, where readings_miu_id is the "DISTINCT" column. In other words, I need to delete all records from a table other than the first DISTINCT readings_miu_id. I guess all I have to do is change the underlying delete operator:
DELETE FROM analyzedCopy2
WHERE readings_miu_id = some_value
But I can't 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 (parsed by Copy2) from a query (where currently all the values I want to store in the RSSI column in the table parsed by Copy2 are 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 apparently I cannot use FROM inside the update statement. Any thoughts?
I'm sure I'm going to make this very non-standard (and possibly, if not possible, erroneous), but I'm not allowed to use vb.net2008 to pull and manipulate and then save the data as I would like to get me stuck right now using SQL statements in ms-access which is a good learning experience (even if you try to do strange things like I had to do in SQL statements makes me bang my head my deck figuratively of course)
a source to share
MS Access SQL UPDATE statements cannot refer to queries, but can refer to tables. So, what needs to be done is to save the query results to a table.
SELECT YourQuery.*
INTO TempTable1
FROM YourQuery
Now you can use TempTable1 in your UPDATE query:
UPDATE TargetTable
INNER JOIN TempTable1 ON TempTable1.TargetTableId = TargetTable.Id
SET TargetTable.TargetField = TempTable1.SourceField
a source to share
I don't have a copy of access on this machine, and it's been a few years since I missed access, so I'm taking a wild hit here, but you can do
delete from analyzedCopy2
where readings_miu_id not in (select top 1 readings_miu_id from analyzedCopy2 order by...)
(you will need an order to get the top 1 record correct, order by id maybe?)
I have no hope of helping you with the second without copy access. I know how to do this in TSQL, but access is a completely different wtf kettle :-)
a source to share
I was trying to make it overly complex since all the records I needed to pull out had the same information in every field 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;
to pull the first 1 record per read_miu_id.
a source to share