Crosstab Query Multiple

B

Ben Pelech

I am trying to find the easiest way to make a report or something that
resembles an excel spreadsheet. The closest thing I have found is a crosstab
query. There are 2 things that I cannot get it to do. #1 I cannot format it
to use a date range parameter and # 2 I need to count the total contact type
in the rows and the total count of dates in the columns at the bottom. Can
this be done? Any help would be greatly appreciated. Below is the SQL of my
crosstab

TRANSFORM Count([CountOfType of Contact])
SELECT [Type of Contact], Count([CountOfType of Contact]) AS [Total Of
CountOfType of Contact]
FROM QuryAllContactsTest
GROUP BY [Type of Contact]
PIVOT Format([Date],"Short Date");

Thank you very much

Ben
 
M

Michel Walsh

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



You can union all this query with



PARAMETERS starting DATETIME, ending DATETIME;
TRANSFORM COUNT( [CountOfType Of Contact] ) AS theCell
SELECT Null AS typeOfContact, SUM(theCell) AS sumOfCount
FROM queryAllContactsTests
WHERE [Date] BETWEEN starting AND ending
GROUP BY null
PIVOT FORMAT( [Date], "Short Date");



which summarizes the same stats, but by considering the whole table as
making just one group.


You may have to use COUNT(theCell) instead of SUM(theCell), I am not sure
which of the two expressions you really want.




Vanderghast, Access MVP
 
B

Ben Pelech

Thank you for your reply. How do I union these two?

Michel Walsh said:
PARAMETERS starting DATETIME, ending DATETIME;
TRANSFORM COUNT( [CountOfType Of Contact] ) AS theCell
SELECT [Type Of Contact] AS typeOfContact, SUM(theCell) AS sumOfCount
FROM queryAllContactsTests
WHERE [Date] BETWEEN starting AND ending
GROUP BY [Type Of Contact]
PIVOT FORMAT( [Date], "Short Date");



You can union all this query with



PARAMETERS starting DATETIME, ending DATETIME;
TRANSFORM COUNT( [CountOfType Of Contact] ) AS theCell
SELECT Null AS typeOfContact, SUM(theCell) AS sumOfCount
FROM queryAllContactsTests
WHERE [Date] BETWEEN starting AND ending
GROUP BY null
PIVOT FORMAT( [Date], "Short Date");



which summarizes the same stats, but by considering the whole table as
making just one group.


You may have to use COUNT(theCell) instead of SUM(theCell), I am not sure
which of the two expressions you really want.




Vanderghast, Access MVP



Ben Pelech said:
I am trying to find the easiest way to make a report or something that
resembles an excel spreadsheet. The closest thing I have found is a
crosstab
query. There are 2 things that I cannot get it to do. #1 I cannot format
it
to use a date range parameter and # 2 I need to count the total contact
type
in the rows and the total count of dates in the columns at the bottom.
Can
this be done? Any help would be greatly appreciated. Below is the SQL of
my
crosstab

TRANSFORM Count([CountOfType of Contact])
SELECT [Type of Contact], Count([CountOfType of Contact]) AS [Total Of
CountOfType of Contact]
FROM QuryAllContactsTest
GROUP BY [Type of Contact]
PIVOT Format([Date],"Short Date");

Thank you very much

Ben
Click to expand...
Click to expand...
 
B

Ben Pelech

Hell Michael. I tried to find how to use the union command and to join the
two but I am receiving an error message

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")
UNION
PARAMETERS starting DATETIME, ending DATETIME;
TRANSFORM COUNT( [CountOfType Of Contact] ) 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");

The message i am receiving is a syntax error (missing operator) in query
expression FORMAT([Date],"short Date")
Union
Parameters starting datetime, ending datetime

Thanks again for all of your help!!!

Ben


Michel Walsh said:
PARAMETERS starting DATETIME, ending DATETIME;
TRANSFORM COUNT( [CountOfType Of Contact] ) AS theCell
SELECT [Type Of Contact] AS typeOfContact, SUM(theCell) AS sumOfCount
FROM queryAllContactsTests
WHERE [Date] BETWEEN starting AND ending
GROUP BY [Type Of Contact]
PIVOT FORMAT( [Date], "Short Date");



You can union all this query with



