Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2013
    Posts
    5

    Unanswered: Divide number of records in equal parts

    Hello,

    I have a table with a list of records (names) that I would like to divide equally by a number of users. The number of users can change from time to time.
    Data in the table is imported via a text file and I need to sort and divide the records based on the Last Name field which is a text field. Division is required to set up a workload distribution list.

    I have already creted a form with texboxes which calculate the total number of records in my table and how many records each user should be assigned (total number/number of users = Number of records each user is assigned).
    My problem is that once I get the number of records each user should work. I probably need to create a query or write up a code that will show the first and last records (the last name) which I need to assign.

    Examples:

    Total Number of records: 6
    User: 3
    Records assigned to each user: 2

    Records in table by last Name

    BLACK
    BLUE
    GREEN
    PINK
    RED
    WHITE

    User 1 should be assigned 2 records, I need a query that returnes returns 2 records, and diplays BLACK and BLU ( I would like to show these in a textbox on a form)
    User 2 should be assigned the next two records GREEN and PINK
    User 3 should be assigned the next two recors RED and WHITE

    My table does not have an autonumber as field. Honestly I do not know how to preceed.

    I was thinking of creating some sort of query but just do not know how to proceede. I you wish I can attaching an example file it this can help.
    Thank you for your help.
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you need
    find a way of associating a user with a client. this can be done in at least two ways. one is to store the user allocated to a client in table clients. the other is to use an intersection table. if you need to knwo the history of which users ahve been assigned to which clients then you'd need a intersection table as storing the allocateduser in the client table means you only know the last assigned user. if you go down the intersection table then you'd need an additional piece of information to make the row unique (adding the date the user was allocated woudl be fine for that purpose as it uniquely identifies a specific user / client assignment from a specific date

    to know the number of users and the number of names
    ...use select count(mycolumname) or use the domain function dcount to do that

    then work out the number of names to allocate per user

    then get a list of clients and a list of users (for lsit read a recordset)
    then iterate through the recordset of clients
    then allocate a user to a client, until the number per user is reached, move to the next user

    you can easily allocate users to clients by a single update statement
    execute the sql using the docmd.runsql macro
    using the 'in syntax' to update a block of clients at he same time eg:-
    strSQL = update clients set AllocatedUser = " & currentuser & " where Client in ('Black', 'Green', 'Blue')
    docmd.runsql (strSQL)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2013
    Posts
    5

    Divide number of record in equal parts

    Thank you for reply.

    As shown on my attachment, I have been able to determine the number of users, clients and determine the number of names to be assigned to each user. I have done this on the form in the attachment.

    What is confusing me is how to allocate a user to a client as my list of clients can go up to 50.000 records. Each user should be assigned a given number of records. Are you referring to an update query? This means that I should add a new column in the table? Unfortunately I am not very good with this and would appreciate further help or giving a look at my saple db. thank you

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I dunno
    its your systejm
    its your requirement

    how do you think you can allocate a client with a user without storing said allocation somewhere, unless this is purely at the moment in time and you just want to match up on a screen

    yes if you want to store data in a db then you have to either use an INSERT fro new rows or an UPDATE to change rows

    the where clause identifies rows to be processed, hence suggesting using the 'in' syntax so you update a set of clients in the same update stateemnt, rather than doing a single update per client

    try following the steps in post #2 and see where you come.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2013
    Posts
    5

    Divide number of records in equal parts

    Thanks for your patience. I will follow your advice (upddate queries). Meantime I have found an interesting sample with a code on a cmdbutton that returns the top records according to the value entered in a text box. This is another important step I was trying to resolve. One this though I cannot figure is how to let the query sort the recors in ascending way. I have tried to add it but as you can see the query each time is deleted and recreated. Is there a way I can add the Order By ACS to the code below? Thanks

    Dim qdfNew As DAO.QueryDef
    Dim sqltext As String

    sqltext = "SELECT TOP " & Me.Text0 & " Clients.BLN FROM Clients;"

    On Error Resume Next
    With CurrentDb
    .QueryDefs.Delete ("My_Query")
    Set qdfNew = .CreateQueryDef("My_Query", sqltext)
    .Close
    End With
    DoCmd.OpenQuery ("My_Query")

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sqltext = "SELECT TOP " & Me.Text0 & " Clients.BLN FROM Clients ORDER BY ACS;"
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2013
    Posts
    5

    Divide number of records in equal parts

    Thanks but it does not work. I did try it but the cbmbutton does not return any records. If I remove it, it does....very strange.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    does not work.. meaning?, what error message diod you get
    where or what is ACS, is it in table clients?
    bearing in mind ACS wasn't defined in your table clients as posted earlier.
    I'd strongly recommend that you develop some form of naming convention. you shoudl give appropriate understandable and fully descriptive names for tables & columns.... so that you and others can understand what each item refers to. ferisntance I don't know what ACS or BLN mean. now it may well be that every person in your organisation immediately understands ACS,BLN and any other acronym but even so its asking for trouble over time

    what value do you have for Text0, and for that matter what value do you have for sqltext
    put a break point on the code and examine those variables
    failing that put a msg box that displays the values

    Dim sqltext As String
    msgbox ("text0 is:" & text0)
    sqltext = "SELECT TOP " & Me.Text0 & " Clients.BLN FROM Clients;"
    msgbox ("sqltext is:" & sqltext
    On Error Resume Next

    understand what 'On Error Resume Next' does
    in essence you have turned error reporting off, so the fact that it doesnt work may well be because you have hidden the error message
    Last edited by healdem; 11-21-13 at 08:14.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2013
    Posts
    5

    divide numbers of records in equal parts

    Sorry, my mistake.
    ACS should have been Ascending order.
    I have now fixed it an notice that the query runs correctly although I have noticed that if I add, for example 4 records and the 1oth records of my list has the same LastName (BLN) it will show also all the other records with the same LastName.

    Examples
    Text0 = 4

    Query will return

    A
    AA
    AB
    AB
    AB
    AB

    Clients can have the same Last name and this is not a problem for me. Thank s

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •