Database table structure (1 Viewer)

[ggrayman]

Attached is the current structure of a Microsoft Access database project I am working on that tracks the approval processes for different types of submissions required for each Line, which represents a grant sub-initiative, of an inclusive "Grant." My difficulty is at the lower right corner where I would like to be able to attach a PREDEFINED Submission Approval Sequence to a submission without having to fill in the fields of each sequence manually for each submission. I cannot figure out how to create a single item that is really a list of items. Please help.

 

[jdraw]

Attached is the current structure of a Microsoft Access database project I am working on that tracks the approval processes for different types of submissions required for each Line, which represents a grant sub-initiative, of an inclusive "Grant." My difficulty is at the lower right corner where I would like to be able to attach a PREDEFINED Submission Approval Sequence to a submission without having to fill in the fields of each sequence manually for each submission. I cannot figure out how to create a single item that is really a list of items. Please help.

I don't have a solution to the difficulty in the lower right hand corner of your data structure diagram. However, I did work on a large government project involving the application for grants under a range of government programs.
I did not work directly in the grants/loans area. However, the approach that was used successfully for a number of years involved a clear definition of the business processes involved.
Each step of the process for each of the granting programs was clearly identified with a number of parameters. This also involved the definition of Status. The steps ranged from preApplication to postProgram review and included Approval, Rejection including rationale, Withdrawal(by client/applicant), site visits/reports and more. Not only was each status defined, but the possible moves from one status to another were also defined and modeled. When someone indicated a particular project was at Status 501 -- everyone understood exactly what that meant. I know the teams involved worked hard to get the business flow completely understood; and modeled the flow with all sorts of samples including legislative adjustments. The bottom line on this is that " you must understand the business of grants and approval processes intimately" in order to create a supportive database application.

 

[pkstormy]

The 1st thing I'd recommend before you proceed is get rid of all the spaces and / in the field/table names. Also as a note, avoid using reserved words for field names and fields named such as: MyFieldName# or other like characters (even though I didn't see any of these characters in your schematic at a quick glance but I did see some / in the field names which I'd highly suggest removing). It will make your life (or whoever is going to write the code) work much easier without spaces in the names. A field names as such: "LINE Item 1" or "Sub Process/Intuition ID" or other fields/tables named as such can be problematic and very time-consuming when it comes to writing/troubleshooting code.

It also makes it easier when joining 2 tables on a field if the field is named the same in both tables. Just something that makes it easier when developing code (ie. GrantID joins to GrantID).

Your question though is difficult to answer without understanding the business logic of what the program is supposed to do. As jdraw mentioned, it's important when designing a structure that you learn all aspects of what is involved with the process, including 'predicting' possible future changes. A single item that is joined to a list of items is a relational join where you'd most likely use a 1 to many relationship.

Again though, I can't emphasis enough correcting the field/table naming.