VBA autocomplete based on %

[johnsm1981]

Please can some expert tell me if this is possible, and how it can be done....

I have an access form which 1 person updates daily, but the task after that is split between 2 users. I have got most of the form data automated with a combo box from a supporting table.

But I now want to automate a username & date, but based on a % and set of conditions.

(At the moment I go in at the end of the week and manually put those 2 usernames next to the new work thats been logged)

I'll try and explain:

user 1... gets 60% of the work
user 2... gets 40% of the work

At the end of that day, when the form is completed with say 100 new records, I want to then run a query, macro (or anythying else that would do it automatically), to assign those usernames 'user1' to 60 of those 100 records... and 'user2' to 40 of the records. and also give it todays date.

Is this even possible? if it helps I am better with SQL than VBA so if that can be used instead please let me know. thanks all.

 

[namliam]

Do you have a guaranteed key in your table that is sequential (i.e. not an autonumber but is 1,2,3,4,5,6,7,9, etc?)

If so perhaps an sql solution might be possible, but otherwize will need some VBA

Where would you set username and the %? I assume some table?


You could feasably even assign the jobs as they come in, which would for me be preferable to batch assignments

Assuming a tabel tblUsers (usernm, perc)
User1 - 40%
User2 - 60%

And a jobs table tblJobs (Datefield, Username)

This query should return the user that should get the next job comming in.

SELECT Abs([Perc]-TodayPerc) AS DiffToday, *
FROM (SELECT tblJobs.datefield
           , tblJobs.username
           , Count(tblJobs.datefield) AS AantalVandatefield
           , totalperday.TotalToday
           , Count([tblJobs].datefield)/[totaltoday] AS TodayPerc
           , tblUsers.[Perc] 
      FROM (tblJobs 
      INNER JOIN (SELECT [tblJobs].datefield
                       , Count([tblJobs].datefield) AS TotalToday 
                  FROM tblJobs 
                  GROUP BY [tblJobs].datefield)  AS totalperday ON tblJobs.datefield = totalperday.datefield) 
      INNER JOIN tblUsers ON tblJobs.username=tblUsers.[UserNm] 
      GROUP BY tblJobs.datefield, tblJobs.username, totalperday.TotalToday, tblUsers.[Perc])  AS [%$##@_Alias]
ORDER BY Abs([Perc]-TodayPerc) DESC;

That way you dont have to run a mass process after day ends, but can dynamicaly assign jobs as they come in... Further advantage is that jobs comming in can be kinda random with additional random assignments is better ussually than batch assignments that would assign the first 4 to User1 and the next 6 to User2
Instead alternating user1 and user2 as jobs come in depending on which one has the "lowest" assign % at the time.

 

[pr2-eugin]

Hello johnsm1981, Welcome to AWF

This is quite easily achievable. Need a bit of math to get the numbers right. In a form, maybe with two textbox enter the numbers 60, 40 in the text box and on a click of the button run an update Query. Something like.

Dim user1Num As Long, user2Num As Long
Dim totlaRecords As Long

totalRecords = DCount("*", "yourTableName", "nameFieldName Is Null And dateFieldName Is Null")

user1Num = Round(totalRecords * Me.yourTextBox1)
user1Num = Round(totalRecords * Me.yourTextBox2)

CurrentDB.Execute "UPDATE tableName set nameFieldName = 'user1' AND dateFieldName = #02/20/2015# WHERE IDFieldName IN (" & _
                  "SELECT TOP " & user1Num & " IDFiledName FROM tableName WHERE nameFieldName Is Null And dateFieldName Is Null)"

CurrentDb.Execute "UPDATE tableName set nameFieldName = 'user2' AND dateFieldName = #02/20/2015# WHERE IDFieldName IN (" & _
                  "SELECT TOP " &  user2Num & " IDFiledName FROM tableName WHERE nameFieldName Is Null  And dateFieldName Is Null)"

 

[johnsm1981]

Thank you Both, I'm not too sure where I would set up any of them though... namliam through an UpdateQuery? and Pr2 through a macro?

I'll try and add a bit more detail.
My Main Table is called DATA MASTER and User Name field is called PENDING WITH, date field ALLOCATION DATE

I have got a working UpdateQuery I run and it updates all records in the data master with 1 name and todays date... on a territory condition.

That looks like this...
UPDATE [DATA MASTER] SET [DATA MASTER].[PENDING WITH] = "User1", [DATA MASTER].[ALLOCATION DATE] = NOW()
WHERE [DATA MASTER].[PENDING WITH]IS NULL and [DATA MASTER].[TERRITORY] IN ('GB','US','ZA')

How do I then use any of your statements to also say .... whatever the total count is when I run the updatequery... give 60% of them to user1...40% to user2.
The % bit it can look up from the table you said 'tblusers'

thanks again

 

[namliam]

1) todays date shouldnt (imho) be an update statement, can easily be done on "after update" of the record being entered and/or be a default value of that field.

