Hi,
My team and I are working on researching some of the accounts in our database. Our database contains 15,000 accounts and we're recording our research results into an updatable query containing a subset of about 10,000 of them.
I want to add a field to the table and populate it with which team member is assigned to each account. Basically, accounts 1-3000 in the query will be assigned to Person A, 3000-6000 will go to Person B, ect...
Normally, I would use an update query to populate the names, but the only criterion I'm using for assignment is the row number in the query. I can't use the index field from the table, because the query only uses a subset of the total accounts (i.e., records 1-3000 on the table only covers rows 1-2500 on the query). And anyhow, the Primary Key in the table is the account number which isn't sequential. Is there a way to create a seperate auto-incrementing key for just the records in the query?
Does anyone have any idea how I might create this field? Someone showed me a trick where you can copy the whole query into excel, make your changes, then paste it back into Access, but when I tried that, Access gave me an error saying it couldn't lock so many records (it's >10,000). I'm sure I can jury rig something that will get the job done (an update query based on arbitrary criteria), but it will be slow and tedious, so I was curious to know if there's a "right" way to do this?
Thanks,
Pinny
My team and I are working on researching some of the accounts in our database. Our database contains 15,000 accounts and we're recording our research results into an updatable query containing a subset of about 10,000 of them.
I want to add a field to the table and populate it with which team member is assigned to each account. Basically, accounts 1-3000 in the query will be assigned to Person A, 3000-6000 will go to Person B, ect...
Normally, I would use an update query to populate the names, but the only criterion I'm using for assignment is the row number in the query. I can't use the index field from the table, because the query only uses a subset of the total accounts (i.e., records 1-3000 on the table only covers rows 1-2500 on the query). And anyhow, the Primary Key in the table is the account number which isn't sequential. Is there a way to create a seperate auto-incrementing key for just the records in the query?
Does anyone have any idea how I might create this field? Someone showed me a trick where you can copy the whole query into excel, make your changes, then paste it back into Access, but when I tried that, Access gave me an error saying it couldn't lock so many records (it's >10,000). I'm sure I can jury rig something that will get the job done (an update query based on arbitrary criteria), but it will be slow and tedious, so I was curious to know if there's a "right" way to do this?
Thanks,
Pinny
