Transform/PIVOT Query

  • Thread starter Thread starter carl
  • Start date Start date
C

carl

My data records look like this...

SubscriberId MessageType
UBSOSC1 ABC
UBSOSC1 ABC
UBSOXT4 DEF
UBSOXT4 GHI
UBSOSC1 ABC
UBSOSC1 DEF


I am trying to create a Pivot Table (but do not want to use the Pivot
table Wizard) via a query.

Would like the results to look like this (displaying Count of Message
Types)...


SubscriberId ABC DEF GHI Grand Total
UBSOSC1 3 1 4
UBSOXT4 1 1 2
Grand Total 3 2 1 6

Is ther a way to use a query to replicate a Pivot Table ?

Thank you in advance.
 
A crosstab query should work

TRANSFORM Count(MessageType)
SELECT SubscriberID, Count(MessageType) as GrandTotal
FROM [SomeTable]
GROUP BY SubscriberID
PIVOT MessageType


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
My data records look like this...

SubscriberId MessageType
UBSOSC1 ABC
UBSOSC1 ABC
UBSOXT4 DEF
UBSOXT4 GHI
UBSOSC1 ABC
UBSOSC1 DEF


I am trying to create a Pivot Table (but do not want to use the Pivot
table Wizard) via a query.

Would like the results to look like this (displaying Count of Message
Types)...


SubscriberId ABC DEF GHI Grand Total
UBSOSC1 3 1 4
UBSOXT4 1 1 2
Grand Total 3 2 1 6

Is ther a way to use a query to replicate a Pivot Table ?

Thank you in advance.
Click to expand...

This looks more like a Crosstab Query rather than a Pivot. Try using the
SubscriberID as the Row Header, the MessageType as the Column Header and
including the totals row. Something like:


TRANSFORM Count(MessageType) AS TheValue
SELECT SubscriberID, Count(*) As GrandTotal
FROM table
GROUP BY SubscriberID
PIVOT MessageType

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
A crosstab query should work

TRANSFORM Count(MessageType)
SELECT SubscriberID, Count(MessageType) as GrandTotal
FROM [SomeTable]
GROUP BY SubscriberID
PIVOT MessageType

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

My data records look like this...
Click to expand...
SubscriberId       MessageType
UBSOSC1                      ABC
UBSOSC1                      ABC
UBSOXT4                      DEF
UBSOXT4                       GHI
UBSOSC1                       ABC
UBSOSC1                       DEF
Click to expand...
I am trying to create a Pivot Table (but do not want to use the Pivot
table Wizard) via a query.
Click to expand...
Would like the results to look like this (displaying Count of Message
Types)...
Click to expand...
SubscriberId       ABC     DEF     GHI     Grand Total
UBSOSC1    3       1                4
UBSOXT4                            1       1        2
Grand Total        3       2       1        6
Click to expand...
Is ther  a way to use a query to replicate a Pivot Table ?
Click to expand...
Thank you in advance.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thank you . Worked great.
 
carl said:
My data records look like this...

SubscriberId MessageType
UBSOSC1 ABC
UBSOSC1 ABC
UBSOXT4 DEF
UBSOXT4 GHI
UBSOSC1 ABC
UBSOSC1 DEF


I am trying to create a Pivot Table (but do not want to use the Pivot
table Wizard) via a query.

Would like the results to look like this (displaying Count of Message
Types)...


SubscriberId ABC DEF GHI Grand Total
UBSOSC1 3 1 4
UBSOXT4 1 1 2
Grand Total 3 2 1 6

Is ther a way to use a query to replicate a Pivot Table ?

Thank you in advance.
Click to expand...
Given that you know all the category names, you don't need to use Transform.
You can do this:

SELECT SubscriberId
,SUM(IIF(MessageType='ABC',1,0)) AS ABC
,SUM(IIF(MessageType='DEF',1,0)) AS DEF
,SUM(IIF(MessageType='GHI',1,0)) AS GHI
,Count(*) AS [Grand Total]
FROM tablename
GROUP BY SubscriberId
UNION ALL
SELECT 'Grand Total'
,SUM(IIF(MessageType='ABC',1,0)) AS ABC
,SUM(IIF(MessageType='DEF',1,0)) AS DEF
,SUM(IIF(MessageType='GHI',1,0)) AS GHI
,Count(*) AS [Grand Total]
FROM tablename

Of course, the flaw in this is that you'll have to modify this query if
additional MessageTypes are added, or use VBA to build it dynamically.

Why do you wish to avoid the crosstab query wizard?
 
Back
Top