2) my solution would again be on record creation, while inputting on the background the query checks who is "next" and enters the user into the input form as a default value.

 

[johnsm1981]

Ok so where abouts would I put your statement above please, on the form itself? and how?
When I tab through the fields to the bottom of the form it then starts a new blank form. I dont know how many will be logged in 1 day/week, so I'm also not sure how it decides on the next users name? would you not need a grand total 1st before it can decide on the 60/40 rule?

 

[namliam]

Well the totaltoday is the grand total of today.

You would incorporate it in the form in one of the events, like After update or perhaps on edit or before updat which ever you prefer.

My query shall return the user with the most "room" in his percentage as the first to be assigned. So if there is no records yet today, then the 60% will get the first one.
Once the 60% has the first record, he will be "over assigned" at 100% of today and will be at -40% thus will be bottom.
While the second user is at 60% available and is at the top.

I did have to change my query "a bit", seems I oversimplified it a little.

So pick your event and use something along the lines of

dim rs as dao.recordset
dim MySQL as string
mySQL = " SELECT tblUsers.* " & _ 
            " , Nz([counttoday],0)/Nz([totaltoday],1) AS PercToday " & _ 
            " , [Perc]-(Nz([counttoday],0)/Nz([totaltoday],1)) AS RemainingPerc " & _ 
        " FROM tblUsers  " & _ 
        " LEFT JOIN (Select tblJobs.Datefield " & _ 
        "                 , tblJobs.Username " & _ 
        "                 , count(*) as CountToday " & _ 
        "                 , TotalToday " & _ 
        "            from tblJobs " & _ 
        "            Inner Join " & _ 
        "                     ( " & _ 
        "                       SELECT [tblJobs].datefield, Count([tblJobs].datefield) AS TotalToday  " & _ 
        "                       FROM tblJobs  " & _ 
        "                       GROUP BY [tblJobs].datefield " & _ 
        "                     ) as TotalsToday on tblJobs.datefield = TotalsToday.Datefield " & _ 
        "            Group by tblJobs.Datefield, tblJobs.Username, TotalToday " & _ 
        "           )  AS Jobs ON tblUsers.Username = Jobs.Username " & _ 
        " ORDER BY [Perc]-(Nz([counttoday],0)/Nz([totaltoday],1)) DESC;"


Set rs = currentdb.openrecordset(mysql)

Msgbox rs!username & " is to be assigned next" 

rs.close
set rs = nothing

Offcourse you want to write your rs!username into the form/table ....

Some tweaking can be done to instead of fetching the count from the table each time, to keep things in memory and work with that, but this should be a good place to start.

 

[johnsm1981]

I copied and pasted your string into a Form After Event.... but it came up with this:

"too few parameters, expected 2"

on this line.

Set rs = CurrentDb.OpenRecordset(MySQL)

 

[namliam]

You have to check your field names... probably a fieldname that is different in the tables.

 

[johnsm1981]

