Crosstab - Multiple values on each row

  • Thread starter Thread starter Duncs
  • Start date Start date
D

Duncs

I'm trying to do a crosstab query, that will show multiple values on
each row, in separate columns. so, I want to display the following:

Month Completed Locked Pended Total
Sep-09 0 0 2 2
Oct-09 15 0 10 25
Nov-09 347 11 107 465

However, my attempt is giving the following:

Month Total Accounts Completed Locked Pended
Sep-2009 2 2
Oct-2009 15 15
Oct-2009 10
10
Nov-2009 11 11
Nov-2009 347 347
Nov-2009 107
107

My query SQL is as follows:

TRANSFORM Count(tblActionDetails.UnallocatedId) AS
CountOfUnallocatedId
SELECT Format([Date_Started],"MMM-YYYY") AS [Month], Count
(tblActionDetails.UnallocatedId) AS [Total Accounts]
FROM tblActionDetails INNER JOIN tblStatus ON
tblActionDetails.Worked_Status = tblStatus.Description
WHERE (((tblActionDetails.Worked_Status)<>"Workable"))
GROUP BY Format([Date_Started],"MM"), Format([Date_Started],"MMM-
YYYY"), tblStatus.StatusID
PIVOT tblActionDetails.Worked_Status;

I've never used Crosstab queries before, so I'd appreciate any and all
help provided.

TIA

Duncs
 
Duncs wrote:
| I'm trying to do a crosstab query, that will show multiple values on
| each row, in separate columns. so, I want to display the following:
|
| Month Completed Locked Pended Total
| Sep-09 0 0 2 2
| Oct-09 15 0 10 25
| Nov-09 347 11 107 465
|
| However, my attempt is giving the following:
|
| Month Total Accounts Completed Locked Pended
| Sep-2009 2 2
| Oct-2009 15 15
| Oct-2009 10
| 10
| Nov-2009 11 11
| Nov-2009 347 347
| Nov-2009 107
| 107
|
| My query SQL is as follows:
|
| TRANSFORM Count(tblActionDetails.UnallocatedId) AS
| CountOfUnallocatedId
| SELECT Format([Date_Started],"MMM-YYYY") AS [Month], Count
| (tblActionDetails.UnallocatedId) AS [Total Accounts]
| FROM tblActionDetails INNER JOIN tblStatus ON
| tblActionDetails.Worked_Status = tblStatus.Description
| WHERE (((tblActionDetails.Worked_Status)<>"Workable"))
| GROUP BY Format([Date_Started],"MM"), Format([Date_Started],"MMM-
| YYYY"), tblStatus.StatusID
| PIVOT tblActionDetails.Worked_Status;
|
| I've never used Crosstab queries before, so I'd appreciate any and all
| help provided.

You grouped by fields that are useless in your query:
Format([Date_Started],"MM") and
StatusID

Not only unclick them in query disign, but clear them completely.

Your query will look like this:

TRANSFORM Count(UnallocatedId)
SELECT
Format([Date_Started],"MMM-YYYY") AS [Month]
, Count(tblActionDetails.UnallocatedId) AS [Total Accounts]
FROM...
WHERE ...
GROUP BY Format([Date_Started],"MMM-YYYY")
PIVOT tblActionDetails.Worked_Status;
 
Duncs wrote:

| I'm trying to do a crosstab query, that will show multiple values on
| each row, in separate columns.  so, I want to display the following:
|
| Month     Completed     Locked     Pended     Total
| Sep-09    0                   0              2              2
| Oct-09    15                  0             10             25
| Nov-09    347                11           107           465
|
| However, my attempt is giving the following:
|
| Month Total Accounts     Completed     Locked      Pended
| Sep-2009 2                 2
| Oct-2009 15                       15
| Oct-2009 10
| 10
| Nov-2009 11                                           11
| Nov-2009 347                     347
| Nov-2009 107
| 107
|
| My query SQL is as follows:
|
| TRANSFORM Count(tblActionDetails.UnallocatedId) AS
| CountOfUnallocatedId
| SELECT Format([Date_Started],"MMM-YYYY") AS [Month], Count
| (tblActionDetails.UnallocatedId) AS [Total Accounts]
| FROM tblActionDetails INNER JOIN tblStatus ON
| tblActionDetails.Worked_Status = tblStatus.Description
| WHERE (((tblActionDetails.Worked_Status)<>"Workable"))
| GROUP BY Format([Date_Started],"MM"), Format([Date_Started],"MMM-
| YYYY"), tblStatus.StatusID
| PIVOT tblActionDetails.Worked_Status;
|
| I've never used Crosstab queries before, so I'd appreciate any and all
| help provided.

You grouped by fields that are useless in your query:
Format([Date_Started],"MM")  and
StatusID

Not only unclick them in query disign, but clear them completely.

Your query will look like this:

TRANSFORM Count(UnallocatedId)
SELECT
   Format([Date_Started],"MMM-YYYY") AS [Month]
, Count(tblActionDetails.UnallocatedId) AS [Total Accounts]
FROM...
WHERE ...
GROUP BY Format([Date_Started],"MMM-YYYY")
PIVOT tblActionDetails.Worked_Status;
Click to expand...

KN,

The problem is, when I execute the query, I get the dates on the left
listed as:

Nov-2009
Oct-2009
Sep-2009

And what I wanted was them listed as:

Sep-2009
Oct-2009
Nov-2009

That's why I included the Format([Date_Started],"MM") entry.

Duncs
 
Remove tblStatus.StatusID from the Group by clause
and
change Format([Date_Started],"MM" to Format([Date_Started],"yyyy-MM"

TRANSFORM Count(tblActionDetails.UnallocatedId) AS
CountOfUnallocatedId
SELECT Format([Date_Started],"MMM-YYYY") AS [Month], Count
(tblActionDetails.UnallocatedId) AS [Total Accounts]
FROM tblActionDetails INNER JOIN tblStatus ON
tblActionDetails.Worked_Status = tblStatus.Description
WHERE (((tblActionDetails.Worked_Status)<>"Workable"))
GROUP BY Format([Date_Started],"yyyy-MM")
, Format([Date_Started],"MMM-YYYY")
, tblStatus.StatusID
PIVOT tblActionDetails.Worked_Status;


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks to all for your help. It's now working fine.

Rgds

Duncs

Remove tblStatus.StatusID from the Group by clause
and
change Format([Date_Started],"MM" to Format([Date_Started],"yyyy-MM"

TRANSFORM Count(tblActionDetails.UnallocatedId) AS
CountOfUnallocatedId
SELECT Format([Date_Started],"MMM-YYYY") AS [Month], Count
(tblActionDetails.UnallocatedId) AS [Total Accounts]
FROM tblActionDetails INNER JOIN tblStatus ON
tblActionDetails.Worked_Status = tblStatus.Description
WHERE (((tblActionDetails.Worked_Status)<>"Workable"))
GROUP BY Format([Date_Started],"yyyy-MM")
, Format([Date_Started],"MMM-YYYY")
, tblStatus.StatusID
PIVOT tblActionDetails.Worked_Status;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County


I'm trying to do a crosstab query, that will show multiple values on
each row, in separate columns.  so, I want to display the following:
Click to expand...
Month     Completed     Locked     Pended     Total
Sep-09    0                   0              2              2
Oct-09    15                  0             10             25
Nov-09    347                11           107           465
Click to expand...
However, my attempt is giving the following:
Click to expand...
Month      Total Accounts     Completed     Locked      Pended
Sep-2009   2                                       2
Oct-2009   15                       15
Oct-2009   10
10
Nov-2009   11                                           11
Nov-2009   347                     347
Nov-2009   107
107
Click to expand...
My query SQL is as follows:
Click to expand...
TRANSFORM Count(tblActionDetails.UnallocatedId) AS
CountOfUnallocatedId
SELECT Format([Date_Started],"MMM-YYYY") AS [Month], Count
(tblActionDetails.UnallocatedId) AS [Total Accounts]
FROM tblActionDetails INNER JOIN tblStatus ON
tblActionDetails.Worked_Status = tblStatus.Description
WHERE (((tblActionDetails.Worked_Status)<>"Workable"))
GROUP BY Format([Date_Started],"MM"), Format([Date_Started],"MMM-
YYYY"), tblStatus.StatusID
PIVOT tblActionDetails.Worked_Status;
Click to expand...
I've never used Crosstab queries before, so I'd appreciate any and all
help provided.

Duncs- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
Back
Top