At 04:35 PM 26/02/2008, you wrote:

>My apologies to all who have been kind enough to answer my questions.
>
>I should have pointed out that I was aware that there were syntax
>errors, and that I would need to fix them. Your answers have been
>helpful in reminding me how variables and IF statements should be structured.
>
>What i am more interested in, however, is how to solve the following problem :
>
>"I need to get a field value dependant on the maximum of another field value.
>
>For example in the following case, I want the BATCH NUMBER value for
>the maximum RECORDNO value."
>
>If I try "SELECT MAX(RECORDNO), BATCHNO FROM BATCH then I have to use
>the GROUP BY clause. The problem with that is that I get a result set
>with multiple rows.
>
>What I would like is a singleton result set, which for the maximum
>RECORDNO will give me the corresponding BATCHNO.
>
>Any ideas?

Well, to get this answer you need to tell us more about RECORDNO and BATCHNO, viz.

1) Is either of them unique in the table? i.e., PK or a unique key?

2) If so, which one is the PK?

3) If not, do you have another column that is?

./heLen