Can you merge a "Count" Crosstab with a "Money Sum" Crosstab?
in [Queries]
|
From: Iram on 3 May 2010 15:42 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
From: KARL DEWEY on 3 May 2010 16:14 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
From: PieterLinden via AccessMonster.com on 3 May 2010 17:04 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/Forums.aspx/access-queries/201005/1
From: Iram on 3 May 2010 17:28 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" 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
From: KARL DEWEY on 3 May 2010 18:41 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_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" 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
|
Next
|
Last
Pages: 1 2 Prev: Summing multiple fields in matrix/table (qry) Next: Append Query Not Working |