Hi David:
Thanks for your input! I'll definitely take a look at the Microsoft
link you provided. You're right that I was trying to transpose the rows and
columns, and then put them in a report. I came to realize that SQL was not
the way to go to get there. Actually, I ended up using a PivotTable view on
my query, which worked great. It also gives the users the ability to play
with different views of the data. They love that.
Alan
"David Schofield" <d.REMOVEschofield@blueyonder.co.uk> wrote in message
news:41c75497.401976952@localhost...[color=blue]
> Hi
> Excuse me if I top-post an answer to this one.
> I think what you are seeking to do is to transpose rows and columns.
> This is not exactly what pivot does.
>
> See
http://support.microsoft.com/?kbid=202176#6 for two ways to do it
> for a table. The excel way shown there is suitable for one-offs only,
> you should use the function modified to use your query rather than a
> table as input.
>
> David
>
>
> On Mon, 20 Dec 2004 04:39:41 GMT, "Alan Lane" <awelane@yahoo.com>
> wrote:
>[color=green]
>>Hello world -- third time!
>>
>> OK. Dump the Microsoft Word. Back to the ASCII text editor.
>>Let's see if I can make this stuff more readable for you folks. First,
>>here's the code:
>>
>>Sub OldRegionReport()
>> On Error GoTo ProcErr
>> Dim strMarket As String
>> Dim strRegion As String
>> Dim strPeriod As String
>> Dim strCriteria As String
>> Dim strSQL As String
>> Dim dbs as DAO.Database
>> Dim qdfReportQuery as DAO.QueryDef
>> strCriteria = ","
>> Set dbs = CurrentDb()
>> If m_bytMarketRegionOldFlag Then
>> '--Load selections from Market/Region listbox into variable
>> For Each varItem In Me!lstMarketRegionOld.ItemsSelected
>> strRegion = strRegion & "'" & Me!lstMarketRegionOld.Column(2,
>>varItem) & "'"
>> strRegion = strRegion & strCriteria
>> strMarket = strMarket & "'" &
>>Me!lstMarketRegionOld.ItemData(varItem) & "'"
>> strMarket = strMarket & strCriteria
>> Next varItem
>> strRegion = Left$(strRegion, Len(strRegion) - Len(strCriteria))
>> strMarket = Left$(strMarket, Len(strMarket) - Len(strCriteria))
>> '--Load selections from Period listbox into variable
>> For Each varItem In Me!lstPeriod.ItemsSelected
>> strPeriod = strPeriod & "'" & Me!lstPeriod.ItemData(varItem) &
>>"'"
>> strPeriod = strPeriod & strCriteria
>> Next varItem
>> strPeriod = Left$(strPeriod, Len(strPeriod) - Len(strCriteria))
>> '--Create SQL query string
>> strSQL = ""
>> strSQL = strSQL & "SELECT qryBase.REGION_OLD, qryBase.PERIOD,
>>qryBase.MARKET_NAME, "
>> strSQL = strSQL & "qryBase.BOP_SUBS, qryBase.GROSS_ADDS,
>>qryBase.DEACTS, EOP_SUBS "
>> strSQL = strSQL & "FROM qryBase "
>> strSQL = strSQL & "WHERE qryBase.REGION_OLD IN (" & strRegion & ")
>> "
>> strSQL = strSQL & "AND qryBase.PERIOD IN (" & strPeriod & ") "
>> strSQL = strSQL & "AND qryBase.AIRPORT_CODE IN (" & strMarket &
>> ");"
>> '--Create the QueryDef if it doesn't exist
>> On Error Resume Next
>> dbs.QueryDefs.Refresh
>> Set qdfReportQuery = dbs.QueryDefs("qryOldRegionReport")
>> If (3265 = Err) Then
>> Set qdfReportQuery = dbs.CreateQueryDef("qryOldRegionReport")
>> dbs.QueryDefs.Refresh
>> Err = 0
>> End If
>> On Error GoTo ProcErr
>> '--Load the Query with the SQL string
>> qdfReportQuery.SQL = strSQL
>> dbs.QueryDefs.Refresh
>> qdfReportQuery.Close
>> '--Run the query
>> DoCmd.OpenQuery "qryOldRegionReport"
>> '--Close and free memory of all objects
>> dbs.Close
>> Set dbs = Nothing
>> qdfReportQuery.Close
>> Set qdfReportQuery = Nothing
>> End If
>>ProcExit:
>> Exit Sub
>>ProcErr:
>> Select Case Err
>> Case 0
>> Resume ProcExit
>> Case Else
>> MsgBox Err.Number & " " & Err.Description
>> Resume ProcExit
>> End Select
>> End Sub
>>
>> Above code produces below output:
>>REGION_OLD MARKET_NAME PERIOD BOP_SUBS GROSS_ADDS REACTS DEACTS
>>EOP_SUBS
>>Western Albuquerque 200401 57651
>>2722
>>1266 -1948 41990
>>Western Albuquerque 200402 64644
>>2135
>>1068 -3006 56495
>>Western Albuquerque 200403 64586
>>2750
>>1440 -2585 57651
>>Western Boise 200401 65833
>>3458 1221 -3582 58269
>>Western Boise 200402 20551
>>3091 1400 -4029 42440
>>Western Boise 200403 20439
>>795 1079 -3611 64586
>>Central Wichita 200401 20632
>>929 384 -3228 65833
>>Central Wichita 200402 41402
>>860 469 -1291 66775
>>Central Wichita 200403 41181
>>1350 447 -1205 20439
>>Central Tulsa 200401 41990
>>1912 906 -956 20632
>>Central Tulsa 200402 56693
>>1615 1011 -2477 20983
>>Central Tulsa 200403 56495
>>2118 783 -2114 41181
>>
>> What I want the output to look like is:
>>REGION_OLD Western Western Western
>>Western Western Western
>>MARKET_NAME Albuquerque Albuquerque Albuquerque Boise
>>Boise Boise
>>PERIOD 200401 200402 200403
>>200401 200402 200403
>>BOP_SUBS 57651 64644 64586
>>65833 20551 20439
>>GROSS_ADDS 2722 2135 2750
>>3458 3091 795
>>REACTS 1266 1068 1440
>>1221 1400 1079
>>DEACTS -1948 -3006 -2585
>> -3582 -4029 -3611
>>EOP_SUBS 41990 56495 57651
>>58269 42440 64586
>>
>> What I can't figure out is how to get from where I am to where I want
>> to
>>go in SQL. I know it has something to do with
>>TRANSFORM and PIVOT, but I can't seem to make it work. Please help!
>>
>> Alan
>>
>>"Alan Lane" <awelane@yahoo.com> wrote in message
>>news:6Drxd.23334$gd.18936@twister.socal.rr.com.. .[color=darkred]
>>> Hello world:
>>>
>>> I'm including both code and examples of query output. I appologize
>>> if
>>> that makes this message longer than it should be.
>>> Anyway, I need to change the query below into a pivot table query. I'm
>>> having trouble doing it. Help!
>>>
>>> Here is my code so far:
>>>
>>> Sub OldRegionQuery()
>>>
>>> On Error GoTo ProcErr
>>>
>>> Dim strMarket As String
>>>
>>> Dim strRegion As String
>>>
>>> Dim strPeriod As String
>>>
>>> Dim strCriteria As String
>>>
>>> Dim strSQL As String
>>>
>>> Dim dbs as DAO.Database
>>>
>>> Dim qdfReportQuery as DAO.QueryDef
>>>
>>> strCriteria = ","
>>>
>>> Set dbs = CurrentDb()
>>>
>>> If m_bytMarketRegionOldFlag Then
>>>
>>> '--Load selections from Market/Region listbox into variable
>>>
>>> For Each varItem In Me!lstMarketRegionOld.ItemsSelected
>>>
>>> strRegion = strRegion & "'" & Me!lstMarketRegionOld.Column(2,
>>> varItem) & "'"
>>>
>>> strRegion = strRegion & strCriteria
>>>
>>> strMarket = strMarket & "'" &
>>> Me!lstMarketRegionOld.ItemData(varItem) & "'"
>>>
>>> strMarket = strMarket & strCriteria
>>>
>>> Next varItem
>>>
>>> strRegion = Left$(strRegion, Len(strRegion) - Len(strCriteria))
>>>
>>> strMarket = Left$(strMarket, Len(strMarket) - Len(strCriteria))
>>>
>>> '--Load selections from Period listbox into variable
>>>
>>> For Each varItem In Me!lstPeriod.ItemsSelected
>>>
>>> strPeriod = strPeriod & "'" & Me!lstPeriod.ItemData(varItem)
>>> &
>>> "'"
>>>
>>> strPeriod = strPeriod & strCriteria
>>>
>>> Next varItem
>>>
>>> strPeriod = Left$(strPeriod, Len(strPeriod) - Len(strCriteria))
>>>
>>> '--Create SQL query string
>>>
>>> strSQL = ""
>>>
>>> strSQL = strSQL & "SELECT qryBase.REGION_OLD, qryBase.PERIOD,
>>> qryBase.MARKET_NAME, "
>>>
>>> strSQL = strSQL & "qryBase.BOP_SUBS, qryBase.GROSS_ADDS,
>>> qryBase.DEACTS, EOP_SUBS "
>>>
>>> strSQL = strSQL & "FROM qryBase "
>>>
>>> strSQL = strSQL & "WHERE qryBase.REGION_OLD IN (" & strRegion &
>>> ")
>>> "
>>>
>>> strSQL = strSQL & "AND qryBase.PERIOD IN (" & strPeriod & ") "
>>>
>>> strSQL = strSQL & "AND qryBase.AIRPORT_CODE IN (" & strMarket &
>>> ");"
>>>
>>> '--Create the QueryDef if it doesn't exist
>>>
>>> On Error Resume Next
>>>
>>> dbs.QueryDefs.Refresh
>>>
>>> Set qdfReportQuery = dbs.QueryDefs("qryOldRegionReport")
>>>
>>> If (3265 = Err) Then
>>>
>>> Set qdfReportQuery = dbs.CreateQueryDef("qryOldRegionReport")
>>>
>>> dbs.QueryDefs.Refresh
>>>
>>> Err = 0
>>>
>>> End If
>>>
>>> On Error GoTo ProcErr
>>>
>>> '--Load the Query with the SQL string
>>>
>>> qdfReportQuery.SQL = strSQL
>>>
>>> dbs.QueryDefs.Refresh
>>>
>>> qdfReportQuery.Close
>>>
>>> '--Run the query
>>>
>>> DoCmd.OpenQuery "qryOldRegionReport"
>>>
>>> '--Close and free memory of all objects
>>>
>>> dbs.Close
>>>
>>> Set dbs = Nothing
>>>
>>> qdfReportQuery.Close
>>>
>>> Set qdfReportQuery = Nothing
>>>
>>> End If
>>>
>>> ProcExit:
>>>
>>> Exit Sub
>>>
>>> ProcErr:
>>>
>>> Select Case Err
>>>
>>> Case 0
>>>
>>> Resume ProcExit
>>>
>>> Case Else
>>>
>>> MsgBox Err.Number & " " & Err.Description
>>>
>>> Resume ProcExit
>>>
>>> End Select
>>>
>>> End Sub
>>>
>>>
>>>
>>> With the right choices in a couple of listboxes,
>>>
>>> the above routine outputs the below data.
>>>
>>>
>>>
>>> REGION_
>>> OLD
>>> MARKET_
>>> NAME
>>> PERIOD
>>> BOP_
>>> SUBS
>>> GROSS_
>>> ADDS
>>> REACTS
>>> DEACTS
>>> EOP_
>>> SUBS
>>>
>>> Western
>>> Albuquerque
>>> 200401
>>> 57651
>>> 2722
>>> 1266
>>> -1948
>>> 41990
>>>
>>> Western
>>> Albuquerque
>>> 200402
>>> 64644
>>> 2135
>>> 1068
>>> -3006
>>> 56495
>>>
>>> Western
>>> Albuquerque
>>> 200403
>>> 64586
>>> 2750
>>> 1440
>>> -2585
>>> 57651
>>>
>>> Western
>>> Boise
>>> 200401
>>> 65833
>>> 3458
>>> 1221
>>> -3582
>>> 58269
>>>
>>> Western
>>> Boise
>>> 200402
>>> 20551
>>> 3091
>>> 1400
>>> -4029
>>> 42440
>>>
>>> Western
>>> Boise
>>> 200403
>>> 20439
>>> 795
>>> 1079
>>> -3611
>>> 64586
>>>
>>> Central
>>> Wichita
>>> 200401
>>> 20632
>>> 929
>>> 384
>>> -3228
>>> 65833
>>>
>>> Central
>>> Wichita
>>> 200402
>>> 41402
>>> 860
>>> 469
>>> -1291
>>> 66775
>>>
>>> Central
>>> Wichita
>>> 200403
>>> 41181
>>> 1350
>>> 447
>>> -1205
>>> 20439
>>>
>>> Central
>>> Tulsa
>>> 200401
>>> 41990
>>> 1912
>>> 906
>>> -956
>>> 20632
>>>
>>> Central
>>> Tulsa
>>> 200402
>>> 56693
>>> 1615
>>> 1011
>>> -2477
>>> 20983
>>>
>>> Central
>>> Tulsa
>>> 200403
>>> 56495
>>> 2118
>>> 783
>>> -2114
>>> 41181
>>>
>>>
>>>
>>>
>>>
>>> What I need to output is below.
>>>
>>>
>>>
>>> REGION_OLD
>>> Western
>>> Western
>>> Western
>>> Western
>>> Western
>>> Western
>>> Central
>>> Central
>>> Central
>>> Central
>>> Central
>>> Central
>>>
>>> MARKET_NAME
>>> Albuquerque
>>> Albuquerque
>>> Albuquerque
>>> Boise
>>> Boise
>>> Boise
>>> Wichita
>>> Wichita
>>> Wichita
>>> Tulsa
>>> Tulsa
>>> Tulsa
>>>
>>> PERIOD
>>> 200401
>>> 200402
>>> 200403
>>> 200401
>>> 200402
>>> 200403
>>> 200401
>>> 200402
>>> 200403
>>> 200401
>>> 200402
>>> 200403
>>>
>>> BOP_SUBS
>>> 57651
>>> 64644
>>> 64586
>>> 65833
>>> 20551
>>> 20439
>>> 20632
>>> 41402
>>> 41181
>>> 41990
>>> 56693
>>> 56495
>>>
>>> GROSS_ADDS
>>> 2722
>>> 2135
>>> 2750
>>> 3458
>>> 3091
>>> 795
>>> 929
>>> 860
>>> 1350
>>> 1912
>>> 1615
>>> 2118
>>>
>>> REACTS
>>> 1266
>>> 1068
>>> 1440
>>> 1221
>>> 1400
>>> 1079
>>> 384
>>> 469
>>> 447
>>> 906
>>> 1011
>>> 783
>>>
>>> DEACTS
>>> -1948
>>> -3006
>>> -2585
>>> -3582
>>> -4029
>>> -3611
>>> -3228
>>> -1291
>>> -1205
>>> -956
>>> -2477
>>> -2114
>>>
>>> EOP_SUBS
>>> 41990
>>> 56495
>>> 57651
>>> 58269
>>> 42440
>>> 64586
>>> 65833
>>> 66775
>>> 20439
>>> 20632
>>> 20983
>>> 41181
>>>
>>>
>>>
>>>
>>>
>>> How do I get from where I am now to where I want to go? Any help is
>>> greatly appreciated.
>>>
>>>
>>>
>>> Alan
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>[/color]
>>
>>[/color]
>[/color]
