Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

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

  • 26-06-2013 2:44pm
    #1
    Registered Users, Registered Users 2 Posts: 14,163 ✭✭✭✭


    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.
    Tagged:


Comments

  • Registered Users, Registered Users 2 Posts: 419 ✭✭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


  • Registered Users, Registered Users 2 Posts: 14,163 ✭✭✭✭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


  • Registered Users, Registered Users 2 Posts: 419 ✭✭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?


  • Registered Users, Registered Users 2 Posts: 14,163 ✭✭✭✭danniemcq


    hmmmm that does actually make things a bit easier!

    spoilt for choice now!


  • Registered Users Posts: 904 ✭✭✭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


  • Advertisement
Advertisement