Update query

[msnews.microsoft.com]

Using Access 2000:

Trying to use an update query to update a field. I would like it to put one
name in record 1, a different name in record 2, and then a different name in
record three. Then in record 4 I would like it to start the three name
process again. For example:

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

....and so forth through the 6000+ records.


Pete Provencher

 

[Andrew L.]

Pete

A lot depends on the whether you have your records sorted in a particular order. You need an
autonumber field in your table, which reflects the correct sequence of records.

Backup your data. In the query design panel, substitute the name of your autonumber field for
[recnumber].

Field: Agent
Update to:
IIf(Int([recnumber]/3)=[recnumber]/3,"Hall",IIf(Int(([recnumber]+1)/3)=([recnumber]+1)/3,"Jones","Sm
ith"))

hth
Andrew L.

 

[John Spencer (MVP)]

Is Record a number in a field? If so you can use that fact to do every record
divisible by 3 with a remainder of 0, 1, or 2.

UPDate YourTable
Set Agent = Switch(([Record] Mod 3)+1,"Smith","Jones","Hall")

If you want to only update records where Agent is blank then add
WHERE Agent is Null
to the end of the query