>>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_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
|
Similar Threads
