Merge Records

[malewine]

I have a table that has 6 fields: Audit ID, Question #, answer, Corrected ?, Comments.

Each Audit ID has a set of question numbers, 10, 20, 30, ... 225.

Each question has an answer: 1, 2, 3, and 4. (1 and 2 are both incorrect but to a different degree) 3 is correct 4 is basically NA

The field "Corrected ?" is just a yes/no field which only those with answers 1 and 2 would have answered yes.

My problem is that they changed the questions by basically combining some of them. so if the old questions 20 and 30 were now the same question, I want to just renumber the new question 20. If I do that, how can I write a query that for each audit ID would go through and merge both question 20 into one record, taking the MIN of the answer, and take the Corrected ? from that record and combine the comments?

Thanks for any help you can provide.

Mark

 

[Micron]

Not sure I follow completely. So you don't want to concatenate the questions, just renumber the result. What I don't get is why say merge 20 and 30 when there is also a 10. Also, you say both question 20 into one record. So where did the other question 20 come from, and what happened to 30?
I doubt you are saying it's always 20 and 30 since you suggest maybe using the Min function (which I don't see working based on what you've posted). If you are saying it's question n and the question immediately before it, then I'd suggest a sub query in your main query. You would want to select the Max question number that is less than the one after it, but you'd need criteria to get the higher question number.
It sounds like this db has been around for a while so it's too late to do anything about it I imagine, but sorry to say, the table field names are just plain bad.