PARAMETERS starting DATETIME, ending DATETIME;
TRANSFORM COUNT( [CountOfType Of Contact] ) AS theCell
SELECT Null AS typeOfContact, SUM(theCell) AS sumOfCount
FROM queryAllContactsTests
WHERE [Date] BETWEEN starting AND ending
GROUP BY null
PIVOT FORMAT( [Date], "Short Date");



which summarizes the same stats, but by considering the whole table as
making just one group.


You may have to use COUNT(theCell) instead of SUM(theCell), I am not sure
which of the two expressions you really want.




Vanderghast, Access MVP



Ben Pelech said:
I am trying to find the easiest way to make a report or something that
resembles an excel spreadsheet. The closest thing I have found is a
crosstab
query. There are 2 things that I cannot get it to do. #1 I cannot format
it
to use a date range parameter and # 2 I need to count the total contact
type
in the rows and the total count of dates in the columns at the bottom.
Can
this be done? Any help would be greatly appreciated. Below is the SQL of
my
crosstab

TRANSFORM Count([CountOfType of Contact])
SELECT [Type of Contact], Count([CountOfType of Contact]) AS [Total Of
CountOfType of Contact]
FROM QuryAllContactsTest
GROUP BY [Type of Contact]
PIVOT Format([Date],"Short Date");

Thank you very much

Ben
Click to expand...
Click to expand...
 
B

Ben Pelech

Ok I saved both Crosstabs but cannot figure the code for the union sql. Sorry
I have never done this before. Any help would be greatly appreciated.

Thanks

Michel Walsh said:
You union the two SAVED crosstabs.


Vanderghast, Access MVP


Ben Pelech said:
Hell Michael. I tried to find how to use the union command and to join the
two but I am receiving an error message

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")
UNION
PARAMETERS starting DATETIME, ending DATETIME;
TRANSFORM COUNT( [CountOfType Of Contact] ) 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");

The message i am receiving is a syntax error (missing operator) in query
expression FORMAT([Date],"short Date")
Union
Parameters starting datetime, ending datetime

Thanks again for all of your help!!!

Ben
Click to expand...
Click to expand...
 
M

Michel Walsh

in SQL view:


SELECT * FROM crosstab1
UNION ALL
SELECT * FROM crosstab2



Vanderghast, Access MVP
 
B

Ben Pelech

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
 
M

Michel Walsh

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 said:
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 said:
in SQL view:


SELECT * FROM crosstab1
UNION ALL
SELECT * FROM crosstab2



Vanderghast, Access MVP
Click to expand...
Click to expand...
 
B

Ben Pelech

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

Michel Walsh said:
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 said:
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 said:
in SQL view:


SELECT * FROM crosstab1
UNION ALL
SELECT * FROM crosstab2



Vanderghast, Access MVP
Click to expand...
Click to expand...
Click to expand...
 
B

Ben Pelech

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 said:
Got it thank you so much for your help!!!

Michel Walsh said:
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 said:
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

:

in SQL view:


SELECT * FROM crosstab1
UNION ALL
SELECT * FROM crosstab2



Vanderghast, Access MVP
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

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



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.
Click to expand...



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?
Click to expand...




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 said:
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 said:
Got it thank you so much for your help!!!

Michel Walsh said:
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



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

:

in SQL view:


SELECT * FROM crosstab1
UNION ALL
SELECT * FROM crosstab2



Vanderghast, Access MVP
Click to expand...
Click to expand...
Click to expand...
 
B

Ben Pelech

You're awesome.. Thanks so much

Michel Walsh said:
column heading to not show
typeOfContact and actually format it like Type of Contact
Click to expand...



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.
Click to expand...



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?
Click to expand...




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 said:
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 said:
Got it thank you so much for your help!!!

:

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



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

:

in SQL view:


SELECT * FROM crosstab1
UNION ALL
SELECT * FROM crosstab2



Vanderghast, Access MVP
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Append Crosstab Query to Existing Table 0
How to display zero value in blank fields of Cross Tab query 0
dcount in a crosstab query 3
Error 3205 - Too Many Crosstab Column Headers (300) 2
Pulling counts out of query results 1
Group by Range in Crosstab Query 5
In crosstab query, how do I count not including duplicates? 2
Crosstab query with unique count 6

Top