[access 2003] splitting a table into X parts to assign records to people

danniemcq

What I have is a table of data and 60% of the stuff on there I can assign to people to work by matching criteria. This part I got it’s the next part that gives me the issues

It leaves me with is a big pile of misc. say 400 records and to make it easy and I’m dividing it up 4 ways so John, Robert, Jimmy and JPJ will each be assigned 100 records.

What would be the best way to get their names in added to the table so the entire assigned to table would be filled.

---

Extra details if required-

I import a spreadsheet

Looks for criteria and compares that with a table to get assign certain records to people.

Leaves a number of nulls behind, I need these assigned (for example) first 25% to Bart, next 25% to Lisa, next 25% to Homer and last 25% to Maggie.

This will then get more data added before unique values only are added to the t_master database on our SharePoint where they will have their own views so only see their stuff.

So far I have been messing around but get stuck at the 25%,I can create a table with 25% of the “is nulls” from the import table but cannot modify 25% without moving them to temp table but then I run into the issue where its always only 25% of the total of "is nulls" so will never reach 0.

And to make things extra awkward there will be a different amount of records each day as new spreadsheets are added so its not always going to be and even 400 records needing updating.

Mort5000

Are you trying to get this data updated from an external application?

Are the current 60% being assigned programmatically or manually?

How about adding a sequential (auto)number to the row. After your 60% is complete, get a min and max of the sequential number on the null rows, divide by 4 and update with sql using the sequential number for your range?
update blahdeblah set user = maggie where seq between 150 and 200
update blahdeblah set user = bart where seq between 200 and 250 etc

danniemcq

Mort5000 wrote: »

Are you trying to get this data updated from an external application?

Are the current 60% being assigned programmatically or manually?

How about adding a sequential (auto)number to the row. After your 60% is complete, get a min and max of the sequential number on the null rows, divide by 4 and update with sql using the sequential number for your range?
update blahdeblah set user = maggie where seq between 150 and 200
update blahdeblah set user = bart where seq between 200 and 250 etc

hah I do belive thats the process I started this morning.

Split original table in 2, assigned and non assigned

autonumber unassigned

count number of nulls and divide by 4.

queries then to update table to each of the peoples names if under x then person A, if over x but under 2x then person B etc

Then combine them with the assigned table in a nice big table.

The only issue i have found so far is the autonumbers continuing and not starting afresh again but a repair and compact of the database seems to sort that issue out.

Thanks for the feedback though had myself banging my head off the keyboard yesterday

Mort5000

danniemcq wrote: »

The only issue i have found so far is the autonumbers continuing and not starting afresh again but a repair and compact of the database seems to sort that issue out.

It is ok if the autonumber continues, as you're just dividing the result into quarters.
So each day will be
(max number)-(min number) for the range
range/4 for the rangesize.
first person is between (min) and (min)+rangesize.
second person is between (min) and (min)+rangesize*2 updating only nulls
third person is between (min) and (min)+rangesize*3 updating only nulls
Would that work for you?

danniemcq

hmmmm that does actually make things a bit easier!

spoilt for choice now!

realgolfgeek

could you not just do something like.

update top (100) testingtbl set mydesc = 'Homer'
where mydesc is null


update top (100) testingtbl set mydesc = 'Maggie'
where mydesc is null


etc etc