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!