How can I sum and replace column data and do averaging all in a MS Access query?

Question

I am pulling data from one table, called analyzedCopy, and using it to over-rite all information in another table called analyzed. The columns I'm sorting/filtering/manipulating, are readings_miu_id, ReadDate, ReadTime, RSSI. I am currently moving data from analyzedCopy to analyzed using the following sql that to get only the last time(highest value in ReadTime since it is formatted to military time) for each unique pair of readings_miu_id and ReadDate.

SELECT readings_miu_id, Reading, ReadDate, ReadTime, MIUwindow, SN, Noise, RSSI, ColRSSI,MIURSSI,Firmware,CFGDate,FreqCorr,Active,MeterType,OriginCol,ColID,Ownage,SiteID,PremID, prem_group1, prem_group2,ReadID  
    INTO analyzed  
    FROM analyzedCopy AS A  
    WHERE  ReadDate BETWEEN #04/21/09# AND #04/29/09#  AND ReadTime=  (SELECT TOP 1 analyzedCopy.ReadTime FROM analyzedCopy  
    WHERE analyzedCopy.readings_miu_id = A.readings_miu_id  AND analyzedCopy.ReadDate = A.ReadDate  
    ORDER BY analyzedCopy.readings_miu_id, analyzedCopy.ReadDate, analyzedCopy.ReadTime DESC)

   ORDER BY A.readings_miu_id, A.ReadDate, A.ReadTime DESC ;

I need to add to this code the ability to put into the table "analyzed" one record per readings_miu_id while changing the ReadDate to show the desired date range (in this case it would need to show something like "4/21/09 to 4/29/09") and while also taking the average of the values in RSSI for each DISTINCT reading_miu_id and inserting that average RSSI into the RSSI field in the analyzed table.

In an attempt to recap/overview, I have a code to insert into a table all of the records from another table where ReadTime is the highest for each unique readings_miu_id and ReadDate combonation. And I need to add to this code the ability to take the average RSSI of the already sorted (the step in the previous sentence) records and insert that average into RSSI in analyzed and insert the date range into the ReadDate in analyzed.

I realize that I'm probably asking for alot here and if i need to use two or three different steps or SQL code thats fine. the program this is going into will not be used by many people and my boss doesn't care if the code is messy or that it will take a little while to run. I cringe at being forced to do things so inefficiently and messy, but it is what I am being forced to do.

I should be able to use the sum() feature in there to sum up the RSSI values im just not sure how to do that while dividing the sum by the count for each DISTINCT readings_miu_id and while doing everything else I need done to this data. Using VB.NET 2008 and sqlServer i could run each line through some logic statements to do what i need to to the data but I don't know how to do that in an SQL or VBA.

---

Edit:

If I didn't explain something well enough, please let me know and I will try to correct and add info to the best of my abilities.

Answer 1

Tackle the problem one issue at a time. Create tables for each step, AnalyzedStep1, AnalyzedStep2, and so on.