How do I generate a sequential number within a query A2007? 2

[TheresAlwaysAWay]

Hi- I've searched all forums and YouTube videos searching for a solution. So far, no luck. Hoping someone can help me.

I am creating a means of automatically assigning incoming work to people who are available. The query that generates the list of available personnel is quite complex and takes a couple of seconds to run. That's not a big deal if it only runs once. I've found several solutions online that cause the query to be run once for each record to generate the count. That's obviously impractical with a slow query.

I've found multiple references to the DCount("id","myTable","id <= " & [id]) method, but my problem is that I have no ID number or any number field that is unique. The only unique identifier in the query is a text UserName field, and it seems that the DCount method can't be used with text. At least, I can't get it to work!

What I want in a nutshell is that every time the query is run a sequential number is generated in one field sequencing the records in the order the query displays them. If there are 10 records in the set, the numbers are 1-10 sequentially. If we assume that work has been assigned to one of the users and that person is no longer available, then the next time the query runs the remaining people will be counted 1-9. If two people finish a task and become available it goes 1-11. I need the sequential counter because these people are ordered within the query. Imagine there's an automatic assignment to #1, but s/he is busy and can't take the assignment. The popup message they get contains the ability to deny the assignment, and when they do the automation sees that #1 denied and then reruns the query and sends it to #2. These numbers have a temporary function and need to be regenerated each time the query runs. They are constantly changing but in every case I need to start with 1 in order for it all to come together.

If someone can show me how to use the DCount method with a text field I'll be tickled pink! If someone has a simple VBA solution I'll be jumping up and down. I've literally spent ten hours searching and I just can't come up with something that works in my application.

All help will be greatly appreciated.

 

[Darrylles]

Here's how I'd do it (I never did like DCount). Untested, but it gives you the gist...

Create a table:

tblUniqueNum
lngKey     (Long - default '0')

Create a function that increments the stored number, then returns that number:

Function GetNextKey As Long
  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Dim strSQL as String
  Dim lngKey as Long

  'Get LAST USED key from table...
  strSQL = "SELECT lngKey FROM tblUniqueNum"
  Set dbs = CurrentDB
  Set rst = dbs.OpenRecordset(strSQL)
  lngKey = rst!lngKey + 1

  'Add incremented key value back to table (ready for next use)...
  strSQL = "UPDATE tblUniqueNum SET lngKey = " & lngKey
  dbs.Execute strSQL, dbFailOnError

  'Return the incremented number to the calling code (query in this case)...
  GetNextKey = lngKey

  Set rst = Nothing
  Set dbs = Nothing

End Function

In your query that returns available personnel - add a field reference:

 '=GetNextKey'

Right-click the query window -> Properties.
Add a meaningful field name (label) for the 'GetNextKey' column.

If you are insistent that numbers should ALWAYS start at '1', then before you start the process to acquire the personnel - run a query to set the table number to '0'.

ATB,

Darrylle

 

[Andrzejek]

The only unique identifier in the query is a text UserName field" - so what prevents you from adding an AutoNumber field (as a PK) to your table where you have your UserName field?


---- Andy

There is a great need for a sarcasm font.

 

[TheresAlwaysAWay]

Damn, Andy! You asked, "so what prevents you from adding an AutoNumber field (as a PK) to your table where you have your UserName field?"

Well, how about being blind?

Of course the table in which the UserName field resides already has a numeric primary key. The query I'm ultimately using as the source for the available workers is the result of collecting data from two union queries and at least one other query. I simply haven't carried that ID field through them because until now I haven't had need for that data, and so it's currently unavailable in that final query. All I need to do is go back and make that ID available!

Slaps forehead! Duh!

It's amazing how sometimes something obvious is invisible, like the story of the elephant in the room hiding behind the curtains and not being noticed.

Thank you for helping me pull my head out of my a...rmpit!

 

[TheresAlwaysAWay]

