How do I put data from multiple records into different columns?
- by Bryan
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
