Reverse Pivot Table in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a Access table that I need to transpose the layout for reporting
purposes. I currently export the file to Excel, run a reverse pivot table
macro (Reorganize_Comments) which produces the result I want. Is it possible
to do all this in Access without going to Excel.


Access Table exported to Excel:

AutoKey Q1C Q2C Q3C Q4C Q5C Q6C
67
69 Wrong date of loss input for suffix; s/b 5/24/05.
70
71
72
73
74
75
76
144 Reported as OR. Correct state is IL.
145 Reported as OH. Correct state is VA.
152 Shown on Suffix Report as AL. FNOL shows TX
153 Suffix Report shows PA. Loss occurred in FL.
155 Suffix Report shows PA. Loss occurred in FL.
156 Suffix Report shows PA. Loss occurred in FL.
177
260 Date of loss in COVERS 6-16-05 should be 8-16-05


Result after running Macro in Excel:

Row Column Value
67 Q1C
67 Q2C
67 Q3C
67 Q4C
67 Q5C
67 Q6C
69 Q1C
69 Q2C
69 Q3C Wrong date of loss input for suffix; s/b 5/24/05.
69 Q4C
69 Q5C
69 Q6C
70 Q1C
70 Q2C
70 Q3C
70 Q4C
70 Q5C
70 Q6C
71 Q1C
71 Q2C
71 Q3C
71 Q4C
71 Q5C
71 Q6C
72 Q1C
72 Q2C
72 Q3C
72 Q4C
72 Q5C
72 Q6C
73 Q1C
73 Q2C
73 Q3C
73 Q4C
73 Q5C
73 Q6C
74 Q1C
74 Q2C
74 Q3C
74 Q4C
74 Q5C
74 Q6C
75 Q1C
75 Q2C
75 Q3C
75 Q4C
75 Q5C
75 Q6C
76 Q1C
76 Q2C
76 Q3C
76 Q4C
76 Q5C
76 Q6C
144 Q1C
144 Q2C
144 Q3C
144 Q4C
144 Q5C
144 Q6C Reported as OR. Correct state is IL.
145 Q1C
145 Q2C
145 Q3C
145 Q4C
145 Q5C
145 Q6C Reported as OH. Correct state is VA.
152 Q1C
152 Q2C
152 Q3C
152 Q4C
152 Q5C
152 Q6C Shown on Suffix Report as AL. FNOL shows TX
153 Q1C
153 Q2C
153 Q3C
153 Q4C
153 Q5C
153 Q6C Suffix Report shows PA. Loss occurred in FL.
155 Q1C
155 Q2C
155 Q3C
155 Q4C
155 Q5C
155 Q6C Suffix Report shows PA. Loss occurred in FL.
156 Q1C
156 Q2C
156 Q3C
156 Q4C
156 Q5C
156 Q6C Suffix Report shows PA. Loss occurred in FL.
177 Q1C
177 Q2C
177 Q3C
177 Q4C
177 Q5C
177 Q6C
260 Q1C
260 Q2C
260 Q3C Date of loss in COVERS 6-16-05 should be 8-16-05
260 Q4C
260 Q5C
260 Q6C
327 Q1C
327 Q2C
327 Q3C
327 Q4C
327 Q5C
327 Q6C

I would like to do this all in Access. Is that possible?

Sorry for such a long memo, it was the only way I could explain.
Thanks for any help.

Dean
 
It's a bit difficult to understand your table structure other than is seems
a victim of being un-normalized. I believe you can fix this with a union
query. This must be created in SQL view.

SELECT AutoKey as Row, "Q1C" as Col, Q1C as Value
FROM tblNoNameGiven
UNION ALL
SELECT AutoKey, "Q2C", Q2C
FROM tblNoNameGiven
UNION ALL
SELECT AutoKey, "Q3C", Q3C
FROM tblNoNameGiven
UNION ALL
--- etc ---
SELECT AutoKey, "Q6C", Q6C
FROM tblNoNameGiven;
 
