Can you merge a "Count" Crosstab with a "Money Sum" Crosstab?

  • Thread starter Thread starter Iram
  • Start date Start date
I

Iram

Hello,
I have created two critical queries. One counts how many times we did
something and the other sums the money amounts for the same "counted"
records. The common field between both crosstab queries is TeamName.
I need a single report that shows Counts and Amounts. How can you merge both
of these or is there a different way around this?


Thanks.
Iram
 
Post the SQL of both crosstab queries by opening in design view, click on
VIEW - SQL View, highlight all, copy, and paste in a post.
 
Karl,
Here you go per your request.... I need to merge the two below crosstab
queries. One query counts and the other sums...


Query Name: qry_CountingWhatCounts_RunCrosstabOnCategoryCounts

TRANSFORM Count(tbl_CountingWhatCountsTempTable.WorkerID) AS CountOfWorkerID
SELECT tbl_CountingWhatCountsTempTable.TeamName,
Count(tbl_CountingWhatCountsTempTable.WorkerID) AS [Total Of WorkerID]
FROM tbl_CountingWhatCountsTempTable
GROUP BY tbl_CountingWhatCountsTempTable.TeamName
PIVOT tbl_CountingWhatCountsTempTable.Category;






Query Name: qry_CountingWhatCounts_RunCrosstabOnCategoryAmounts

TRANSFORM Sum(tbl_CountingWhatCountsTempTable.Amount) AS SumOfAmount
SELECT tbl_CountingWhatCountsTempTable.TeamName,
Sum(tbl_CountingWhatCountsTempTable.Amount) AS Totals
FROM tbl_CountingWhatCountsTempTable
GROUP BY tbl_CountingWhatCountsTempTable.TeamName
PIVOT tbl_CountingWhatCountsTempTable.Category;





Iram
 
Try this --
TRANSFORM Count(tbl_CountingWhatCountsTempTable.WorkerID) & " -- " &
Sum(tbl_CountingWhatCountsTempTable.Amount) AS CountWorker_SumAmount
SELECT tbl_CountingWhatCountsTempTable.TeamName,
Count(tbl_CountingWhatCountsTempTable.WorkerID) & " -- " &
Sum(tbl_CountingWhatCountsTempTable.Amount) AS Total_CountWorker_SumAmount
FROM tbl_CountingWhatCountsTempTable
GROUP BY tbl_CountingWhatCountsTempTable.TeamName
PIVOT tbl_CountingWhatCountsTempTable.Category;

--
Build a little, test a little.


Iram said:
Karl,
Here you go per your request.... I need to merge the two below crosstab
queries. One query counts and the other sums...


Query Name: qry_CountingWhatCounts_RunCrosstabOnCategoryCounts

TRANSFORM Count(tbl_CountingWhatCountsTempTable.WorkerID) AS CountOfWorkerID
SELECT tbl_CountingWhatCountsTempTable.TeamName,
Count(tbl_CountingWhatCountsTempTable.WorkerID) AS [Total Of WorkerID]
FROM tbl_CountingWhatCountsTempTable
GROUP BY tbl_CountingWhatCountsTempTable.TeamName
PIVOT tbl_CountingWhatCountsTempTable.Category;






Query Name: qry_CountingWhatCounts_RunCrosstabOnCategoryAmounts

TRANSFORM Sum(tbl_CountingWhatCountsTempTable.Amount) AS SumOfAmount
SELECT tbl_CountingWhatCountsTempTable.TeamName,
Sum(tbl_CountingWhatCountsTempTable.Amount) AS Totals
FROM tbl_CountingWhatCountsTempTable
GROUP BY tbl_CountingWhatCountsTempTable.TeamName
PIVOT tbl_CountingWhatCountsTempTable.Category;





Iram

KARL DEWEY said:
Post the SQL of both crosstab queries by opening in design view, click on
VIEW - SQL View, highlight all, copy, and paste in a post.
Click to expand...
Click to expand...
 
