By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,148 Members | 739 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,148 IT Pros & Developers. It's quick & easy.

Access 2003: Request help on pivot table query

P: n/a
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














Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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******************@twister.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_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















Nov 13 '05 #2

P: n/a
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" <aw*****@yahoo.com> wrote in message
news:6D******************@twister.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_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















Nov 13 '05 #3

P: n/a
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_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" <aw*****@yahoo.com> wrote in message
news:6D******************@twister.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_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

















Nov 13 '05 #4

P: n/a
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.401976952@localhost...
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_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" <aw*****@yahoo.com> wrote in message
news:6D******************@twister.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_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
















Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.