My table structure is:

AutoKey
Q1C
Q2C
Q3c
etc.

The result I want is to create as follows.

Col1 - auto key value
Col2 - "Q1C" (label from above)
col3 - value of Q1C

The following is the macro I used in Excel:

Sub ReorganizeComments()
'
' ReorganizeComments Macro
' Macro recorded 7/2/2004 by C191271
Dim SummaryTableRange As Range
Set SummaryTableRange = ActiveCell.CurrentRegion

Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlConsolidation, SourceData:= _
Array(SummaryTableRange.Address(True, True, xlR1C1,
True))).CreatePivotTable TableDestination:="", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Row").Orientation = _
xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Column").Orientation
= _
xlHidden
ActiveSheet.PivotTables("PivotTable1").AddFields
ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Value"). _
Orientation = xlHidden
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Value")
.Orientation = xlDataField
.Caption = "Sum of Value"
.Function = xlSum
End With
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems("Sum of
Value" _
).Position = 1
Range("B4").Select
Selection.ShowDetail = True
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="="
End Sub

Would your solution accomplish this??

Where would I put the SQL statements?

Thanks for replying.
 
Here is a simpler example of what I am trying to do within Access.


Current table: Auto_Data

AutoKey Q1C Q2C Q3C

1 Bill Jim Ken
2 Bob Jim
3 John Dean

Result Table: ReorganizedData

Key Field1 Field2

1 Q1C Bill
1 Q2C Jim
1 Q3C Ken
2 Q1C Bob
2 Q2C
2 Q3C Jim
3 Q1C
3 Q2C John
3 Q3C Dean


Thanks for any help.
 
That's exactly what Duane's suggestion will do.

Do you really want the two rows:

2 Q2C
3 Q1C

What do you want for Field2: a Null, or a blank?
 
I don't need the rows if the value of field 2 is blank or null. I was going
to filter out in a subsequent query.

Do I just put Duane's SQL in a new blank query?

Thanks for the help.
 
Yup. When you create the new query, it's going to ask you to select a table.
Don't bother. Go to the View menu, and select SQL View. Type the SQL there.

If you want to eliminate the blanks or nulls, use:

SELECT AutoKey as Row, "Q1C" as Col, Q1C as Value
FROM tblNoNameGiven
WHERE Len(Q1C & "") > 0
UNION ALL
SELECT AutoKey, "Q2C", Q2C
FROM tblNoNameGiven
WHERE Len(Q2C & "") > 0
UNION ALL
SELECT AutoKey, "Q3C", Q3C
FROM tblNoNameGiven
WHERE Len(Q3C & "") > 0
UNION ALL
--- etc ---
SELECT AutoKey, "Q6C", Q6C
FROM tblNoNameGiven
WHERE Len(Q6C & "") > 0
 
I created the query but I keep getting an error message:
"The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect"

Here is my query:

SELECT ClaimNumber as Row, "Q1C" as Col, Q1C as Value
FROM tblErrors
WHERE Len(Q1C & "") > 0
UNION ALL
SELECT ClaimNumber, "Q2C", Q2C
FROM tblErrors
WHERE Len(Q2C & "") > 0
UNION ALL
SELECT ClaimNumber, "Q3C", Q3C
FROM tblErrors
WHERE Len(Q3C & "") > 0
UNION ALL ;

Here is my Table:

Claim Number Q1C Q2C Q3C
E216767421
E216767402 Wrong date
E216767431
E216767441
E219852321 Invalid Tax
E219852322
E219852323 Bad code Invalid date Wrong policy
E219852331
E219856321
E219761121
E219768721
E219768921
E218742921 Wrong Claim
E218742922
E218742931
E219762721
E219743821 Date of loss
18C17133

What have I done wrong?

I appreciate your help on this.
 
Back
Top