Query update

  • Thread starter Pete Provencher
  • Start date
P

Pete Provencher

Using Access 2000:

Trying to write an update query that will put a name in Agent field in
record 1, a different name in field 2, and another name in record 3. Then I
want it to start the process over with the same t\hree name in the same
order.

Example

Record Agent
1 Jones
2 Smith
3 Brown
4 Jones
5 Smith
6 Brown

....and so on through 5000 records.


Pete Provencher
 
K

Ken Snell

How is "record" number defined? There is no order in a table unless you have
a field that holds a "record number", or unless you use a query to impose
some type of order on the table and then use a calculated field to establish
a "ranking" (essentially, a record number concept) for each record.

Can you provide more information about what your entire table structure is
and how you're defining record number sequence?
 
K

Ken Snell

Something like this might work for you...be sure to make a backup copy of
the data before you try any update or other "action" queries:

UPDATE TableName
SET TableName.Agent =
Choose((SELECT Count(*) FROM TableName AS T
WHERE T.AutonumberField <= TableName.AutonumberField
ORDER BY T.AutonumberField) Mod 3) + 1", "Brown", "Jones",
"Smith));

Substitute the real table name for TableName and the real name of the
autonumber field for AutonumberField in the above query SQL statement.
 
Top