Crosstab - Multiple values on each row

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
 
K

Krzysztof Naworyta

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;
 
D

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;
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
 
J

John Spencer

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
 
D

Duncs

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

crosstab query total column 1
Crosstab query and Row Heading 1
Crosstab by Month 1
Attn: Duane Crosstab by Month 1
Maintaining cell 'variable' values even after the the macro has en 1
Crosstab query and formatted date 1
Calulating FY Totals 3
Crosstab Query Question 12

Top