Crosstab Query

[Mathew]

Hi all ...

I would like to create a crosstab query that groups scores
into categories if possible based on the results of a
query.
a sample of my table layout is below:

ID# Date Max
15151 01/01/2002 97
15150 01/15/2002 12
15149 01/31/2002 27
15148 02/07/2002 56
15147 02/07/2002 68


Below is my SQL:
TRANSFORM Count(myquery.id) AS CountOfid
SELECT Format([date],"mmmyy") AS [Month], Count
(myquery.id) AS [Total Of ID]
FROM myquery
GROUP BY Format([date],"mmmyy"), Year([date]), Month
([date])
ORDER BY Year([date]), Month([date])
PIVOT myquery.Max;

15151 01/01/2002 97
15150 01/15/2002 12
15149 01/31/2002 27
15148 02/07/2002 56
15147 02/07/2002 68

What I get is:

Month |12|27|56|68|97|
Jan02 |1 |1 | | |1 |
Feb02 | | | 1| 1| |

What I would like to get is:

Month |<50 |50-95 |>100
Jan02 | 2 | | 1
Feb02 | | 2 |


Any Help would be appreciated
Thanks
Mathew

 

[Chris2]

See Below:

Hi all ...

I would like to create a crosstab query that groups scores
into categories if possible based on the results of a
query.
a sample of my table layout is below:

ID# Date Max
15151 01/01/2002 97
15150 01/15/2002 12
15149 01/31/2002 27
15148 02/07/2002 56
15147 02/07/2002 68


Below is my SQL:
TRANSFORM Count(myquery.id) AS CountOfid
SELECT Format([date],"mmmyy") AS [Month], Count
(myquery.id) AS [Total Of ID]
FROM myquery
GROUP BY Format([date],"mmmyy"), Year([date]), Month
([date])
ORDER BY Year([date]), Month([date])
PIVOT myquery.Max;

15151 01/01/2002 97
15150 01/15/2002 12
15149 01/31/2002 27
15148 02/07/2002 56
15147 02/07/2002 68

What I get is:

Month |12|27|56|68|97|
Jan02 |1 |1 | | |1 |
Feb02 | | | 1| 1| |

What I would like to get is:

Month |<50 |50-95 |>100
Jan02 | 2 | | 1
Feb02 | | 2 |

CREATE TABLE MyQuery
(ID INTEGER
,myDate DATE
,Max INTEGER
,CONSTRAINT pk_MyQuery PRIMARY KEY (MyQueryID)
)

YourQuery (changed date column name to avoid using a reserved word in the
test table)

TRANSFORM Count(M1.id) AS CountOfid
SELECT Format(M1.myDate,"mmmyy") AS [Month], Count(M1.id) AS [Total Of ID]
FROM myquery AS M1
GROUP BY Format(M1.myDate,"mmmyy"), Year(M1.myDate), Month(M1.myDate)
ORDER BY Year(m1.myDate), Month(M1.myDate)
PIVOT M1.Max;

NewQuery:

TRANSFORM Count(M1.Max)
SELECT FORMAT(M1.myDate,"mmmyy") AS [Month]
,Count(M1.ID) AS [Total Of ID]
FROM myquery AS M1
GROUP BY FORMAT(M1.myDate,"mmmyy")
PIVOT SWITCH((M1.Max) < 50, "<50",
((M1.Max >=50) AND (M1.Max <=95)), "50-95",
(M1.Max) > 100, ">100")


I'm personally curious, though, in wondering what happens to values 96-99?


My results were pretty close to what you needed, except the value of 97
fell outside the SWITCH, and Access created it's own <> result, which I
found to be interesting, to note the 97 value's occurrence.

Month Total Of ID <> <50 50-95

Feb02 2 2

Jan02 3 1 2





Modify the above query to taste.


It was semi-annyoing, though, to note that I couldn't seem to control where
the columns showed up.
I changed the 97 value to 107, and the columns appeared in the order of <50,

100, and 50-95 headers).

Sincerely,

Chris O.

 

[Guest]

Chris -

Thanks for all of your help ... it took a little tweaking
to get exactly what I wanted but you provided a great
resolution ... I would never have gotten there without
your advice ...

Thanks so much

Mathew

BTW - to help your curiosity ... it was a typo ... it was
supposed to say >95 instead of >100 ...

-----Original Message-----
See Below:

Hi all ...

I would like to create a crosstab query that groups scores
into categories if possible based on the results of a
query.
a sample of my table layout is below:

ID# Date Max
15151 01/01/2002 97
15150 01/15/2002 12
15149 01/31/2002 27
15148 02/07/2002 56
15147 02/07/2002 68


Below is my SQL:
TRANSFORM Count(myquery.id) AS CountOfid
SELECT Format([date],"mmmyy") AS [Month], Count
(myquery.id) AS [Total Of ID]
FROM myquery
GROUP BY Format([date],"mmmyy"), Year([date]), Month
([date])
ORDER BY Year([date]), Month([date])
PIVOT myquery.Max;

15151 01/01/2002 97
15150 01/15/2002 12
15149 01/31/2002 27
15148 02/07/2002 56
15147 02/07/2002 68

What I get is:

Month |12|27|56|68|97|
Jan02 |1 |1 | | |1 |
Feb02 | | | 1| 1| |

What I would like to get is:

Month |<50 |50-95 |>100
Jan02 | 2 | | 1
Feb02 | | 2 |

CREATE TABLE MyQuery
(ID INTEGER
,myDate DATE
,Max INTEGER
,CONSTRAINT pk_MyQuery PRIMARY KEY (MyQueryID)
)

YourQuery (changed date column name to avoid using a reserved word in the
test table)

TRANSFORM Count(M1.id) AS CountOfid
SELECT Format(M1.myDate,"mmmyy") AS [Month], Count(M1.id) AS [Total Of ID]
FROM myquery AS M1
GROUP BY Format(M1.myDate,"mmmyy"), Year(M1.myDate), Month (M1.myDate)
ORDER BY Year(m1.myDate), Month(M1.myDate)
PIVOT M1.Max;

NewQuery:

TRANSFORM Count(M1.Max)
SELECT FORMAT(M1.myDate,"mmmyy") AS [Month]
,Count(M1.ID) AS [Total Of ID]
FROM myquery AS M1
GROUP BY FORMAT(M1.myDate,"mmmyy")
PIVOT SWITCH((M1.Max) < 50, "<50",
((M1.Max >=50) AND (M1.Max <=95)), "50- 95",
(M1.Max) > 100, ">100")


I'm personally curious, though, in wondering what happens to values 96-99?


My results were pretty close to what you needed, except the value of 97
fell outside the SWITCH, and Access created it's own <> result, which I
found to be interesting, to note the 97 value's occurrence.

Month Total Of ID <> <50 50-95

Feb02 2 2

Jan02 3 1 2





Modify the above query to taste.


It was semi-annyoing, though, to note that I couldn't seem to control where
the columns showed up.
I changed the 97 value to 107, and the columns appeared in the order of <50,

100, and 50-95 headers).

Sincerely,

Chris O.

Any Help would be appreciated
Thanks
Mathew

.

 

[Chris2]

Chris -

Thanks for all of your help ... it took a little tweaking
to get exactly what I wanted but you provided a great
resolution ... I would never have gotten there without
your advice ...

Thanks so much

Mathew

BTW - to help your curiosity ... it was a typo ... it was
supposed to say >95 instead of >100 ...

Ah! I was wondering, typos are usually the answer for that sort of thing.

And, of course, you are welcome.

Sincerely,

Chris O.