|
| 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 |
|
#1
May 3rd, 2010, 08:42 PM
posted to microsoft.public.access.queries
|
|||
|
|||
Can you merge a "Count" Crosstab with a "Money Sum" Crosstab?
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 
|
|
#2
May 3rd, 2010, 09:14 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Can you merge a "Count" Crosstab with a "Money Sum" Crosstab?
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. "Iram" wrote: 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
|
|
#3
May 3rd, 2010, 10:04 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Can you merge a "Count" Crosstab with a "Money Sum" Crosstab?
Iram wrote:
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 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,... -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201005/1
|
|
#4
May 3rd, 2010, 10:28 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Can you merge a "Count" Crosstab with a "Money Sum" Crosstab?
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_RunCrosstabOnCategoryAmount s 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" wrote: 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. "Iram" wrote: 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
|
|
#5
May 3rd, 2010, 11:41 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Can you merge a "Count" Crosstab with a "Money Sum" Crosstab?
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" wrote: 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_RunCrosstabOnCategoryAmount s 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" wrote: 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. "Iram" wrote: 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
|
|
#6
May 4th, 2010, 04:59 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Can you merge a "Count" Crosstab with a "Money Sum" Crosstab?
KARL DEWEY, THAT WAS IMPRESSIVE!!!!
It works beautifully! I didn't think it was possible! YOU ARE THE MAN! Thanks! Iram/mcp "KARL DEWEY" wrote: 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" wrote: 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_RunCrosstabOnCategoryAmount s 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" wrote: 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. "Iram" wrote: 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
|
|
#7
May 4th, 2010, 06:27 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Can you merge a "Count" Crosstab with a "Money Sum" Crosstab?
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" wrote: 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" wrote: 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_RunCrosstabOnCategoryAmount s 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" wrote: 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. "Iram" wrote: 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
|
|
#8
May 4th, 2010, 09:14 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Can you merge a "Count" Crosstab with a "Money Sum" Crosstab?
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" wrote: 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" wrote: 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" wrote: 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_RunCrosstabOnCategoryAmount s 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" wrote: 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. "Iram" wrote: 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
|
|
#9
May 5th, 2010, 12:04 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Can you merge a "Count" Crosstab with a "Money Sum" Crosstab?
AGAIN YOUR AWESOME!
"KARL DEWEY" wrote: 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" wrote: 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" wrote: 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" wrote: 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_RunCrosstabOnCategoryAmount s 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" wrote: 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. "Iram" wrote: 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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