Thanks Darrylles- I can never properly express my gratitude for the help everyone here so freely provides.

I'm not at work yet so I haven't had a chance to do anything yet. I can see how your solution provides what I need, but honestly I like Andy's idea better. If I don't need to build more structure then why do it?

You said you're not a fan of the DCount method but just from personal experience I can say that I have been using it successfully for years in queries and I have found it to be quite straightforward. What's more logical than a count?

I'll let everyone know how it turned out but thanks again.

 

[TheresAlwaysAWay]

Just a quick update. After revising all the queries along the way so that the ID number was available in the final query used for Automation I found that the DCount method still was not effective, as it forced the query to run once for every record in the set. I can tolerate a slightly slow query running once, but not ten times in a row, so I had to abandon the concept.

I finally solved the problem by changing the Select query to an Append query. I created a local table with an auto-number field, and before appending the records to the new table I run the following code:

CurrentDb.Execute "ALTER TABLE AutomationSorted ALTER COLUMN RowNum COUNTER(1,1)"

That resets the AutoNumber counter to 1 each time the table is repopulated. I now have a solid, reliable, and fast way to create the sequential numbers that I need in order to complete this project.

It should be something that is do-able within a query, but I've wasted far too much time on this already. This solution works and now at least I can get on with the rest of this project.

The choice of a local rather than a SQL Server table may seem counter-intuitive, but this is only for the purpose of identifying who is available to send work to. Once the info on the next available worker is collected from the local table it is assigned through a linked table.

Thanks again to those who offered help.

 

[MajP]

You did not explain how the query is ordered. If there is some ordering than you can do sequential numbering. Often referred to as a Ranking Sub Query.

Lets assume your query is based on user iD (could be lastname, age, DOB, years in service,....)

SELECT A.UserID, A.UserName,...., A.OtherField (SELECT Count(*) FROM tblTable AS B WHERE A.ID > B.ID) AS Sequence
FROM tblTable AS A
ORDER BY A.UserID

In access subqueries are not fast, but faster than dcounts. See this for example and pros cons

http://allenbrowne.com/ranking.html

 

[Darrylles]

The reason that I discarded the 'autonumber' PK idea, was specifically because you seemed to require some 'order', and, the auto-pk would be 'random'.
Good luck with it in any case.

ATB,

Darrylle

 

[TheresAlwaysAWay]

Ahhh, MajP, you have asked a question I tried to avoid. It's not because I don't want to answer, but because it is very complex, which is the reason for the many queries that feed the ultimate results query. I simply said the query was ordered so as not to complicate that long explanation I opened with.

However, you have asked and I'll do what I can to answer. We are a busy insurance agency with 20+ work stations. A part of our model is that some quotes must be returned within 12 minutes. Others are not so time sensitive. Some of those may be returned within an hour, while another group can be done any time before the end of the day. Some of the incoming work involves writing a quotation, while other work is writing a policy for those who have purchased.

The sort order identifies who is not working on anything at all at that moment, and those people are at the top of the availability list, sorted by their production that day. Those who have had the fewest quotes are first, and are ordered in ascending order. Then those who have an end of day quote but nothing else are next, again sorted in ascending order of daily production. Then those that have a pending job that needs to be returned within the hour become the third group available, again sorted ascending by production. Those working on waiting quotes or on writing policies are excluded from the list available for work. However, as soon as they finish that task they are immediately returned to the list of available workers and placed within the order as described above by what is remaining for them. Also, excluded from the list are those at lunch or on break.

It's obviously highly complex, and that's why I dodged it, but ultimately you can see that the order is critical to reason the list exists at all, and why that numeric row number is important. It also becomes evident that it is not a simple ranking.

However, the solution I came up with works fine. It doesn't noticeably affect the 1.5-2 seconds it takes to run the query, and that is not burdensome since it only runs on one machine, and even then only when there has been a new incoming piece of work that must be assigned.

