473,573 Members | 2,835 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Access 2003: Request help on pivot table query

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_bytMarketRegi onOldFlag Then

'--Load selections from Market/Region listbox into variable

For Each varItem In Me!lstMarketReg ionOld.ItemsSel ected

strRegion = strRegion & "'" & Me!lstMarketReg ionOld.Column(2 ,
varItem) & "'"

strRegion = strRegion & strCriteria

strMarket = strMarket & "'" &
Me!lstMarketReg ionOld.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.It emsSelected

strPeriod = strPeriod & "'" & Me!lstPeriod.It emData(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_SU BS, qryBase.GROSS_A DDS,
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.R efresh

Set qdfReportQuery = dbs.QueryDefs(" qryOldRegionRep ort")

If (3265 = Err) Then

Set qdfReportQuery = dbs.CreateQuery Def("qryOldRegi onReport")

dbs.QueryDefs.R efresh

Err = 0

End If

On Error GoTo ProcErr

'--Load the Query with the SQL string

qdfReportQuery. SQL = strSQL

dbs.QueryDefs.R efresh

qdfReportQuery. Close

'--Run the query

DoCmd.OpenQuery "qryOldRegionRe port"

'--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














Nov 13 '05 #1
4 2852
Hello world again:

Sorry for the way the output looks. It looked fine when I sent it.
Anyway, it should still be possible to see from the code what the output
would be. Please help me figure out how to do a SQL pivot table query based
upon this existing query. Thanks.

Alan

"Alan Lane" <aw*****@yahoo. com> wrote in message
news:6D******** **********@twis ter.socal.rr.co m...
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_bytMarketRegi onOldFlag Then

'--Load selections from Market/Region listbox into variable

For Each varItem In Me!lstMarketReg ionOld.ItemsSel ected

strRegion = strRegion & "'" & Me!lstMarketReg ionOld.Column(2 ,
varItem) & "'"

strRegion = strRegion & strCriteria

strMarket = strMarket & "'" &
Me!lstMarketReg ionOld.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.It emsSelected

strPeriod = strPeriod & "'" & Me!lstPeriod.It emData(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_SU BS, qryBase.GROSS_A DDS,
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.R efresh

Set qdfReportQuery = dbs.QueryDefs(" qryOldRegionRep ort")

If (3265 = Err) Then

Set qdfReportQuery = dbs.CreateQuery Def("qryOldRegi onReport")

dbs.QueryDefs.R efresh

Err = 0

End If

On Error GoTo ProcErr

'--Load the Query with the SQL string

qdfReportQuery. SQL = strSQL

dbs.QueryDefs.R efresh

qdfReportQuery. Close

'--Run the query

DoCmd.OpenQuery "qryOldRegionRe port"

'--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















Nov 13 '05 #2
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_bytMarketRegi onOldFlag Then
'--Load selections from Market/Region listbox into variable
For Each varItem In Me!lstMarketReg ionOld.ItemsSel ected
strRegion = strRegion & "'" & Me!lstMarketReg ionOld.Column(2 ,
varItem) & "'"
strRegion = strRegion & strCriteria
strMarket = strMarket & "'" &
Me!lstMarketReg ionOld.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.It emsSelected
strPeriod = strPeriod & "'" & Me!lstPeriod.It emData(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_SU BS, qryBase.GROSS_A DDS,
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.R efresh
Set qdfReportQuery = dbs.QueryDefs(" qryOldRegionRep ort")
If (3265 = Err) Then
Set qdfReportQuery = dbs.CreateQuery Def("qryOldRegi onReport")
dbs.QueryDefs.R efresh
Err = 0
End If
On Error GoTo ProcErr
'--Load the Query with the SQL string
qdfReportQuery. SQL = strSQL
dbs.QueryDefs.R efresh
qdfReportQuery. Close
'--Run the query
DoCmd.OpenQuery "qryOldRegionRe port"
'--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" <aw*****@yahoo. com> wrote in message
news:6D******** **********@twis ter.socal.rr.co m...
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_bytMarketRegi onOldFlag Then

'--Load selections from Market/Region listbox into variable

For Each varItem In Me!lstMarketReg ionOld.ItemsSel ected

strRegion = strRegion & "'" & Me!lstMarketReg ionOld.Column(2 ,
varItem) & "'"

strRegion = strRegion & strCriteria

strMarket = strMarket & "'" &
Me!lstMarketReg ionOld.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.It emsSelected

strPeriod = strPeriod & "'" & Me!lstPeriod.It emData(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_SU BS, qryBase.GROSS_A DDS,
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.R efresh

Set qdfReportQuery = dbs.QueryDefs(" qryOldRegionRep ort")

If (3265 = Err) Then

Set qdfReportQuery = dbs.CreateQuery Def("qryOldRegi onReport")

dbs.QueryDefs.R efresh

Err = 0

End If

On Error GoTo ProcErr

'--Load the Query with the SQL string

qdfReportQuery. SQL = strSQL

dbs.QueryDefs.R efresh

qdfReportQuery. Close

'--Run the query

DoCmd.OpenQuery "qryOldRegionRe port"

'--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















Nov 13 '05 #3
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" <aw*****@yahoo. com>
wrote:
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_bytMarketRegi onOldFlag Then
'--Load selections from Market/Region listbox into variable
For Each varItem In Me!lstMarketReg ionOld.ItemsSel ected
strRegion = strRegion & "'" & Me!lstMarketReg ionOld.Column(2 ,
varItem) & "'"
strRegion = strRegion & strCriteria
strMarket = strMarket & "'" &
Me!lstMarketRe gionOld.ItemDat a(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.It emsSelected
strPeriod = strPeriod & "'" & Me!lstPeriod.It emData(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_SU BS, qryBase.GROSS_A DDS,
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.R efresh
Set qdfReportQuery = dbs.QueryDefs(" qryOldRegionRep ort")
If (3265 = Err) Then
Set qdfReportQuery = dbs.CreateQuery Def("qryOldRegi onReport")
dbs.QueryDefs.R efresh
Err = 0
End If
On Error GoTo ProcErr
'--Load the Query with the SQL string
qdfReportQuery. SQL = strSQL
dbs.QueryDefs.R efresh
qdfReportQuery. Close
'--Run the query
DoCmd.OpenQuery "qryOldRegionRe port"
'--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" <aw*****@yahoo. com> wrote in message
news:6D******* ***********@twi ster.socal.rr.c om...
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_bytMarketRegi onOldFlag Then

'--Load selections from Market/Region listbox into variable

For Each varItem In Me!lstMarketReg ionOld.ItemsSel ected

strRegion = strRegion & "'" & Me!lstMarketReg ionOld.Column(2 ,
varItem) & "'"

strRegion = strRegion & strCriteria

strMarket = strMarket & "'" &
Me!lstMarketReg ionOld.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.It emsSelected

strPeriod = strPeriod & "'" & Me!lstPeriod.It emData(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_SU BS, qryBase.GROSS_A DDS,
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.R efresh

Set qdfReportQuery = dbs.QueryDefs(" qryOldRegionRep ort")

If (3265 = Err) Then

Set qdfReportQuery = dbs.CreateQuery Def("qryOldRegi onReport")

dbs.QueryDefs.R efresh

Err = 0

End If

On Error GoTo ProcErr

'--Load the Query with the SQL string

qdfReportQuery. SQL = strSQL

dbs.QueryDefs.R efresh

qdfReportQuery. Close

'--Run the query

DoCmd.OpenQuery "qryOldRegionRe port"

'--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

















Nov 13 '05 #4
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.************ ***@blueyonder. co.uk> wrote in message
news:41c75497.4 01976952@localh ost...
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" <aw*****@yahoo. com>
wrote:
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_bytMarketRegi onOldFlag Then
'--Load selections from Market/Region listbox into variable
For Each varItem In Me!lstMarketReg ionOld.ItemsSel ected
strRegion = strRegion & "'" & Me!lstMarketReg ionOld.Column(2 ,
varItem) & "'"
strRegion = strRegion & strCriteria
strMarket = strMarket & "'" &
Me!lstMarketR egionOld.ItemDa ta(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.It emsSelected
strPeriod = strPeriod & "'" & Me!lstPeriod.It emData(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.MARKE T_NAME, "
strSQL = strSQL & "qryBase.BOP_SU BS, qryBase.GROSS_A DDS,
qryBase.DEACT S, 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.R efresh
Set qdfReportQuery = dbs.QueryDefs(" qryOldRegionRep ort")
If (3265 = Err) Then
Set qdfReportQuery = dbs.CreateQuery Def("qryOldRegi onReport")
dbs.QueryDefs.R efresh
Err = 0
End If
On Error GoTo ProcErr
'--Load the Query with the SQL string
qdfReportQuery. SQL = strSQL
dbs.QueryDefs.R efresh
qdfReportQuery. Close
'--Run the query
DoCmd.OpenQuery "qryOldRegionRe port"
'--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" <aw*****@yahoo. com> wrote in message
news:6D****** ************@tw ister.socal.rr. com...
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_bytMarketRegi onOldFlag Then

'--Load selections from Market/Region listbox into variable

For Each varItem In Me!lstMarketReg ionOld.ItemsSel ected

strRegion = strRegion & "'" & Me!lstMarketReg ionOld.Column(2 ,
varItem) & "'"

strRegion = strRegion & strCriteria

strMarket = strMarket & "'" &
Me!lstMarketReg ionOld.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.It emsSelected

strPeriod = strPeriod & "'" & Me!lstPeriod.It emData(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_SU BS, qryBase.GROSS_A DDS,
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.R efresh

Set qdfReportQuery = dbs.QueryDefs(" qryOldRegionRep ort")

If (3265 = Err) Then

Set qdfReportQuery = dbs.CreateQuery Def("qryOldRegi onReport")

dbs.QueryDefs.R efresh

Err = 0

End If

On Error GoTo ProcErr

'--Load the Query with the SQL string

qdfReportQuery. SQL = strSQL

dbs.QueryDefs.R efresh

qdfReportQuery. Close

'--Run the query

DoCmd.OpenQuery "qryOldRegionRe port"

'--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
















Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
5394
by: Sean C. | last post by:
Helpful folks, Most of my previous experience with DB2 was on s390 mainframe systems and the optimizer on this platform always seemed very predictable and consistent. Since moving to a WinNT/UDB 7.2 environment, the choices the optimizer makes often seem flaky. But this last example really floored me. I was hoping someone could explain why...
4
3865
by: Brian Andrus | last post by:
Ok. I upgraded to MS Access 2003 recently and now I am having great heartache. In Access 2002, when I opened a table to view the data, there were wonderful little "plus" signs that I could click on for each record that would open up the corresponding record in any linked table. I could drill down and edit/add records in a most efficient and...
4
2886
by: Michael John | last post by:
Dear Oracle Developers, my task is to make up a Oracle View from a Pivot table in MS Access. Given are two tables to join: T_FIRM: FIRM_ABBR VARCHAR2(3 BYTE), FIRM_LONG VARCHAR2(70 BYTE), CONSTRAINT PK_FIRM PRIMARY KEY (FIRM_ABBR)
2
5639
by: eskil | last post by:
Hi, I am running a pivot table based on a query. I need to be able to display decimal precision for on a calculated field (in the query). The query returns the values correctly (i.e. with the number of decimals specified), the pivot table - when viewed as a regular form - shows decimals, but the pivot itself displays only the suffix ,00. ...
1
3868
by: Jerome Ranch | last post by:
I consider myself an Excel PT wizard of sorts, but now I have a situation with so much infromationthat I need to categorize and summarize, that I will use access to manage it. Interestingly, though, the pivot tables in excel 2003 do not resemble the pivot tables in access 2003 as closely as they did previous versions of the products. One of...
8
4841
by: Jerome Ranch | last post by:
Okay So I've got pivot tables setup in Access 2003. Only about 30K records in the current 2005 databases...the pivots summarize the info in a number of nice ways. I need to get the pivot tables into a document so I can distribute the tables, as is, as an electronic report (without the detailed data) So I export to rtf and xls, and I get an...
0
1318
by: ghazbanpoker | last post by:
I have two computers both running XP and Office 2003. Whenever I try to open a query in Pivot Table view on my laptop, the computer pauses briefly, then changes to datasheet view. If I move the database to my desktop, I can view queries in Pivot Table view with no problems. I tried creating a dummy database from scratch to see if the problem...
0
2218
by: cbj0517 | last post by:
Hello: I am trying to create a Pivot table in an Access 2003 report. I have SQL that retunrs 6 columns of data, (month_year, units, products_per_unit, gross_sales, price_pe_product, and avg_days_to_fulfill). I would like the columns of the pivot to be the months of the year, and a row for each of the other columns, with a total column at the...
2
4029
by: Marisol2 | last post by:
I have some queries in Access 2003 db that I have setup to display as pivots. I can go into design pivot table view and click on and then click on Export to Office MicroSoft Excel. The problem is I have about 6 queries that produce 48 reports (based on parameters). I don't want to manually save each pivot query results. The first macro I...
0
7784
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8204
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7795
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8073
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6424
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5600
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3735
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2223
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1309
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.