Unsure how to proceed

[JeRz]

Hi everyone,



My apologies if this is in the wrong location.


I was asked to create a database that would assist in updating daily data so that it can then be sent out as the individual's daily assignment. I am really stumped here as to how I can even get going with this and if this is even possible to do. (I only usually work on issue tracking databases)


There are 2 fields that need to be updated each day when the new data is imported. There is an Assign field that would be used to assign an auditor's name for that particular record (there are 6).

There is also a Priority field that would need a number value of 1-9 assigned to it. These fields would be blank initially on the raw data imported each day.


My main issue is that when assigning the auditor name, there can be either one piece of criteria for that person's name (based on AssignMessage) or, there can be up to 3 pieces of criteria that could go into assigning that person to the task but may not be all 3, either. (AssignMessage,Customer,Yes/No)


Regarding the Priority field, from the data I was provided, it would be determined as to which number is assigned based on one of two fields (GRP_NBR) or (ID_1). Some criteria may be used in GRP for some of the data while other criteria may be in ID_1. They would never be in both at the same time however.


My initial thought process was to create a table for the Auditors, an Assign table, and a Priority table and link those together. From there, build a form to allow the users to select the criteria which would then run an update query to add the auditor name. After that, build another update query to Assign the appropriate value to the Priority field but again, do this where the user selects the criteria. With the idea being to use cascading combo boxes to make the process easier.


I haven't encountered a project like this before. With the multiple possibilities of what the criteria could be, is there an easier way to do this? Is this approach even sensible?


Thanks for any help!

[ArviLaanemets]

A probable design.
tblQualificatons: QualificationID, QualificationText
tblAssignments: AssignmentID, AssignmentInfo, ...
tblAssignmentQualifications: ACID, AssignmentID, QualificationID, [QualificationPriority]
tblAuditors: AuditorID, ForeName, LastName, ...
tblAuditorQualifications: AQID, AuditorID, QualificationID
tblAssignmentAuditor[s]: AssignmentID, AuditorID, [Priority].

When user selects auditor(s) for an assignment, then auditor is selected from list of audtitors ordered by qualification index descending (the auditor qualified best is at top of list) Qualification index is calculated, as number of qualification points every auditor gets, divided bay maximal possible number of points.
An example:
There is a assignement with 3 different qualifications. qualification1 with QualificationPriority = 2, qualification3 with QualificationPriority = 5, and qualification6 with QualificationPriority = 1. The max of qualification points for task is 2+5+1 = 8;
You have 3 auditors. Auditor1 has qualifications 1, 2 5 and 7. Auditor2 has qualifications 1, 3, 4 and 6. Auditor 3 has qualifications 2, 5, 6 7 and 8.
Autitor2 has 2+5+1 = 8 qualification points and qualification index 8/8 = 1
Auditor3 has 5+1 = 6 qualification points and qualification index 6/8 = 0.75
Auditor1 has 2 qualification points and qualification index 2/8=0.25

So in combo auditors are listed in order Auditor2;Auditor3;Auditor1. When there are no other restrictions, then user selects Auditor1 as having best qualification for this task. When Auditor1 is p.e. busy, or ill, or on annual leave, auditor2 is next best choice.

For what you plan use Priority from your post, remained unclear for me - I couldn't find no reasonable position for it.