Query update

  • Thread starter Thread starter Pete Provencher
  • Start date 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
 
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?
 
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.
 
Back
Top