Prev: Summing multiple fields in matrix/table (qry)
Next: Append Query Not Working
From: Iram on 4 May 2010 11:59
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_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: Iram on 4 May 2010 13:27
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
From: KARL DEWEY on 4 May 2010 16:14
>>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
From: Iram on 4 May 2010 19:04
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_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
First  |  Prev  | 
Pages: 1 2
Prev: Summing multiple fields in matrix/table (qry)
Next: Append Query Not Working