KARL DEWEY, THAT WAS IMPRESSIVE!!!!

It works beautifully!
I didn't think it was possible!

YOU ARE THE MAN!


Thanks!
Iram/mcp


KARL DEWEY said:
Try this --
TRANSFORM Count(tbl_CountingWhatCountsTempTable.WorkerID) & " -- " &
Sum(tbl_CountingWhatCountsTempTable.Amount) AS CountWorker_SumAmount
SELECT tbl_CountingWhatCountsTempTable.TeamName,
Count(tbl_CountingWhatCountsTempTable.WorkerID) & " -- " &
Sum(tbl_CountingWhatCountsTempTable.Amount) AS Total_CountWorker_SumAmount
FROM tbl_CountingWhatCountsTempTable
GROUP BY tbl_CountingWhatCountsTempTable.TeamName
PIVOT tbl_CountingWhatCountsTempTable.Category;

--
Build a little, test a little.


Iram said:
Karl,
Here you go per your request.... I need to merge the two below crosstab
queries. One query counts and the other sums...


Query Name: qry_CountingWhatCounts_RunCrosstabOnCategoryCounts

TRANSFORM Count(tbl_CountingWhatCountsTempTable.WorkerID) AS CountOfWorkerID
SELECT tbl_CountingWhatCountsTempTable.TeamName,
Count(tbl_CountingWhatCountsTempTable.WorkerID) AS [Total Of WorkerID]
FROM tbl_CountingWhatCountsTempTable
GROUP BY tbl_CountingWhatCountsTempTable.TeamName
PIVOT tbl_CountingWhatCountsTempTable.Category;






Query Name: qry_CountingWhatCounts_RunCrosstabOnCategoryAmounts

TRANSFORM Sum(tbl_CountingWhatCountsTempTable.Amount) AS SumOfAmount
SELECT tbl_CountingWhatCountsTempTable.TeamName,
Sum(tbl_CountingWhatCountsTempTable.Amount) AS Totals
FROM tbl_CountingWhatCountsTempTable
GROUP BY tbl_CountingWhatCountsTempTable.TeamName
PIVOT tbl_CountingWhatCountsTempTable.Category;





Iram

KARL DEWEY said:
Post the SQL of both crosstab queries by opening in design view, click on
VIEW - SQL View, highlight all, copy, and paste in a post.

--
Build a little, test a little.


:


Hello,
I have created two critical queries. One counts how many times we did
something and the other sums the money amounts for the same "counted"
records. The common field between both crosstab queries is TeamName.
I need a single report that shows Counts and Amounts. How can you merge both
of these or is there a different way around this?


Thanks.
Iram
Click to expand...
Click to expand...
Click to expand...
 
KARL,
I created a copy of your query so that I could create a parameterized query
that would pull in a date range however the crosstab no longer groups the
"Teams". I inserted the following parameters in the Query Parameters:
[Forms]![frm_FormDailyReports]![BeginDate] Date/Time
[Forms]![frm_FormDailyReports]![EndDate] Date/Time

The query comes up with multiples of the same team. How can I fix this. Your
query works beautifully and it is essential in our operations, but now I need
this secondary query with a date range which is also essential. Could you
help me fix this?

Update query with Date Range parameter
PARAMETERS [Forms]![frm_FormDailyReports]![BeginDate] DateTime,
[Forms]![frm_FormDailyReports]![EndDate] DateTime;
TRANSFORM Count(tbl_CountingWhatCountsTempTable.WorkerID) & " " &
Sum(tbl_CountingWhatCountsTempTable.Amount) AS CountWorker_SumAmount
SELECT tbl_CountingWhatCountsTempTable.Team,
Count(tbl_CountingWhatCountsTempTable.WorkerID) & " " &
Sum(tbl_CountingWhatCountsTempTable.Amount) AS [Total Counts/$]
FROM tbl_CountingWhatCountsTempTable
WHERE (((tbl_CountingWhatCountsTempTable.StatsDate) Between
[Forms]![frm_FormDailyReports]![BeginDate] And
[Forms]![frm_FormDailyReports]![EndDate]))
GROUP BY tbl_CountingWhatCountsTempTable.Team,
tbl_CountingWhatCountsTempTable.StatsDate
PIVOT tbl_CountingWhatCountsTempTable.Category;


