Multiple Tables/Priority

[Torn39]

Okay, I've been reading the forums for awhile trying to pick up as much as I can, but now I'm stuck. Hopefully some of you can help me out here.

I've got a development problem. Nice Access 2000 db, forms all created, etc. Table 'Problems' contains all the problems my users are having. No problem getting the problems accurately in the database. Table 'Priority' sets up which are the most important problems, essentially giving a heirachy to all the possible issues we encounter. No problem setting this up, works like a charm. I've got 1-5 "problem solvers" I can assign the problems to.

Here's the problem...

I'm stuck having my database produce even (even defined as fair or equal) "to-do" list for my problem solvers, depending on how many problem solvers I have that day.

Example: I have 10 calls, 5 are "priority A", 4 priority B, and 1 priority C. I have 3 problem solvers today (miracle!). So in my app, I enter a "3" for "Problem Assignment Lists" I need my app to spit 3 lists for my three workers, each list having at least 1 "A calls" and 1 "B call" and the lucky bastard not having to do 2 A calls gets stuck with the extra B call...

I've got the whole app "working" until I get here, and then I'm stuck because I'm dying trying to produce these accurate "temp" tables. I'm not sure really sure where to go next....Any direction would be greatly appreciated.

Thanks for your help.

Torn39

 

[SBendBuckeye]

A query can call a function at runtime if it is in a class module. Try something like the following against a query which is sorted with the highest priorities first:

A. Create a function something like this in a class module:

Public Function Splitter(InputFieldFromQuery,
AvailableWorkers) As Integer
Static intCounter As Integer
If intCouner > AvailableWorkers Then
intCounter = 0
End If
intCounter = intCounter + 1
Splitter = intCounter
End Function

B. Run your query with a field to hold the splitter output.

C. Take the result data and split it based on the number assigned to it by splitter above.

Sorry that I had to do this off of the top of my head. Hopefully, this will get you started.

Good Luck!

 

[Torn39]

Thanks very much for your help. This has me started in a good direction...

Thanks,

Torn