Calculate a new value conditionally based on 2 records

[Liz A]

I am trying to write an expression that calculates a new field in a query. The new field is simply recoding values from another field (survey question responses) using IIf statements. This new field, let's call it RecodedResponse, will eventually be summed in a totals query to get the total score on that survey for each participant. Each record is a question response and there are multiple records for each participant (16 items on this particular survey). The problem is that for 2 of the questions on this 16 item survey, the responses are supposed to be eventually combined and recoded based on each other. So, my question is:

Is there a way to do a conditional expression where the result from the previous record is used? Or do I need to first do a crosstab query so that all the responses for one participant are on one line?

I am pretty sure I can do a workaround to make it work, probably doing a crosstab, then recoding in another query based on the crosstab, but it would be cleaner (I think) to do this some other way. Any ideas? Or is this too vague?

Thanks,
Liz

 

[namliam]

Iif ??? Geez thats gonna be a lot of hassle isnt it? Why not make a function in VBA? Much simpler...

I think the Crosstab is the way to go anyway...

 

[Liz A]

Thanks for your response namliam. I am a bit of a novice when it comes to VBA (am trying to learn, but admittedly on a bit of an ad hoc basis and so far, mainly by looking at the code generated by wizards). Can I include a VBA function within the query and how would I go about that? I don't really quite get how a VBA function would operate differently than the IIf expression within the query.

Thanks again,
Liz

 

[namliam]

Liz,

VBA operates mostly like the IIF.

However the IIF is for that one query only, offcourse you can copy it into a new one. But suppose you need to change it.... You have to change it everywhere.
The VBA function you can call in every query and if you have to change it, no hassle. Because there is only 1.

Furthermore IIf's can be verry hard to read and cannot contain any helpfully explination (spelling?). Where as VBA can be writen verry cleanly and can contain comments which can explain the reason for this If or this Function.

IIF, I think, is a little faster in running the query. But maintanance is hell.

On your original question, I think it would be much easier if you start of on a crosstab query (not as a workaround, but just as the basic). You are looking for a score per participant, the crosstab makes the logical (participant) record for you. And this way you can add and substract easy like from withing one record...

Greetz