Crosstab Query Multiple

Hello,

It works great for the query to do a print preview and print it out. But I
was wondering if there is a way to change the column heading to not show
typeOfContact and actually format it like Type of Contact as well as
sumofcontact.

Also, the print preview comes up with the data with a listing of the types
of contact. But when I try to export it to excel to do some additional
formatting, the dates and totals are there but the actual types of contacts
listed disappear. Do you know what may have caused this?

Thanks

"Ben Pelech" wrote:

Got it thank you so much for your help!!!

"Michel Walsh" wrote:

If I use

TRANSFORM COUNT(*) As TheCell
SELECT Orders.EmployeeID, SUM(theCell)
FROM Orders
GROUP BY Orders.EmployeeID
PIVOT CustomerID


in Northwind, I do get the total by row, and if I use


TRANSFORM COUNT(*) As TheCell
SELECT NULL, SUM(theCell)
FROM Orders
GROUP BY NULL
PIVOT CustomerID


I do get a total by column.


Note that the total column is, in query data view, the second one, NOT the
last one.



Vanderghast, Access MVP



"Ben Pelech" wrote in message
...
Well I didnt get an error message, but it still isnt behaving correctly.
I
am looking for a total of type of contacts by row and then a total of the
date by column

Type of Contact 7-2-08 7-3-08 7-4-08 7-5-08 Total
Voicemail 2 3 5 8
18
Outbound Call 6 10 1 4
21
Email 8 2 3 4
17
Totals 16 15 8 16
56

The code in my current two crosstabs are

CROSSTAB1

PARAMETERS Starting DateTime, Ending DateTime;
TRANSFORM COUNT( [CountOfType Of Contact] ) AS theCell
SELECT [Type Of Contact] AS typeOfContact, SUM(theCell) AS sumOfCount
FROM quryAllContactsTest
WHERE [Date] BETWEEN starting AND ending
GROUP BY [Type Of Contact]
PIVOT FORMAT( [Date], "Short Date");


CROSSTAB 2
PARAMETERS Starting DateTime, Ending DateTime;
TRANSFORM COUNT( [CountOfDate] ) AS theCell
SELECT Null AS typeOfContact, SUM(theCell) AS sumOfCount
FROM quryAllContactsTest
WHERE [Date] BETWEEN starting AND ending
GROUP BY null
PIVOT FORMAT( [Date], "Short Date");

and Union Qury

SELECT * FROM quryallcontactstest_crosstab
UNION SELECT * FROM quryallcontactstest_crosstab1;

I appreciate your help!!

Thanks
Ben

"Michel Walsh" wrote:

in SQL view:


SELECT * FROM crosstab1
UNION ALL
SELECT * FROM crosstab2



Vanderghast, Access MVP

column heading to not show
typeOfContact and actually format it like Type of Contact



remove the alias, ie, instead of

SELECT [Type Of Contact] AS typeOfContact,

have

SELECT [Type Of Contact] ,

or

SELECT [Type Of Contact] AS [Nice new name I want] ,




as well as
sumofcontact.



Same. Change

SELECT [Type Of Contact] AS typeOfContact, SUM(theCell) AS sumOfCount

to

SELECT [Type Of Contact] AS [Nice new name I want] , SUM(theCell) AS [Total]



You should only have to do it in the first crosstab you use in the UNION
ALL: the UNION ALL picks the name of the result from the first query it
sees.



But when I try to export it to excel to do some additional
formatting, the dates and totals are there but the actual types of
contacts
listed disappear. Do you know what may have caused this?




Nothing not obvious come to mind. Maybe re-running the export ? Or try
using a TransferSpreadsheet (easier from a Macro, maybe, than through VBA
code), since then you see the possible values for the options in combo box,
rather than through intellisense.


Vanderghast, Access MVP


"Ben Pelech" wrote in message
...
Hello,

It works great for the query to do a print preview and print it out. But
I
was wondering if there is a way to change the column heading to not show
typeOfContact and actually format it like Type of Contact as well as
sumofcontact.

Also, the print preview comes up with the data with a listing of the types
of contact. But when I try to export it to excel to do some additional
formatting, the dates and totals are there but the actual types of
contacts
listed disappear. Do you know what may have caused this?

Thanks

"Ben Pelech" wrote:

Got it thank you so much for your help!!!

"Michel Walsh" wrote:

If I use

TRANSFORM COUNT(*) As TheCell
SELECT Orders.EmployeeID, SUM(theCell)
FROM Orders
GROUP BY Orders.EmployeeID
PIVOT CustomerID


in Northwind, I do get the total by row, and if I use


TRANSFORM COUNT(*) As TheCell
SELECT NULL, SUM(theCell)
FROM Orders
GROUP BY NULL
PIVOT CustomerID


I do get a total by column.


Note that the total column is, in query data view, the second one, NOT
the
last one.



Vanderghast, Access MVP



"Ben Pelech" wrote in message
...
Well I didnt get an error message, but it still isnt behaving
correctly.
I
am looking for a total of type of contacts by row and then a total of
the
date by column

Type of Contact 7-2-08 7-3-08 7-4-08 7-5-08
Total
Voicemail 2 3 5
8
18
Outbound Call 6 10 1 4
21
Email 8 2 3
4
17
Totals 16 15 8
16
56

