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
