During the process of creating a clean database to upload, I tried Rabbit's suggestion and it works beautifully! I'm uploading what I have to show how I did it.
***Caution - Very Complex***
If you open frmLoans, you will see my ultimate goal. Each record with a subform listing the appropriate checklist tasks. Some completed, others not. Now, the fun part.
Table structure
[tblLoans] is the root table. Records here are what triggers a new process. All the other tables support it or are just there to tell the database how to behave.
[tblTasks] sets all the tasks based on the type of loan (from [tblLoanTypes]). There are a bunch of fields, but the important ones for this question are CompletionType (tells the system where to look to know if the task has been completed) and LookupValue, which has multiple functions. If [CompletionType] is "Document Uploaded", then [LookupValue] gives the document type ID that needs to be attached in [tblLoanDocument s] to mark the task completed. If [CompletionType] is "Loan Value", then it gives the field name to look for in [tblLoans]. If [CompletionType] is "Loan Value" and [ValueType] is "Group", then there are a group of fields required in [tblLoans] required to mark the task as completed. When there is a group, then [tblTaskAddition alInfo] comes into play, with the table and field names to complete that task.
[tblLoans] and [tblTasks] have a many-to-many relationship, so [tblLoanTasks] is the join table between them. For those who are curious, if [CompletionType] is "Task Value" (from [tblTasks]), then the user is entering values into the fields TaskDate, TaskText, or TaskYesNo depending on the value in [tblTasks].[ValueType]. For the purpose of this example, however, I haven't included any of those values in my checklist at this time.
Queries
It would take way to much time to completely describe every single query included, but I'll provide a bit of a road map.
[qryLoanFields] is the "unpivoted" query that Rabbit suggested.
[qryChecklist] gets the tasks assigned to each loan and provides some of the information necessary to split them out based on the [CompletionType].
[qryLoanDocument Counts] checks for how many documents are attached to each loan, grouped by the document type.
[qryChecklist_Do cumentTasks] then takes the count to see if the appropriate documents are uploaded for each task and marks the task complete or not.
[qryLoanTaskAddi tionalInfoNotCo mpleted] counts the number of fields in [tblLoans] that are empty, but required in [tblTaskAddition alInfo]. It works based on the principle that if the count is more than 0, then not all the fields in that group are populated.
[qryChecklist_Lo anValueGroup] uses the info from [qryLoanTaskAddi tionalInfoNotCo mpleted] to mark the task completed or not.
[MasterChecklist] combines the results of [qryChecklist_Do cumentTasks] and [qryChecklist_Lo anValueGroup] to form the results.
Ultimately, there would be other queries to handle the other [CompletionTypes] and then combined in the [MasterChecklist] UNION query.
Disclaimer
This is not my final design. I'm hoping to be able to reduce all the QueryDefs and possibly change the possible means of completing a task. At this point I'm just trying to figure out what is possible so that I can meet all the user requirements and have a flexible design.
***Caution - Very Complex***
If you open frmLoans, you will see my ultimate goal. Each record with a subform listing the appropriate checklist tasks. Some completed, others not. Now, the fun part.
Table structure
[tblLoans] is the root table. Records here are what triggers a new process. All the other tables support it or are just there to tell the database how to behave.
[tblTasks] sets all the tasks based on the type of loan (from [tblLoanTypes]). There are a bunch of fields, but the important ones for this question are CompletionType (tells the system where to look to know if the task has been completed) and LookupValue, which has multiple functions. If [CompletionType] is "Document Uploaded", then [LookupValue] gives the document type ID that needs to be attached in [tblLoanDocument s] to mark the task completed. If [CompletionType] is "Loan Value", then it gives the field name to look for in [tblLoans]. If [CompletionType] is "Loan Value" and [ValueType] is "Group", then there are a group of fields required in [tblLoans] required to mark the task as completed. When there is a group, then [tblTaskAddition alInfo] comes into play, with the table and field names to complete that task.
[tblLoans] and [tblTasks] have a many-to-many relationship, so [tblLoanTasks] is the join table between them. For those who are curious, if [CompletionType] is "Task Value" (from [tblTasks]), then the user is entering values into the fields TaskDate, TaskText, or TaskYesNo depending on the value in [tblTasks].[ValueType]. For the purpose of this example, however, I haven't included any of those values in my checklist at this time.
Queries
It would take way to much time to completely describe every single query included, but I'll provide a bit of a road map.
[qryLoanFields] is the "unpivoted" query that Rabbit suggested.
[qryChecklist] gets the tasks assigned to each loan and provides some of the information necessary to split them out based on the [CompletionType].
[qryLoanDocument Counts] checks for how many documents are attached to each loan, grouped by the document type.
[qryChecklist_Do cumentTasks] then takes the count to see if the appropriate documents are uploaded for each task and marks the task complete or not.
[qryLoanTaskAddi tionalInfoNotCo mpleted] counts the number of fields in [tblLoans] that are empty, but required in [tblTaskAddition alInfo]. It works based on the principle that if the count is more than 0, then not all the fields in that group are populated.
[qryChecklist_Lo anValueGroup] uses the info from [qryLoanTaskAddi tionalInfoNotCo mpleted] to mark the task completed or not.
[MasterChecklist] combines the results of [qryChecklist_Do cumentTasks] and [qryChecklist_Lo anValueGroup] to form the results.
Ultimately, there would be other queries to handle the other [CompletionTypes] and then combined in the [MasterChecklist] UNION query.
Disclaimer
This is not my final design. I'm hoping to be able to reduce all the QueryDefs and possibly change the possible means of completing a task. At this point I'm just trying to figure out what is possible so that I can meet all the user requirements and have a flexible design.
Comment