I don't think this is easy!!!

B

Bill

Hi All,

I have a crosstab query that produces values. The Column Headings are
months, the Row Headings are organised such that the intersection values are
organised in a series of pairs representing a sum of, Yes and No answers to
survey questions.

Trouble is what I really need are the percentage figures which needs each
value to be divided by the sum of the pairs. I have exported the data to
Excel but it is not straightforward in that either!! (That are many survey
questions!!)

Can anyone give advice on the best way to go about this please. My
preference would be for the answer to be entirely calculated within Access.

Regards.
Bill.
 
J

John Spencer

Are the responses always either yes or no or can their be other values?

Also, I would suggest that you post the SQL of your crosstab query.
HINT: In design view, select View: SQL from the menu.

Another aid to help us help you is to post a line or two of sample data.
 
B

Bill

The intersections in the cross tab are values. Down the side there will be a
column containing the question, in the next column Yes, and underneath it
No. The intersections there fore the numbers of Yes's and No's for each much
month.

I will post the SQL tomorrow - its on another PC - and some data.

(I tried to simplify the question a bit by using the yes/no scenario - in
fact it isn't quite like that but I will explain when I post)

Thanks for replying.

Regards.
Bill.
 
B

Bill

As promises.

SQL:-
TRANSFORM Count([q_fixtimes by assignee team - part2].CallID) AS
CountOfCallID
SELECT [q_fixtimes by assignee team - part2].Team, [q_fixtimes by assignee
team - part2].Priority, [q_fixtimes by assignee team - part2].FixStatus,
Count([q_fixtimes by assignee team - part2].CallID) AS [Total Of CallID]
FROM [q_fixtimes by assignee team - part2]
GROUP BY [q_fixtimes by assignee team - part2].Team, [q_fixtimes by assignee
team - part2].Priority, [q_fixtimes by assignee team - part2].FixStatus
PIVOT [q_fixtimes by assignee team - part2].CallMonth In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


Cut down version of the data:-

q_fixtimes by assignee team - part2_Crosstab Team Priority FixStatus Total
Of CallID Jan Feb Mar Apr
System Support 1C FixTime Exceeded 40 3 2 4 3
System Support 1C FixTime Met 178 20 12 12 16
System Support 2U FixTime Exceeded 121 10 11 13 8
System Support 2U FixTime Met 153 16 33 11 9
System Support 3M FixTime Exceeded 215 35 16 11 15
System Support 3M FixTime Met 343 64 40 32 27
System Support 3M WIP Still FixTime 3




System Support 4L FixTime Exceeded 14 3 3 1 1
System Support 4L FixTime Met 34 5 4 6 2


The rows of data are nearly always in pairs but occasionally there will be a
third row as in the example.

I think it will also be possible for there just to be one row of data
occasionally.

What I require is that instead of the values I return percentages. So in the
case of the 1C priorities above for the month of Jan the calculation I need
is.

40/(40+178)
and
178/(40+178)

I have tried the calculation in Excel and though it is possible (and using
the OFFSET function makes things easier) the fact that the dataset that I
need to base the calculation on can be made up from 1, 2 or 3 rows still
makes it a lengthy process. I would prefer to tackle it all within Access if
I could.

I did say I didn't think this would be easy!!

Regards.
Bill
 
G

Gary Walter

Bill said:
As promises.

SQL:-
TRANSFORM Count([q_fixtimes by assignee team - part2].CallID) AS
CountOfCallID
SELECT [q_fixtimes by assignee team - part2].Team, [q_fixtimes by assignee
team - part2].Priority, [q_fixtimes by assignee team - part2].FixStatus,
Count([q_fixtimes by assignee team - part2].CallID) AS [Total Of CallID]
FROM [q_fixtimes by assignee team - part2]
GROUP BY [q_fixtimes by assignee team - part2].Team, [q_fixtimes by
assignee team - part2].Priority, [q_fixtimes by assignee team -
part2].FixStatus
PIVOT [q_fixtimes by assignee team - part2].CallMonth In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


Cut down version of the data:-

q_fixtimes by assignee team - part2_Crosstab Team Priority FixStatus
Total Of CallID Jan Feb Mar Apr
System Support 1C FixTime Exceeded 40 3 2 4 3
System Support 1C FixTime Met 178 20 12 12 16
System Support 2U FixTime Exceeded 121 10 11 13 8
System Support 2U FixTime Met 153 16 33 11 9
System Support 3M FixTime Exceeded 215 35 16 11 15
System Support 3M FixTime Met 343 64 40 32 27
System Support 3M WIP Still FixTime 3
System Support 4L FixTime Exceeded 14 3 3 1 1
System Support 4L FixTime Met 34 5 4 6 2


The rows of data are nearly always in pairs but occasionally there will be
a third row as in the example.

I think it will also be possible for there just to be one row of data
occasionally.

What I require is that instead of the values I return percentages. So in
the case of the 1C priorities above for the month of Jan the calculation I
need is.
Click to expand...

It looks here like you are using "TotalOf CallID"
instead of counts for Jan? Did you mean?

3/3 + 20
and
20/3 + 20
40/(40+178)
and
178/(40+178)
Click to expand...
I wonder if it wouldn't be more efficient "relationally"
to roll your own crosstabs for a Team/Priority/FixStatus
group and a Team/Priority group, then join those 2 queries...

{I'm not going change negative counts w/Abs() function
because I am lazy typist, but hopefully you know...}

qryTPF

SELECT
q.Team,
q.Priority,
q.FixStatus,
Count(q.CallID) AS TPFTotCnt,
Sum(q.CallMonth="Jan") AS JanTPF,
Sum(q.CallMonth="Feb") As FebTPF,
Sum(q.CallMonth="Mar") As MarTPF,
Sum(q.CallMonth="Apr") As AprTPF,
Sum(q.CallMonth="May") As MayTPF,

{etc.}

FROM
[q_fixtimes by assignee team - part2] As q
GROUP BY
q.Team,
q.Priority,
q.FixStatus;

qryTP

SELECT
q.Team,
q.Priority,
Count(q.CallID) AS TPTotCnt,
Sum(q.CallMonth="Jan") AS JanTP,
Sum(q.CallMonth="Feb") As FebTP,
Sum(q.CallMonth="Mar") As MarTP,
Sum(q.CallMonth="Apr") As AprTP,
Sum(q.CallMonth="May") As MayTP,

{etc.}

FROM
[q_fixtimes by assignee team - part2] As q
GROUP BY
q.Team,
q.Priority;

I may be completely wrong but I imagine joining
these 2 queries on Team and Priority, then performing
your calculations.
 
B

Bill

Gary Walter said:
Bill said:
As promises.

SQL:-
TRANSFORM Count([q_fixtimes by assignee team - part2].CallID) AS
CountOfCallID
SELECT [q_fixtimes by assignee team - part2].Team, [q_fixtimes by
assignee team - part2].Priority, [q_fixtimes by assignee team -
part2].FixStatus, Count([q_fixtimes by assignee team - part2].CallID) AS
[Total Of CallID]
FROM [q_fixtimes by assignee team - part2]
GROUP BY [q_fixtimes by assignee team - part2].Team, [q_fixtimes by
assignee team - part2].Priority, [q_fixtimes by assignee team -
part2].FixStatus
PIVOT [q_fixtimes by assignee team - part2].CallMonth In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


Cut down version of the data:-

q_fixtimes by assignee team - part2_Crosstab Team Priority FixStatus
Total Of CallID Jan Feb Mar Apr
System Support 1C FixTime Exceeded 40 3 2 4 3
System Support 1C FixTime Met 178 20 12 12 16
System Support 2U FixTime Exceeded 121 10 11 13 8
System Support 2U FixTime Met 153 16 33 11 9
System Support 3M FixTime Exceeded 215 35 16 11 15
System Support 3M FixTime Met 343 64 40 32 27
System Support 3M WIP Still FixTime 3
System Support 4L FixTime Exceeded 14 3 3 1 1
System Support 4L FixTime Met 34 5 4 6 2


The rows of data are nearly always in pairs but occasionally there will
be a third row as in the example.

I think it will also be possible for there just to be one row of data
occasionally.

What I require is that instead of the values I return percentages. So in
the case of the 1C priorities above for the month of Jan the calculation
I need is.
Click to expand...

It looks here like you are using "TotalOf CallID"
instead of counts for Jan? Did you mean?

3/3 + 20
and
20/3 + 20
40/(40+178)
and
178/(40+178)
Click to expand...
I wonder if it wouldn't be more efficient "relationally"
to roll your own crosstabs for a Team/Priority/FixStatus
group and a Team/Priority group, then join those 2 queries...

{I'm not going change negative counts w/Abs() function
because I am lazy typist, but hopefully you know...}

qryTPF

SELECT
q.Team,
q.Priority,
q.FixStatus,
Count(q.CallID) AS TPFTotCnt,
Sum(q.CallMonth="Jan") AS JanTPF,
Sum(q.CallMonth="Feb") As FebTPF,
Sum(q.CallMonth="Mar") As MarTPF,
Sum(q.CallMonth="Apr") As AprTPF,
Sum(q.CallMonth="May") As MayTPF,

{etc.}

FROM
[q_fixtimes by assignee team - part2] As q
GROUP BY
q.Team,
q.Priority,
q.FixStatus;

qryTP

SELECT
q.Team,
q.Priority,
Count(q.CallID) AS TPTotCnt,
Sum(q.CallMonth="Jan") AS JanTP,
Sum(q.CallMonth="Feb") As FebTP,
Sum(q.CallMonth="Mar") As MarTP,
Sum(q.CallMonth="Apr") As AprTP,
Sum(q.CallMonth="May") As MayTP,

{etc.}

FROM
[q_fixtimes by assignee team - part2] As q
GROUP BY
q.Team,
q.Priority;

I may be completely wrong but I imagine joining
these 2 queries on Team and Priority, then performing
your calculations.
Click to expand...
Thanks you, there may be a glimmer of something here!!!
Bill.
 

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

Working with question-answer pairs 0
Counting survey responses, 4 queries or 1 query?? 1
Complex query problem 2
Count only text fields containing data 5
CountIF on two criteria 2
Compare ranges - compute agreement % 4
Calculate in a Crosstab? 4
Pivot Table and Crosstab Query Limitions 4

Top