473,386 Members | 1,795 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

home > topics > microsoft access / vba > questions > access 2003: request help on pivot table query

Join Bytes to post your question to a community of 473,386 software developers and data experts.

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_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
4 2833
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
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
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
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
New Post

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

Similar topics

14
Better index access = worse performance??
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...
DB2 Database
4
Access 2003 pivot-table view gone?!?
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...
Microsoft Access / VBA
4
Pivot Table from Access to Oracle
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 ...
Microsoft Access / VBA
2
Showing decimals in Access pivot table
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...
Microsoft Access / VBA
1
Pivot Tables Access vs Excel
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,...
Microsoft Access / VBA
8
trying to copy an Access 2003 pivot table to excel or word .... or something
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...
Microsoft Access / VBA
0
Problem with Pivot Tables in MS Access
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...
Microsoft Access / VBA
0
Creating a Pivot table in an Access 2003 report
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...
Microsoft Access / VBA
2
Specifying Directory and File Name on Pivot Table Export from Access query to Excel
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...
Microsoft Access / VBA
0
Basic Javascript concepts
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
Javascript
0
Merging data from multiple Excel files
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
Data Management
0
Migrating Website to Cloud - Emmanuel Katto
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
General
0
BarryA
Navigating the Data Structures and Algorithms (DSA)
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
Algorithms / Advanced Math
1
Looking to do Android software development, any suggestions? Is flutter better?
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
General
1
Is that possible of reading the .csv file in column wise and the column have different lengths ?
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
C / C++
0
marktang
What is ONU?
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,...
General
0
Changing the language in Windows 10
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
Windows Server
0
Oralloy
Problem With Comparison Operator <=> in G++
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
C / C++

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.

BYTES.COM © 2024
About Bytes
Terms Of Use
Privacy Policy
Sitemap
Advertise on Bytes:
Post a Job
Sponsored Posts
Platinum & Gold Sponsors
Hire Now!