The code in my current two crosstabs are

CROSSTAB1

PARAMETERS Starting DateTime, Ending DateTime;
TRANSFORM COUNT( [CountOfType Of Contact] ) AS theCell
SELECT [Type Of Contact] AS typeOfContact, SUM(theCell) AS sumOfCount
FROM quryAllContactsTest
WHERE [Date] BETWEEN starting AND ending
GROUP BY [Type Of Contact]
PIVOT FORMAT( [Date], "Short Date");


CROSSTAB 2
PARAMETERS Starting DateTime, Ending DateTime;
TRANSFORM COUNT( [CountOfDate] ) AS theCell
SELECT Null AS typeOfContact, SUM(theCell) AS sumOfCount
FROM quryAllContactsTest
WHERE [Date] BETWEEN starting AND ending
GROUP BY null
PIVOT FORMAT( [Date], "Short Date");

and Union Qury

SELECT * FROM quryallcontactstest_crosstab
UNION SELECT * FROM quryallcontactstest_crosstab1;

I appreciate your help!!

Thanks
Ben

"Michel Walsh" wrote:

in SQL view:


SELECT * FROM crosstab1
UNION ALL
SELECT * FROM crosstab2



Vanderghast, Access MVP

You're awesome.. Thanks so much

"Michel Walsh" wrote:



column heading to not show
typeOfContact and actually format it like Type of Contact



remove the alias, ie, instead of

SELECT [Type Of Contact] AS typeOfContact,

have

SELECT [Type Of Contact] ,

or

SELECT [Type Of Contact] AS [Nice new name I want] ,




as well as
sumofcontact.



Same. Change

SELECT [Type Of Contact] AS typeOfContact, SUM(theCell) AS sumOfCount

to

SELECT [Type Of Contact] AS [Nice new name I want] , SUM(theCell) AS [Total]



You should only have to do it in the first crosstab you use in the UNION
ALL: the UNION ALL picks the name of the result from the first query it
sees.



But when I try to export it to excel to do some additional
formatting, the dates and totals are there but the actual types of
contacts
listed disappear. Do you know what may have caused this?




Nothing not obvious come to mind. Maybe re-running the export ? Or try
using a TransferSpreadsheet (easier from a Macro, maybe, than through VBA
code), since then you see the possible values for the options in combo box,
rather than through intellisense.


Vanderghast, Access MVP


"Ben Pelech" wrote in message
...
Hello,

It works great for the query to do a print preview and print it out. But
I
was wondering if there is a way to change the column heading to not show
typeOfContact and actually format it like Type of Contact as well as
sumofcontact.

Also, the print preview comes up with the data with a listing of the types
of contact. But when I try to export it to excel to do some additional
formatting, the dates and totals are there but the actual types of
contacts
listed disappear. Do you know what may have caused this?

Thanks

"Ben Pelech" wrote:

Got it thank you so much for your help!!!

"Michel Walsh" wrote:

If I use

TRANSFORM COUNT(*) As TheCell
SELECT Orders.EmployeeID, SUM(theCell)
FROM Orders
GROUP BY Orders.EmployeeID
PIVOT CustomerID


in Northwind, I do get the total by row, and if I use


TRANSFORM COUNT(*) As TheCell
SELECT NULL, SUM(theCell)
FROM Orders
GROUP BY NULL
PIVOT CustomerID


I do get a total by column.


Note that the total column is, in query data view, the second one, NOT
the
last one.



Vanderghast, Access MVP



"Ben Pelech" wrote in message
...
Well I didnt get an error message, but it still isnt behaving
correctly.
I
am looking for a total of type of contacts by row and then a total of
the
date by column

Type of Contact 7-2-08 7-3-08 7-4-08 7-5-08
Total
Voicemail 2 3 5
8
18
Outbound Call 6 10 1 4
21
Email 8 2 3
4
17
Totals 16 15 8
16
56

The code in my current two crosstabs are

CROSSTAB1

PARAMETERS Starting DateTime, Ending DateTime;
TRANSFORM COUNT( [CountOfType Of Contact] ) AS theCell
SELECT [Type Of Contact] AS typeOfContact, SUM(theCell) AS sumOfCount
FROM quryAllContactsTest
WHERE [Date] BETWEEN starting AND ending
GROUP BY [Type Of Contact]
PIVOT FORMAT( [Date], "Short Date");


CROSSTAB 2
PARAMETERS Starting DateTime, Ending DateTime;
TRANSFORM COUNT( [CountOfDate] ) AS theCell
SELECT Null AS typeOfContact, SUM(theCell) AS sumOfCount
FROM quryAllContactsTest
WHERE [Date] BETWEEN starting AND ending
GROUP BY null
PIVOT FORMAT( [Date], "Short Date");

and Union Qury

SELECT * FROM quryallcontactstest_crosstab
UNION SELECT * FROM quryallcontactstest_crosstab1;

I appreciate your help!!

Thanks
Ben

"Michel Walsh" wrote:

in SQL view:


SELECT * FROM crosstab1
UNION ALL
SELECT * FROM crosstab2



Vanderghast, Access MVP