Updating the next n number of records

[brendandaly]

I really just can not figure this out and it seems like it should be so simple. I've got a list of records that are to be assigned to users for completion. I'm trying to make an update query that uses a (list? combo?)box from a form to select a user and a text box to input a number of records to assign to the user. So essentially I want the query to assign N incomplete records to X user, and I just can not for the life of me figure this out. Thank you SO much for any help on this in advance.

 

[Anchoress]

So it doesn't matter who gets what records? You just have a 'staff' field or something, and you want it to update X records to "Julie", and X records to "Matt", etc?

I'm not an Access programmer, but I'd do it one name at a time using a range of numbers in your ID field (assuming you have one, which you should!).

 

[brendandaly]

I do have an ID field. And it doesn't really matter who gets what records as they;re all the same thing. For example, I'll be told to assign 50 to Jim and 30 to Bob. In the past I was just using a query where i just took whatever number was completed up to, added how many i was to assign and updated the "user" field. I'm trying to figure out a way to do this programmatically without having to go into the tables it self. Theres also a possibility that they wont be completed sequentially so if i could get a way to have the query assign the user to the next N number of unassigned records...

I've been playing around with the count() function but i just can not get it to work. I would LOVE if i were able to just do this in a macro and not in VB.

 

[Beetle]

You might not need a macro or vb, maybe just an update query, but we need more info.

What table are you trying to update?

Is the value of n coming from another table or just an unbound form control?

Are you just trying to set a fields value to n, or are you trying to add n new records to a table?

 

[brendandaly]

Theres a main table of records for completion, on which one of the columns is 'User.' We assign the records out using that field and a simple selection query. The value of N, being the number of records to assign, is totally arbitrary. A dialog box asking "how many records would you like to assign?" would be fine, put if I could put it onto an admin form I've made, that would just be a bit smoother. Thanks guys. I know you're gunna figure this out and the solution is going to be so simple I'm only going to feel more noob than i already do.

 

[gemma-the-husky]

you could probably do a query to select TOP however many unselected items. you would need some code, as access only offers a certain number of presets.

if you want to avoid writing SQL, have a number of pre-built queries for TOP10, TOP20 etc.


then just run an update query on those to allocate them to a given agent.