Hi thank you for your help, but I'm totally lost on what to put where now. I've tried these, but got the same message.
Set rs = Me.[ALLOCATION DATE].OpenRecordset(MySQL)
Set rs = Me.[PENDING WITH].OpenRecordset(MySQL)
Set rs = Me.[DATA MASTER].OpenRecordset(MySQL)

 

[namliam]

Huh? wait, what?

Ok lets try and debug...
take this code:

Sub namliamtest()

Dim qdf As QueryDef
Dim MySQL As String
MySQL = " SELECT tblUsers.* " & _
            " , Nz([counttoday],0)/Nz([totaltoday],1) AS PercToday " & _
            " , [Perc]-(Nz([counttoday],0)/Nz([totaltoday],1)) AS RemainingPerc " & _
        " FROM tblUsers  " & _
        " LEFT JOIN (Select tblJobs.Datefield " & _
        "                 , tblJobs.Username " & _
        "                 , count(*) as CountToday " & _
        "                 , TotalToday " & _
        "            from tblJobs " & _
        "            Inner Join " & _
        "                     ( " & _
        "                       SELECT [tblJobs].datefield, Count([tblJobs].datefield) AS TotalToday  " & _
        "                       FROM tblJobs  " & _
        "                       GROUP BY [tblJobs].datefield " & _
        "                     ) as TotalsToday on tblJobs.datefield = TotalsToday.Datefield " & _
        "            Group by tblJobs.Datefield, tblJobs.Username, TotalToday " & _
        "           )  AS Jobs ON tblUsers.Username = Jobs.Username " & _
        " ORDER BY [Perc]-(Nz([counttoday],0)/Nz([totaltoday],1)) DESC;"
Set qdf = CurrentDb.CreateQueryDef("qrynamliamTest", MySQL)
Set qdf = Nothing
DoCmd.OpenQuery "qrynamliamtest"

End Sub

And run it, this should tell you which columns I used that differ from your column names.
It will make a query and run it, popping up the 2 parameters it cant find.

 

[johnsm1981]

It stopped at this...

Dim rs As dao.Recordset
Dim MySQL As String
MySQL = " SELECT tblUsers.* " & _

"compile error - invalid outside procedure"

This is what I done to see if you can see where i've messed up (as I am clearly a novice)... I created a table called tblusers (with 2 usernames and 1 with 40% and the other 60%) and another table called tbljobs (just with 2 usernames)

With your 1st code I went on the form properties and pasted it as an after event. when I run it stopped on that CurrentDB line.

On the form I've then tried changing the 'Pending With' row source field to look at the tblusers table. is that right?

 

[namliam]

The error means you didnt copy the full code I gave you

What are the column names of your tblUsers and tblJobs?

 

[johnsm1981]

tblusers = Assign Percentage, Username
tbljobs = Username, Datefield (this has no data in this column)

 

[johnsm1981]

appologies, i hadnt called the field names exactly as you'd put before, however after matching them it still came up with the same message

"too few parameters, expected 2"

on this line.

Set rs = CurrentDb.OpenRecordset(MySQL)

 

[namliam]

So
tblJobs (Datefield, Username)
tblUsers (UserName, Perc)

Try taking the sql alone and creating a query from it, run that see what it does:

SELECT tblUsers.*, Nz([counttoday],0)/Nz([totaltoday],1) AS PercToday, [Perc]-(Nz([counttoday],0)/Nz([totaltoday],1)) AS RemainingPerc
FROM tblUsers LEFT JOIN (Select tblJobs.Datefield, tblJobs.Username, count(*) as CountToday, TotalToday
                 from tblJobs
                 Inner Join
                                     (
                             SELECT [tblJobs].datefield, Count([tblJobs].datefield) AS TotalToday 
                             FROM tblJobs 
                             GROUP BY [tblJobs].datefield
                                     ) as TotalsToday on tblJobs.datefield = TotalsToday.Datefield
                Group by tblJobs.Datefield, tblJobs.Username, TotalToday
                )  AS Jobs ON tblUsers.Username = Jobs.Username
ORDER BY [Perc]-(Nz([counttoday],0)/Nz([totaltoday],1)) DESC;