Counting in three dimentions

J

John Melbourne

I'm trying to add a dimension to my count but when I add a group by I get the
additional rows but the counts are all the same within the columns. My table
contains the name, sex, state, workplace and Id of employees. I currently
have the following code that gives me one dimension, I want to add by
workplace.

ID NAME M/F STATE WORK
0001 TED M 2 OFFICE
0002 PAT F 3 OFFICE
0003 JOHN M 3 WORKSHOP
0004 MARY F 2 FIELD
0005 CLARE F 3 OFFICE
0006 JIM M 2 FIELD

Current count looks like

MNNSW FENSW MNVIC FEVIC
2 1 1 2

Would like to

MNNSW FENSW MNVIC FEVIC TOTAL
FIELD 1 1 2
OFFICE 1 2 3
WORKSHOP 1 1
TOTAL 2 1 1 2 6

My current code is

SELECT Count([tblAfAct].fldID) AS [FE-MALE COUNT],
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "M" AND F01STC = "2") AS MNNSW,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "F" AND F01STC = "2") AS FENSW,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "M" AND F01STC = "3") AS MNVIC,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "F" AND F01STC = "3") AS FEVIC,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "M" AND F01STC = "4") AS MNQLD,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "F" AND F01STC = "4") AS FEQLD,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "M" AND F01STC = "5") AS MNSA,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "F" AND F01STC = "5") AS FESA,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "M" AND F01STC = "6") AS MNWA,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "F" AND F01STC = "6") AS FEWA,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "M" AND F01STC = "7") AS MNTAS,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "F" AND F01STC = "7") AS FETAS
FROM [TEST-Q-END-VBA];

Thanks all
 
G

Gary Walter

John Melbourne said:
I'm trying to add a dimension to my count but when I add a group by I get
the
additional rows but the counts are all the same within the columns. My
table
contains the name, sex, state, workplace and Id of employees. I currently
have the following code that gives me one dimension, I want to add by
workplace.

ID NAME M/F STATE WORK
0001 TED M 2 OFFICE
0002 PAT F 3 OFFICE
0003 JOHN M 3 WORKSHOP
0004 MARY F 2 FIELD
0005 CLARE F 3 OFFICE
0006 JIM M 2 FIELD

Current count looks like

MNNSW FENSW MNVIC FEVIC
2 1 1 2

Would like to

MNNSW FENSW MNVIC FEVIC TOTAL
FIELD 1 1 2
OFFICE 1 2 3
WORKSHOP 1 1
TOTAL 2 1 1 2 6

My current code is

SELECT Count([tblAfAct].fldID) AS [FE-MALE COUNT],
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "M" AND F01STC = "2") AS MNNSW,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "F" AND F01STC = "2") AS FENSW,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "M" AND F01STC = "3") AS MNVIC,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "F" AND F01STC = "3") AS FEVIC,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "M" AND F01STC = "4") AS MNQLD,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "F" AND F01STC = "4") AS FEQLD,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "M" AND F01STC = "5") AS MNSA,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "F" AND F01STC = "5") AS FESA,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "M" AND F01STC = "6") AS MNWA,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "F" AND F01STC = "6") AS FEWA,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "M" AND F01STC = "7") AS MNTAS,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "F" AND F01STC = "7") AS FETAS
FROM [TEST-Q-END-VBA];
Click to expand...
Hi John,

One possible solution might be to create a translation table (say
"tblTrans")

ID ColName fldMF F01STC
1 MNNSW M 2
2 FENSW F 2
3 MNVIC M 3
4 FEVIC F 3
5 MNQLD M 4
6 FEQLD F 4
7 MNSA M 5
8 FESA F 5
9 MNWA M 6
10 FEWA F 6
11 MNTAS M 7
12 FETAS F 7

Then join to your table (or query?) in a crosstab