Are you sorry you asked? :=)

 

[TheresAlwaysAWay]

Darrylles, from my response to MajP you can see that the order is indeed critical, and all I did was turn the final Select query into an Append query and append the records to the table while an AutoNumber field generates the desired sequence. You are correct. An auto-pk would be random, but there is no pk involved in these queries. It's based on what is happening at any given moment, like a snapshot.

 

[TheresAlwaysAWay]

I'm not sure if I should start a new thread for this, but the previous explanation of what I'm doing is already posted here and while this question is unrelated to the sequential number (I explained how I solved that earlier), it's still related to the overall project I'm trying to implement. One problem is just explaining this whole thing. It's obviously highly complex, and tedious and wordy explanations are needed in order to get anyone willing to help up to speed just trying to understand what I'm trying to accomplish.

The actual automation logic is developed and working, and I'm now trying to isolate those who are available for assignment. I have no trouble creating a union query that joins those that are excluded from the list of available processors. Those include people out to lunch or break, and those who are working on either a waiting quote or writing a policy. That gives me a list of folks that should be ignored on all future lists. I've created a separate query that isolates those that are working on a quote due within the hour and those working on something due by the end of the day. It is possible for someone working on an end of day quote to be assigned an hour quote and then something waiting as well. The more urgent quote simply takes precedence. However, once someone is on that excluded list already mentioned, I do not want them available for automated assignments. As I explained earlier, this is a fluid situation. As soon as someone completes writing a policy or a waiting quote, or when they return from lunch/break, they're available again, so automation assignments are based on a snapshot at that moment of who's doing what.

That said, I'm pulling out what little hair I have left trying to create the remaining list. So I don't have to repeat myself, let's say there are four lists. The first is the list of folks logged in and present at that moment. Like any business, we have people who are off, away, etc. that are not even there that day. So from the list of available processors (query called ProcessorsAvailableNow), the second list, those excluded from automated assignments is drawn. It's a Union query that combines separate queries, one displaying only those on break/lunch, another displaying only those working on Waiting quotes, and another displaying only those writing policies. The resultant list contains all the excluded names based on who is present at that moment.

So far, so good. I just need to offer a little more explanation in order to get to my actual problem/question.

I have also created separate queries that isolate those working on hour quotes and day quotes as individual lists, both of which are drawn from the same ProcessorsAvailableNow record set mentioned earlier. FYI, those two queries are called ProcessorActivityAutomationHour and ProcessorActivityAutomationDay respectively.

Now the actual issue.

How do I create the three remaining lists I still require; those who are not on the excluded list but who have hour quotes, those not excluded and who do not have hour quotes but do have day quotes, and finally, those who are inactive, not working on anything at that moment, and then combine them into one? I am going nuts trying to create my ultimate goal through all this, one record set with no duplicate names, that does not include anyone on the excluded list, and which would do the following. It would identify people based on their most pressing assignments. We would show those working on an hour quote as hour even if they also have a day quote pending. Those identified as day would have nothing but a day quote in their name. And then identify those who are inactive, who have nothing at all going on, by deriving those who aren't on any of the lists.

Does anyone have a suggestion how to combine all these elements to give me what I need at the end of all this? I'm sure there will be other questions and more explanation, but I don't know how to ask this without providing as much detail as I have.

As always, all help is greatly appreciated.

 

[Andrzejek]

You have mentioned all those queries you have: ProcessorsAvailableNow, ProcessorActivityAutomationHour, ProcessorActivityAutomationDay, etc.
Do they have some common, unique identifier? Primary Key? NameID? LoginName? UserName? A field that is in all queries that identifies a specific record?

Let's say you do, and it is called UserName
You can always do something like:[tt]
Select * from ProcessorsAvailableNow
Where UserName NOT IN (Select UserName from ProcessorActivityAutomationHour)[/tt]


---- Andy

There is a great need for a sarcasm font.