Thanks.
Iram/mcp




KARL DEWEY said:
Try this --
TRANSFORM Count(tbl_CountingWhatCountsTempTable.WorkerID) & " -- " &
Sum(tbl_CountingWhatCountsTempTable.Amount) AS CountWorker_SumAmount
SELECT tbl_CountingWhatCountsTempTable.TeamName,
Count(tbl_CountingWhatCountsTempTable.WorkerID) & " -- " &
Sum(tbl_CountingWhatCountsTempTable.Amount) AS Total_CountWorker_SumAmount
FROM tbl_CountingWhatCountsTempTable
GROUP BY tbl_CountingWhatCountsTempTable.TeamName
PIVOT tbl_CountingWhatCountsTempTable.Category;

--
Build a little, test a little.


Iram said:
Karl,
Here you go per your request.... I need to merge the two below crosstab
queries. One query counts and the other sums...


Query Name: qry_CountingWhatCounts_RunCrosstabOnCategoryCounts

TRANSFORM Count(tbl_CountingWhatCountsTempTable.WorkerID) AS CountOfWorkerID
SELECT tbl_CountingWhatCountsTempTable.TeamName,
Count(tbl_CountingWhatCountsTempTable.WorkerID) AS [Total Of WorkerID]
FROM tbl_CountingWhatCountsTempTable
GROUP BY tbl_CountingWhatCountsTempTable.TeamName
PIVOT tbl_CountingWhatCountsTempTable.Category;






Query Name: qry_CountingWhatCounts_RunCrosstabOnCategoryAmounts

TRANSFORM Sum(tbl_CountingWhatCountsTempTable.Amount) AS SumOfAmount
SELECT tbl_CountingWhatCountsTempTable.TeamName,
Sum(tbl_CountingWhatCountsTempTable.Amount) AS Totals
FROM tbl_CountingWhatCountsTempTable
GROUP BY tbl_CountingWhatCountsTempTable.TeamName
PIVOT tbl_CountingWhatCountsTempTable.Category;





Iram

KARL DEWEY said:
Post the SQL of both crosstab queries by opening in design view, click on
VIEW - SQL View, highlight all, copy, and paste in a post.

--
Build a little, test a little.


:


Hello,
I have created two critical queries. One counts how many times we did
something and the other sums the money amounts for the same "counted"
records. The common field between both crosstab queries is TeamName.
I need a single report that shows Counts and Amounts. How can you merge both
of these or is there a different way around this?


Thanks.
Iram
Click to expand...
Click to expand...
Click to expand...
 
The query comes up with multiples of the same team.
Your problem is that you included tbl_CountingWhatCountsTempTable.StatsDate
in the GROUP BY.

Use just the team.
.....
GROUP BY tbl_CountingWhatCountsTempTable.Team
PIVOT tbl_CountingWhatCountsTempTable.Category;

--
Build a little, test a little.


Iram said:
KARL,
I created a copy of your query so that I could create a parameterized query
that would pull in a date range however the crosstab no longer groups the
"Teams". I inserted the following parameters in the Query Parameters:
[Forms]![frm_FormDailyReports]![BeginDate] Date/Time
[Forms]![frm_FormDailyReports]![EndDate] Date/Time

The query comes up with multiples of the same team. How can I fix this. Your
query works beautifully and it is essential in our operations, but now I need
this secondary query with a date range which is also essential. Could you
help me fix this?

