How do I denote rows in a recordset?!

Hi guys,

I hope I can make this problem clear enough - Im afraid I dont really have any specific code (yet) but im looking more for "concepts"...

I have a database (in Access) that contains a table, and this table contains lots of records. Each record can be either Case1, Case2 or Case3. Every hour, I wish for the table to be "refreshed" - i.e. a script will alter all data in the database depending on whether the record is Case1, Case2 or Case3. I have no difficulty in doing the code for each Case, but my problem lies in what method to use. I can think of two ways of doing this:

1 - To just select every record in the table (i.e. all Cases), take the first record in the recordset (of the entire table), look at its case, then depending on result run either ScriptCase1, ScriptCase2, ScriptCase3, run the script, move to the next one, look at whether its case 1, 2, or 3....run the appropriate script, move to the next and so on...........or

2 - To select all the entries in the table and put them into a recordset that are one Case - eg select everything that is case A. Run the script for Case A on the whole record set, then select all record entries that are Case B, run a script that updates this set, and so on......

To begin with, I cant decide which technique would be better, and also, for the second case I am not sure how to update a row within a record.

If i Have a table with rows such as:

AutoNumber Type Otherdata
1 Case1 blah blah
2 Case2 etc etc
3 Case1 yada yada

If i use the second method to put rows 1 and 3 into a record set, in my update script there will be a line that says something like:

...............("UPDATE otherdata SET something=something WHERE ****************">

The ********** section is the bit i cannot solve......i cant create a variable called "Autonumber" ( WHERE Autonumber="1" for example) and add 1 to it each time I move to the next record because in this particular case there is no "Autonumber="2") because it is Case2.

I hope this makes some sense - ive managed to confuse myself talking about it... If im a lost cause then just ignore me

If anyone could point me in the right direction or spot a suitable way to do this I would be extreeeemely grateful,

Thanks once again guys,

Porky

Use your option 2 imho.

I thought that would be better also - but as i say, i cant figure out how to finish the line with the "UPDATE" command on it. If i dont include the "WHERE" part of the line, the script will not do what i want it to, but i dont know how to tell the different records apart in the recordset