TRANSFORM NZ(Count(*),0) AS cnt
SELECT
tblAfAct.WORK,
Count(*) AS Total
FROM
tblTrans
INNER JOIN
tblAfAct
ON
(tblTrans.F01STC = tblAfAct.F01STC)
AND
(tblTrans.fldMF = tblAfAct.fldMF)
GROUP BY
tblAfAct.WORK
PIVOT tblTrans.ColName
IN ("MNNSW","FENSW","MNVIC","FEVIC","MNQLD",
"FEQLD","MNSA","FESA","MNWA","FEWA","MNTAS","FETAS");

the "bottom row totals" would be handled simply in your
report (or subreport) -- thus the reason for setting your
column headings in PIVOT clause so report will work
with same field values...

Good luck,

gary
 
J

John Melbourne

Thanks Gary,
It took me a little time to work it out. But it works just
fine. I only had trouble figuring out that I didn't need my code as well.
--
John Melbourne


Gary Walter said:
John Melbourne said:
I'm trying to add a dimension to my count but when I add a group by I get
the
additional rows but the counts are all the same within the columns. My
table
contains the name, sex, state, workplace and Id of employees. I currently
have the following code that gives me one dimension, I want to add by
workplace.

ID NAME M/F STATE WORK
0001 TED M 2 OFFICE
0002 PAT F 3 OFFICE
0003 JOHN M 3 WORKSHOP
0004 MARY F 2 FIELD
0005 CLARE F 3 OFFICE
0006 JIM M 2 FIELD

Current count looks like

MNNSW FENSW MNVIC FEVIC
2 1 1 2

Would like to

MNNSW FENSW MNVIC FEVIC TOTAL
FIELD 1 1 2
OFFICE 1 2 3
WORKSHOP 1 1
TOTAL 2 1 1 2 6

My current code is

SELECT Count([tblAfAct].fldID) AS [FE-MALE COUNT],
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "M" AND F01STC = "2") AS MNNSW,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "F" AND F01STC = "2") AS FENSW,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "M" AND F01STC = "3") AS MNVIC,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "F" AND F01STC = "3") AS FEVIC,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "M" AND F01STC = "4") AS MNQLD,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "F" AND F01STC = "4") AS FEQLD,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "M" AND F01STC = "5") AS MNSA,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "F" AND F01STC = "5") AS FESA,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "M" AND F01STC = "6") AS MNWA,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "F" AND F01STC = "6") AS FEWA,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "M" AND F01STC = "7") AS MNTAS,
(SELECT COUNT([tblAfAct].fldMF)
FROM [tblAfAct] WHERE fldMF = "F" AND F01STC = "7") AS FETAS
FROM [TEST-Q-END-VBA];
Click to expand...
Hi John,

One possible solution might be to create a translation table (say
"tblTrans")

ID ColName fldMF F01STC
1 MNNSW M 2
2 FENSW F 2
3 MNVIC M 3
4 FEVIC F 3
5 MNQLD M 4
6 FEQLD F 4
7 MNSA M 5
8 FESA F 5
9 MNWA M 6
10 FEWA F 6
11 MNTAS M 7
12 FETAS F 7

Then join to your table (or query?) in a crosstab

TRANSFORM NZ(Count(*),0) AS cnt
SELECT
tblAfAct.WORK,
Count(*) AS Total
FROM
tblTrans
INNER JOIN
tblAfAct
ON
(tblTrans.F01STC = tblAfAct.F01STC)
AND
(tblTrans.fldMF = tblAfAct.fldMF)
GROUP BY
tblAfAct.WORK
PIVOT tblTrans.ColName
IN ("MNNSW","FENSW","MNVIC","FEVIC","MNQLD",
"FEQLD","MNSA","FESA","MNWA","FEWA","MNTAS","FETAS");

the "bottom row totals" would be handled simply in your
report (or subreport) -- thus the reason for setting your
column headings in PIVOT clause so report will work
with same field values...

Good luck,

gary
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

Combining Queries 4
Combine Crosstab Queries? 2
Counting records matching criteria 4
Count unique in a query instead of count # 5
rank in select query not right 1
Help with sql which counts records 9
multiple grouped counts in one query 8
Help with Duplicate Output Alias Error 'Exp1' 0

Top