Crosstab query

[jason.teen@gmail.com]

Hi All,

I am having trouble creating a crosstab query.
In my original data I have two columns, One called "Categorize d" and
one called "Mapped'
in which those columns can hold values of "true" or "false" only in
checkbox format

Ie.

----+-----
Cat | Map
----+-----
F | T
T | F
T | F
T | F
T | F
F | F
----+-----

>From this data I want to create a crosstab query so that I can provide

some metrics to this data of how many "true" and "false" I have to be
represented nicely as so:

+-----+-----
| Cat | Map
-----+-----+-----
True | 4 | 1
-----+-----+-----
False| 2 | 5
-----+-----+-----

Does anyone have any ideas on how about doing this? I keep on getting
the error messages and the output I have only allows to have only one
or the other shown. not both.

Appreciate It.

Cheers,

[GH]

Re: Crosstab query

Jason,

If these are the only fields you have to work with, you don't really
have enough to create a crosstab query unless you create a starting
query that gives you at least three "columns" for the crosstab.
However, there are other ways to perform the query in question. The
only way I came up with to successfully give you the format requested
is the following union with subqueries. Again, large numbers of rows
can cause performance degradation. Since I did not know your table
name, I made up my own.

SELECT "True" as [T/F],
(SELECT Count(Categoriz ed) FROM tblTF WHERE Categorized= "T") AS
[Categorized],
(SELECT Count(Mapped') FROM tblTF WHERE Map = "T") as [Mapped']
FROM tblTF
UNION
SELECT "False" as [T/F],
(SELECT Count(Categoriz ed) FROM tblTF WHERE Categorized= "F") AS
[Categorized],
(SELECT Count(Mapped') FROM tblTF WHERE Mapped'= "F") as [Mapped]
FROM tblTF
ORDER BY 1 DESC

The output datasheet looks like:

T/F Categorized Mapped
True 4 1
False 2 5

If there is a better way, I hope someone thinks of it, but my brain
couldn't come up with anything else today.

Good luck!
GH

jason.teen@gmai l.com wrote:

Hi All,
>
I am having trouble creating a crosstab query.
In my original data I have two columns, One called "Categorize d" and
one called "Mapped'
in which those columns can hold values of "true" or "false" only in
checkbox format
>
Ie.
>
----+-----
Cat | Map
----+-----
F | T
T | F
T | F
T | F
T | F
F | F
----+-----
>

From this data I want to create a crosstab query so that I can provide

some metrics to this data of how many "true" and "false" I have to be
represented nicely as so:
>
+-----+-----
| Cat | Map
-----+-----+-----
True | 4 | 1
-----+-----+-----
False| 2 | 5
-----+-----+-----
>
Does anyone have any ideas on how about doing this? I keep on getting
the error messages and the output I have only allows to have only one
or the other shown. not both.
>
Appreciate It.
>
Cheers,

[GH]

Re: Crosstab query

Sorry, but I just noticed a typo in the query I posted from my
cut-and-paste operations. Several of the references to the Mapped
field have an added apostrophe; that is in error. I also cut-off one
of the field names. The query should actually be:

SELECT "True" as [T/F],
(SELECT Count(Categoriz ed) FROM tblTF WHERE Categorized= "T") AS
[Categorized],
(SELECT Count(Mapped) FROM tblTF WHERE Mapped = "T") AS [Mapped]
FROM tblTF
UNION
SELECT "False" as [T/F],
(SELECT Count(Categoriz ed) FROM tblTF WHERE Categorized= "F") AS
[Categorized],
(SELECT Count(Mapped) FROM tblTF WHERE Mapped= "F") AS [Mapped]
FROM tblTF
ORDER BY 1 DESC

That's why we have QA, right?

GH wrote:

Jason,
>

....

jason.teen@gmai l.com wrote:

Hi All,

I am having trouble creating a crosstab query.
In my original data I have two columns, One called "Categorize d" and
one called "Mapped'
in which those columns can hold values of "true" or "false" only in
checkbox format

Ie.

----+-----
Cat | Map
----+-----
F | T
T | F
T | F
T | F
T | F
F | F
----+-----

>From this data I want to create a crosstab query so that I can provide

some metrics to this data of how many "true" and "false" I have to be
represented nicely as so:

+-----+-----
| Cat | Map
-----+-----+-----
True | 4 | 1
-----+-----+-----
False| 2 | 5
-----+-----+-----

Does anyone have any ideas on how about doing this? I keep on getting
the error messages and the output I have only allows to have only one
or the other shown. not both.

Appreciate It.

Cheers,