Newbie Crosstab Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am a newbie at crosstabs and have question on either combining multiple crosstabs or a "super" crosstab. I have looked at Duane Hookoms advice on the query and the cartesian idea and think that is what I need but can't seem to understand it

My data table has a Landowner Type field and 10 fields (named BMP_1 ... BMP_10) each BMP* field has one of 4 "criteria" type answers ("meets", "exceeds", "minor", "major" ).

I can do a xtab on each field and sum a count of each criteria, with this sql (I now have 10 xtabs

TRANSFORM Count(BMP_Table.Insp_ID_PK) AS CountOfInsp_ID_P
SELECT BMP_Table.[Landowner Type], Count(BMP_Table.Insp_ID_PK) AS Tota
FROM BMP_Tabl
GROUP BY BMP_Table.[Landowner Type
PIVOT BMP_Table.[BMP_5]

and what I get is something like this (for each of the 10 BMP queries)

BMP_
LandownerType Meets Exceeds Minor Major Tota
-------------------------------------------------------------------------------
Industrial 1 3 5 1 10
TIMO 3 4 2 2 11
NIPFL 1 6 3 0 1
State 5 4 1 0 1


This makes it awkward for reporting and other things and What I want is one query withall landowner types and all bmp* fields accompanied by the total count across each criteria like

Landowner Meets Exceeds Minor Major Tota
------------------------------------------------------
Industrial BMP_1 3 5 5 1 1
Industrial BMP_2 5 4 1 0 1
Industrial BMP_3 6 1 1 0
Industrial BMP_4 1 7 3 2 1

.................................

State BMP_8 3 5 5 1 1
State BMP_9 5 4 1 0 10
State BMP_10 6 1 1 0


Perhaps someone can point me in the right direction

Many thanks, Steve
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your BMP_Table table is not normalized you should not have columns named
BMP_1, BMP_2, BMP_3, etc. The table should be designed like this:

BMP_Table:
Insp_ID_PK - AutoNumber ? or some number
Landowner Type - text or number referencing a table of landowner types
BMPCode - the 1, 2, 3, etc. of the BMP_1, BMP_2, BPM_3, etc.
BMPValue - Number links to table containing:
1:Meets, 2:Exceeds, 3:Minor, 4:Major
So you can add more attributes as needed.

... other columns ? ...

Example data:

Insp_ID_PK Landowner Type BMPCode BMPValue
1 Industrial 1 1
2 Industrial 2 3
3 Industrial 3 1
.... etc. ...

The contents of table BMPValues would look like this:

BMPValueID BMPDescription
1 Meets
2 Exceeds
3 Minor
4 Major

Then your query would look like this:

TRANSFORM Count(B.*) AS DaValue
SELECT B.[Landowner Type], B.BMPCode, Count(B.*) AS Total
FROM BMP_Table As B INNER JOIN BMPValues As V
ON B.BMPValue = B.BMPValueID
GROUP BY B.[Landowner Type], B.BMPCode
PIVOT V.BMPDescription

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQMdlaIechKqOuFEgEQLXhACgmLcXa+wgToDkGvt1/u/3aNJUpRcAoOSI
qKBRh2NMATvM/ReqMEEtfWvo
=YGZ+
-----END PGP SIGNATURE-----


Steven said:
I am a newbie at crosstabs and have question on either combining multiple crosstabs or a "super" crosstab. I have looked at Duane Hookoms advice on the query and the cartesian idea and think that is what I need but can't seem to understand it .

My data table has a Landowner Type field and 10 fields (named BMP_1 ... BMP_10) each BMP* field has one of 4 "criteria" type answers ("meets", "exceeds", "minor", "major" ).

I can do a xtab on each field and sum a count of each criteria, with this sql (I now have 10 xtabs)

TRANSFORM Count(BMP_Table.Insp_ID_PK) AS CountOfInsp_ID_PK
SELECT BMP_Table.[Landowner Type], Count(BMP_Table.Insp_ID_PK) AS Total
FROM BMP_Table
GROUP BY BMP_Table.[Landowner Type]
PIVOT BMP_Table.[BMP_5];

and what I get is something like this (for each of the 10 BMP queries):

BMP_5
LandownerType Meets Exceeds Minor Major Total
-------------------------------------------------------------------------------
Industrial 1 3 5 1 10
TIMO 3 4 2 2 11
NIPFL 1 6 3 0 10
State 5 4 1 0 10


This makes it awkward for reporting and other things and What I want is one query withall landowner types and all bmp* fields accompanied by the total count across each criteria like:


Landowner Meets Exceeds Minor Major Total
------------------------------------------------------
Industrial BMP_1 3 5 5 1 14
Industrial BMP_2 5 4 1 0 10
Industrial BMP_3 6 1 1 0 8
Industrial BMP_4 1 7 3 2 13

.................................

State BMP_8 3 5 5 1 14
State BMP_9 5 4 1 0 10
State BMP_10 6 1 1 0 8



Perhaps someone can point me in the right direction.
Click to expand...
 
Steve,

You need to create a query to normalize your data. It
will be a union query that looks something like:

SELECT [Landowner Type], 1 as BMP_Index, BMP_1 as Criteria
FROM BMP_Table
WHERE BMP_1 IS NOT NULL
UNION
SELECT [Landowner Type], 2 as BMP_Index, BMP_2 as Criteria
FROM BMP_Table
WHERE BMP_2 IS NOT NULL
....
UNION
SELECT [Landowner Type], 10 as BMP_Index, BMP_10 as
Criteria FROM BMP_Table
WHERE BMP_10 IS NOT NULL

Save this query as qry_BMP_Table

Then build your crosstab using the [Landowner Type] and
[BMP_Index] as Row headers and Criteria as the Column
Header

HTH
Dale Fye
-----Original Message-----
I am a newbie at crosstabs and have question on either
Click to expand...
combining multiple crosstabs or a "super" crosstab. I
have looked at Duane Hookoms advice on the query and the
cartesian idea and think that is what I need but can't
seem to understand it .
My data table has a Landowner Type field and 10 fields
Click to expand...
(named BMP_1 ... BMP_10) each BMP* field has one of
4 "criteria" type answers
("meets", "exceeds", "minor", "major" ).
I can do a xtab on each field and sum a count of each
Click to expand...
criteria, with this sql (I now have 10 xtabs)
TRANSFORM Count(BMP_Table.Insp_ID_PK) AS CountOfInsp_ID_PK
SELECT BMP_Table.[Landowner Type], Count
Click to expand...
(BMP_Table.Insp_ID_PK) AS Total
FROM BMP_Table
GROUP BY BMP_Table.[Landowner Type]
PIVOT BMP_Table.[BMP_5];

and what I get is something like this (for each of the 10 BMP queries):

BMP_5
LandownerType Meets Exceeds Minor Major Total
---------------------------------------------------------- ---------------------
Industrial 1 3 5 1 10
TIMO 3 4 2 2 11
NIPFL 1 6 3 0 10
State 5 4 1 0 10


This makes it awkward for reporting and other things and
Click to expand...
What I want is one query withall landowner types and
all bmp* fields accompanied by the total count across each
criteria like:
 
Back
Top