Multiple Tables/Priority List

[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

 

[LittleSmudge]

I think you are working along the same lines as I'd approach it, But I'd do it just with the Problems table.

Add to the 'Problems' table a field for SolverNumber ( type Integer )

Dim intSolvers As Integer
Dim intCount As Integer
intSolvers = number of solvers for today
intCount = 1

rst.Open Recordset "SELECT * FROM Problems ORDER BY Priority"

While Not rst.EOF
rst!SolverNumber = intCount
intCount = intCount + 1
If intCount > intSolvers Then intCount = 1
Wend

rst.Close
etc..
( RecordSet code will depend on your version of Access {DAO or ADO} )

Then create a Report that prints out the Problems table Groups by SolverNumber on separate pages and ordered by Priority.


QED

G LS