If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
Crosstab Query Multiple
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 |
#13
|
|||
|
|||
Crosstab Query Multiple
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 |
|
Thread Tools | |
Display Modes | |
|
|