Update query with Date Range parameter
PARAMETERS [Forms]![frm_FormDailyReports]![BeginDate] DateTime,
[Forms]![frm_FormDailyReports]![EndDate] DateTime;
TRANSFORM Count(tbl_CountingWhatCountsTempTable.WorkerID) & " " &
Sum(tbl_CountingWhatCountsTempTable.Amount) AS CountWorker_SumAmount
SELECT tbl_CountingWhatCountsTempTable.Team,
Count(tbl_CountingWhatCountsTempTable.WorkerID) & " " &
Sum(tbl_CountingWhatCountsTempTable.Amount) AS [Total Counts/$]
FROM tbl_CountingWhatCountsTempTable
WHERE (((tbl_CountingWhatCountsTempTable.StatsDate) Between
[Forms]![frm_FormDailyReports]![BeginDate] And
[Forms]![frm_FormDailyReports]![EndDate]))
GROUP BY tbl_CountingWhatCountsTempTable.Team,
tbl_CountingWhatCountsTempTable.StatsDate
PIVOT tbl_CountingWhatCountsTempTable.Category;


Thanks.
Iram/mcp




KARL DEWEY said:
Try this --
TRANSFORM Count(tbl_CountingWhatCountsTempTable.WorkerID) & " -- " &
Sum(tbl_CountingWhatCountsTempTable.Amount) AS CountWorker_SumAmount
SELECT tbl_CountingWhatCountsTempTable.TeamName,
Count(tbl_CountingWhatCountsTempTable.WorkerID) & " -- " &
Sum(tbl_CountingWhatCountsTempTable.Amount) AS Total_CountWorker_SumAmount
FROM tbl_CountingWhatCountsTempTable
GROUP BY tbl_CountingWhatCountsTempTable.TeamName
PIVOT tbl_CountingWhatCountsTempTable.Category;

--
Build a little, test a little.


Iram said:
Karl,
Here you go per your request.... I need to merge the two below crosstab
queries. One query counts and the other sums...


Query Name: qry_CountingWhatCounts_RunCrosstabOnCategoryCounts

TRANSFORM Count(tbl_CountingWhatCountsTempTable.WorkerID) AS CountOfWorkerID
SELECT tbl_CountingWhatCountsTempTable.TeamName,
Count(tbl_CountingWhatCountsTempTable.WorkerID) AS [Total Of WorkerID]
FROM tbl_CountingWhatCountsTempTable
GROUP BY tbl_CountingWhatCountsTempTable.TeamName
PIVOT tbl_CountingWhatCountsTempTable.Category;






Query Name: qry_CountingWhatCounts_RunCrosstabOnCategoryAmounts

TRANSFORM Sum(tbl_CountingWhatCountsTempTable.Amount) AS SumOfAmount
SELECT tbl_CountingWhatCountsTempTable.TeamName,
Sum(tbl_CountingWhatCountsTempTable.Amount) AS Totals
FROM tbl_CountingWhatCountsTempTable
GROUP BY tbl_CountingWhatCountsTempTable.TeamName
PIVOT tbl_CountingWhatCountsTempTable.Category;





Iram

:

Post the SQL of both crosstab queries by opening in design view, click on
VIEW - SQL View, highlight all, copy, and paste in a post.

--
Build a little, test a little.


:


Hello,
I have created two critical queries. One counts how many times we did
something and the other sums the money amounts for the same "counted"
records. The common field between both crosstab queries is TeamName.
I need a single report that shows Counts and Amounts. How can you merge both
of these or is there a different way around this?


Thanks.
Iram
Click to expand...
Click to expand...
Click to expand...
 
AGAIN YOUR AWESOME!




KARL DEWEY said:
Your problem is that you included tbl_CountingWhatCountsTempTable.StatsDate
in the GROUP BY.

Use just the team.
.....
GROUP BY tbl_CountingWhatCountsTempTable.Team
PIVOT tbl_CountingWhatCountsTempTable.Category;

--
Build a little, test a little.


Iram said:
KARL,
I created a copy of your query so that I could create a parameterized query
that would pull in a date range however the crosstab no longer groups the
"Teams". I inserted the following parameters in the Query Parameters:
[Forms]![frm_FormDailyReports]![BeginDate] Date/Time
[Forms]![frm_FormDailyReports]![EndDate] Date/Time

