Transform/PIVOT Query

[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.

 

[John Spencer]

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

 

[John W. Vinson]

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.

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

 

[carl]

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.- Hide quoted text -

- Show quoted text -

Thank you . Worked great.

 

[Bob Barrows]

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.

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?