At Your Survey: Calculate field in cros-tab queries (followup)

[ButBi]

Thank you for your reply.

I.e. I have questions such as: Population served (qst1), No of town
served (qst2).
Now I need to display also i.e. Average size of town = qst1/qst2

Population served | No of towns | Average size
=qst1/qst2

Respondent1 1000000 | 5 | 200000
Respondent2 800000 | 2 | 400000

....

I know there is functions for Sum, Average etc. but I want to know how
to use a calculated values in cros-tab queries in general.

One more question about validation. If some answers missing how can I
display the related calculated fields "NA" instead of 0 or get error
(if No of town missing).

Thank you.

PS: I got the error "Unable to retrieve message
(e-mail address removed)" to post the reply in thread so
I have to post as new message.

 

[Duane Hookom]

A crosstab query should be able to create columns for qst1 and qst2 while
converting them from text to numbers and replacing nulls with 0 using
Nz([field], 0). Then take the results from the crosstab and do the math.

 

[butbi]

Thank you for your promt reply.
But sorry, I'm newbie. Can you explain more in detail how to do that
in query qxtbRspnsIDxQstsText with some calculate fields? Or can you
provide a sample SQL code?

 

[butbi]

Thank you for yor promt reply.
But as I said I'm a newbie. Can you explain a little more detailed or
show me the SQL code i.e. for the query qxtbRspnsIDxQstnText with a
sample calculated value.
Thank you.

 

[Duane Hookom]

Checkout crosstabs where you question number is the column heading. Limit
the query to the two question you desire. Use First of the response as the
value.

 

[Duane Hookom]

Assuming I want to multiply the response from QstnID values of 1 and 6.
First create a crosstab (qxtbRespID_1_6) that returns these two Questions:

PARAMETERS [Forms]!fmnuReportSelect![cboSrvID] Long;
TRANSFORM First(tblResponses.Rspns) AS FirstOfRspns
SELECT tblSrvRspns.RspnsID
FROM tblSrvRspns INNER JOIN tblResponses ON tblSrvRspns.RspnsID =
tblResponses.RspnsID
WHERE (((tblSrvRspns.SrvID)=[Forms]![fmnuReportSelect]![cboSrvID]))
GROUP BY tblSrvRspns.RspnsID
PIVOT tblResponses.QstnID In (1,6);

Then create a query that multiplies the columns:
SELECT qxtbRespID_1_6.RspnsID, qxtbRespID_1_6.[1],
qxtbRespID_1_6.[6], Val([1])*Val([6]) AS OneTimesSix
FROM qxtbRespID_1_6;