tags:

views:

192

answers:

1

My two tables are titled analyzed and analyzedCopy3. I'm trying to put information from analyzedCopy3 into multiple columns in analyzed.

Sample data from analyzedCopy3:

    
readings_miu_id              OriginalCol               ColRSSIz
110001366                    Frederick Road            -108    
110001366                    Steel Street   
110001366                    Fifth Ave.
110001508                    Steel Street              -104   

What I want to do is put the top 3 OriginalCol, ColRSSIz combinations into columns that I have in the table analyzed. In analyzed there is only one record for each unique readings_miu_id.

Any ideas? Thanks in advance.

Additional Info:
By "top 3 OriginalCol, ColRSSIz combinations" I mean the first 3 combinations with the highest value in the ColRSSIz column. For any readings_miu_id there could be anywhere from 1 row of information to 6 rows of information. So at most I'm only wanting the top 3. If there is less than 3 rows for the readings_miu_id then the other columns need to be blank.

Query that generates the table "analyzed":

strSql4 = " SELECT 
                 readings_miu_id,
                 Count(readings_miu_id) as NumberOfReads, 
                 First(PercentSuccessz) as PercentSuccess, 
                 First(Readingz)as Reading,
                 First(MIUwindowz) as MIUwindow,
                 First(SNz) as SN,
                 First(Noisez) as Noise,
                 First(RSSIz) as RSSI,
                 First(ColRSSIz) as ColRSSI,
                 First(MIURSSIz) as MIURSSI, 
                 First(Col1z) as Col1, 
                 First(Col1RSSIz) as Col1RSSI, 
                 First(Col2z) as Col2, 
                 First(Col2RSSIz) as Col2RSSI, 
                 First(Col3z) as Col3, 
                 First(Col3RSSIz) as Col3RSSI, 
                 First(Firmwarez) as Firmware,
                 First(CFGDatez) as CFGDate,
                 First(FreqCorrz) as FreqCorr,
                 First(Activez) as Active,
                 First(MeterTypez) as MeterType,
                 First(OriginColz) as OriginCol,
                 First(ColIDz) as ColID,
                 First(Ownagez) as Ownage,
                 First(SiteIDz) as SiteID,
                 First(PremIDz) as PremID,
                 First(prem_group1z) as prem_group1, 
                 First(prem_group2z) as prem_group2,
                 First(ReadIDz) as ReadID,
                 First(prem_addr1z) as prem_addr1 " & _
          "INTO analyzed " & _
          "FROM analyzedCopy2 " & _
          "GROUP BY readings_miu_id, PremIDz; "

DoCmd.SetWarnings False
DoCmd.RunSQL strSql4
DoCmd.SetWarnings True
A: 

Based on the tags I'm guessing that you're using MS Access.

I think your best bet may be to use a cursor to run through the records one at at a time, ordered by readings_miu_id, ColRSSIz.

Crappy Coding Guy