The query comes up with multiples of the same team. How can I fix this. Your
query works beautifully and it is essential in our operations, but now I need
this secondary query with a date range which is also essential. Could you
help me fix this?

Update query with Date Range parameter
PARAMETERS [Forms]![frm_FormDailyReports]![BeginDate] DateTime,
[Forms]![frm_FormDailyReports]![EndDate] DateTime;
TRANSFORM Count(tbl_CountingWhatCountsTempTable.WorkerID) & " " &
Sum(tbl_CountingWhatCountsTempTable.Amount) AS CountWorker_SumAmount
SELECT tbl_CountingWhatCountsTempTable.Team,
Count(tbl_CountingWhatCountsTempTable.WorkerID) & " " &
Sum(tbl_CountingWhatCountsTempTable.Amount) AS [Total Counts/$]
FROM tbl_CountingWhatCountsTempTable
WHERE (((tbl_CountingWhatCountsTempTable.StatsDate) Between
[Forms]![frm_FormDailyReports]![BeginDate] And
[Forms]![frm_FormDailyReports]![EndDate]))
GROUP BY tbl_CountingWhatCountsTempTable.Team,
tbl_CountingWhatCountsTempTable.StatsDate
PIVOT tbl_CountingWhatCountsTempTable.Category;


Thanks.
Iram/mcp




KARL DEWEY said:
Try this --
TRANSFORM Count(tbl_CountingWhatCountsTempTable.WorkerID) & " -- " &
Sum(tbl_CountingWhatCountsTempTable.Amount) AS CountWorker_SumAmount
SELECT tbl_CountingWhatCountsTempTable.TeamName,
Count(tbl_CountingWhatCountsTempTable.WorkerID) & " -- " &
Sum(tbl_CountingWhatCountsTempTable.Amount) AS Total_CountWorker_SumAmount
FROM tbl_CountingWhatCountsTempTable
GROUP BY tbl_CountingWhatCountsTempTable.TeamName
PIVOT tbl_CountingWhatCountsTempTable.Category;

--
Build a little, test a little.


:

Karl,
Here you go per your request.... I need to merge the two below crosstab
queries. One query counts and the other sums...


Query Name: qry_CountingWhatCounts_RunCrosstabOnCategoryCounts

TRANSFORM Count(tbl_CountingWhatCountsTempTable.WorkerID) AS CountOfWorkerID
SELECT tbl_CountingWhatCountsTempTable.TeamName,
Count(tbl_CountingWhatCountsTempTable.WorkerID) AS [Total Of WorkerID]
FROM tbl_CountingWhatCountsTempTable
GROUP BY tbl_CountingWhatCountsTempTable.TeamName
PIVOT tbl_CountingWhatCountsTempTable.Category;






Query Name: qry_CountingWhatCounts_RunCrosstabOnCategoryAmounts

TRANSFORM Sum(tbl_CountingWhatCountsTempTable.Amount) AS SumOfAmount
SELECT tbl_CountingWhatCountsTempTable.TeamName,
Sum(tbl_CountingWhatCountsTempTable.Amount) AS Totals
FROM tbl_CountingWhatCountsTempTable
GROUP BY tbl_CountingWhatCountsTempTable.TeamName
PIVOT tbl_CountingWhatCountsTempTable.Category;





Iram

:

Post the SQL of both crosstab queries by opening in design view, click on
VIEW - SQL View, highlight all, copy, and paste in a post.

--
Build a little, test a little.


:


Hello,
I have created two critical queries. One counts how many times we did
something and the other sums the money amounts for the same "counted"
records. The common field between both crosstab queries is TeamName.
I need a single report that shows Counts and Amounts. How can you merge both
of these or is there a different way around this?


Thanks.
Iram
Click to expand...
Click to expand...
Click to expand...
 
Back
Top