need help making a query (crosstab instead of Pivot tables?)

[webicky]

I'm trying to make a query that will imitate a 2-layer pivot table (as
a- the pivot table takes a while to load up and b- i'd rather have the
data in a query anyway). Guessing that is a crosstab or a (pivot table
query, but could not find that). Access 2003

I have multiple repeated id's (17k),multiple repeated subgroups (at
least 25), 3 repeated types, and number of people (type/subgroup
specific). each id only has one type/subgroup - These are correctly
collapsed by the pivot table
Orig table
ID # Subgroup Type Num ppl
100 1 6 100
100 1 7 10
100 1 8 50
100 3 6 25
100 3 7 50
100 3 8 5
100 5 6 111
200 1 6 10
200 1 7 20
200 1 8 30
200 3 6 40
300 1 6 5

Pivot displays (correctly)
ID# (now unique) Type/Subgroup
6.1 6.3 6.5,, 7.1 7.3
100 100 25 111 10 50
200 10 40
300 5

Cross tab only allows me to get 1 piece of data, so I am thinking i
need to link two crosstabs? I'd like to NOT have to manually make
every group since there are more than 25*3 combinations.

Any help would be appreciated.

Thanks

 

[Duane Hookom]

You should be able to create an expression to use as the column heading like:
colhead: [Type] & "_" & [SubGroup]
I don't think you can use a period since it isn't allowed in field naming.

 

[MGFoster]

I'm trying to make a query that will imitate a 2-layer pivot table (as
a- the pivot table takes a while to load up and b- i'd rather have the
data in a query anyway). Guessing that is a crosstab or a (pivot table
query, but could not find that). Access 2003

I have multiple repeated id's (17k),multiple repeated subgroups (at
least 25), 3 repeated types, and number of people (type/subgroup
specific). each id only has one type/subgroup - These are correctly
collapsed by the pivot table
Orig table
ID # Subgroup Type Num ppl
100 1 6 100
100 1 7 10
100 1 8 50
100 3 6 25
100 3 7 50
100 3 8 5
100 5 6 111
200 1 6 10
200 1 7 20
200 1 8 30
200 3 6 40
300 1 6 5

Pivot displays (correctly)
ID# (now unique) Type/Subgroup
6.1 6.3 6.5,, 7.1 7.3
100 100 25 111 10 50
200 10 40
300 5

Cross tab only allows me to get 1 piece of data, so I am thinking i
need to link two crosstabs? I'd like to NOT have to manually make
every group since there are more than 25*3 combinations.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Probably like this:

TRANSFORM SUM([Num ppl]) As theValue
SELECT [ID#]
FROM table_name
GROUP BY [ID#]
PIVOT Type & "." & Subgroup

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBSqlUQIechKqOuFEgEQITNgCbBY9VEn6DuNuJHrA2vOVZZ4Dt8P8AoJ9S
YT1Csp6uQaTU5AXzmz4upK07
=FXT6
-----END PGP SIGNATURE-----

 

[webicky]

Problem solved!

I'm trying to make a query that will imitate a 2-layer pivot table (as
a- the pivot table takes a while to load up and b- i'd rather have the
data in a query anyway). Guessing that is a crosstab or a (pivot table
query, but could not find that).  Access 2003

I have multiple repeated id's (17k),multiple repeated subgroups (at
least 25), 3 repeated types, and number of people (type/subgroup
specific).  each id only has one type/subgroup - These are correctly
collapsed by the pivot table
Orig table
ID #   Subgroup    Type    Num ppl
100       1              6           100
100       1             7              10
100        1              8             50
100        3             6             25
100        3             7             50
100        3             8              5
100        5              6                 111
200       1              6               10
200       1              7               20
200        1            8                30
200       3             6                 40
300        1           6                       5

Pivot displays (correctly)
ID# (now unique)   Type/Subgroup
                          6.1   6.3  6.5,,   7.1 7.3
100                     100  25    111    10   50
200                      10   40
300                         5

Cross tab only allows me to get 1 piece of data, so I am thinking i
need to link two crosstabs?  I'd like to NOT have to manually make
every group since there are more than 25*3 combinations.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Probably like this:

TRANSFORM SUM([Num ppl]) As theValue
SELECT [ID#]
FROM table_name
GROUP BY [ID#]
PIVOT Type & "." & Subgroup

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup.  I DO NOT respond to emails **

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

iQA/AwUBSqlUQIechKqOuFEgEQITNgCbBY9VEn6DuNuJHrA2vOVZZ4Dt8P8AoJ9S
YT1Csp6uQaTU5AXzmz4upK07
=FXT6
-----END PGP SIGNATURE------ Hide quoted text -

- Show quoted text -