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

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
 
K

KARL DEWEY

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

PieterLinden via AccessMonster.com

Iram said:
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...

Without seeing it, my guess is to create a query where you have something
like

SELECT fld1, fld2,...
FROM CrosstabQuery
UNION ALL
SELECT fld1, fld2,...
FROM SummaryQuery
GROUP BY fld1,...
 
I

Iram

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
 
K

KARL DEWEY

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

Iram

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

Iram

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

KARL DEWEY

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

Iram

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

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

UNION and Crosstab query 5
Insert a crosstab to temp table for report 6
CrossTab Query question 1
Crosstab query to include fields with Null value 2
how to make a crosstab with a where clause? 3
Union Query with a Crosstab Query 17
Help with crosstab query 2
report using a subreport based on a crosstab query 5

Top